Select To view two-year billing sum

Asked

Viewed 37 times

1

Good afternoon, I’d like to see the billing sum of two different dates(2018 x 2019, but I’m having trouble with the subselect "Details: "ODBC: ERROR [HY000] [Oracle][ODBC][Ora]ORA-00907: right parenthesis not found"

  Select 
Cast(SUM(VLRBRU) AS VARCHAR(20)) AS "Faturamento 2018",
(Select 
Cast(SUM(VLRBRU) AS VARCHAR(20)) AS "Faturamento 2019"
FROM E140IPV
WHERE CODEMP=1 AND CODFIL=1 AND datger BETWEEN TO_DATE ('2019/01/01', 'yyyy/mm/dd') AND TO_DATE ('2019/12/31', 'yyyy/mm/dd') AND TNSPRO IN ('5101','5103','5904','6101','6109','6124','6401','6501','7101','5949A','6109D','6113','5501','6118');
)

FROM E140IPV
WHERE CODEMP=1 AND CODFIL=1 AND datger BETWEEN TO_DATE ('2018/01/01', 'yyyy/mm/dd') AND TO_DATE ('2018/12/31', 'yyyy/mm/dd') AND TNSPRO IN ('5101','5103','5904','6101','6109','6124','6401','6501','7101','5949A','6109D','6113','5501','6118')
oracle

3 answers

0

I tried anyway and it won’t

"SELECT (SELECT CAST(SUM(VLRBRU) AS VARCHAR(20)) FROM E140IPV WHERE CODEMP=1 AND CODFIL=1 AND datger BETWEEN TO_DATE ('2018/01/01', 'yyyy/mm/dd') AND TO_DATE ('2018/12/31', 'yyyy/mm/dd') AND TNSPRO IN ('5101','5103','5904','6101','6109','6124','6401','6501','7101','5949A','6109D','6113','5501','6118')), (SELECT CAST(SUM(VLRBRU) AS VARCHAR(20)) FROM E140IPV WHERE CODEMP=1 AND CODFIL=1 AND datger BETWEEN TO_DATE ('2019/01/01', 'yyyy/mm/dd') AND TO_DATE ('2019/12/31', 'yyyy/mm/dd') AND TNSPRO IN ('5101','5103','5904','6101','6109','6124','6401','6501','7101','5949A','6109D','6113','5501','6118'))"

0

  • On line 6 you don’t need the ;

  • Since I don’t know the type of column datger, we can change where this:

    AND datger BETWEEN TO_DATE...

    for:

    AND TRUNC(TO_DATE(datger,'yyyy/mm/dd')) BETWEEN TO_DATE...

Getting something like:

SELECT  CAST(SUM(VLRBRU) AS VARCHAR(20)) AS "Faturamento 2018",
                (SELECT  CAST(SUM(VLRBRU) AS VARCHAR(20)) AS "Faturamento 2019"
                    FROM E140IPV
                  WHERE CODEMP=1 
                       AND CODFIL=1 
                       AND TRUNC(TO_DATE(datger,'yyyy/mm/dd')) BETWEEN TO_DATE ('2019/01/01', 'yyyy/mm/dd') AND TO_DATE ('2019/12/31', 'yyyy/mm/dd') 
                       AND TNSPRO IN ('5101','5103','5904','6101','6109','6124','6401','6501','7101','5949A','6109D','6113','5501','6118')
                )

FROM E140IPV
WHERE CODEMP=1 abela
     AND CODFIL=1 
     AND TRUNC(TO_DATE(datger,'yyyy/mm/dd')) BETWEEN TO_DATE ('2018/01/01', 'yyyy/mm/dd') AND TO_DATE ('2018/12/31', 'yyyy/mm/dd') 
     AND TNSPRO IN ('5101','5103','5904','6101','6109','6124','6401','6501','7101','5949A','6109D','6113','5501','6118')

0

Wouldn’t it be simpler to do:

SELECT 
    SUM(CASE WHEN year(datger) = 2018 THEN VLRBRU ELSE 0 END) AS "Faturamento 2018",
    SUM(CASE WHEN year(datger) = 2019 THEN VLRBRU ELSE 0 END) AS "Faturamento 2019"
FROM E140IPV 
WHERE CODEMP=1 AND CODFIL=1 AND (year(datger) = 2018 OR year(datger) = 2019) AND
 TNSPRO IN ('5101','5103','5904','6101','6109','6124','6401','6501','7101','5949A','6109D','6113','5501','6118');

Browser other questions tagged oracle

You are not signed in. Login or sign up in order to post.