CODEWITHSUNDEEP
sqloracle-database
Dominik Madix
Dominik Madix

Reputation: 15

SQL problems duplicate column name using inner join and missing right parenthesis

SELECT 
    BillNo, Non_resident, 
    SUM(ConsumptionCharge AND SupplyCharge) AS "TotalCharge"
FROM
    BILL
INNER JOIN 
    ACCOUNT ON ACCOUNT.BILLNO = BILL.BILLNO
WHERE 
    Non_resident = Upper('Yes') AND to_char(CreatedDate,'mm') = '09'
GROUP BY 
    ACCOUNT.BILLNO;

CREATE OR REPLACE VIEW View_B 
AS
    SELECT DISTINCT*
    FROM BILL
    INNER JOIN 
        (SELECT DISTINCT * FROM METER) M ON M.BillNo = BILL.BillNo
    JOIN 
        (SELECT DISTINCT * FROM SERVICEADDRESS) SA ON M.AddressID = SA.AddressID
WHERE 
    SA.PostCode = '1267'
    AND SA.FullAddress = '53 Drip Drive, Dripwater'
    AND CreatedDate BETWEEN to_date('2020-01','yyyy-mm') AND to_date('2020-09','yyyy-mm');

The first select does not work at all, showing error

ORA-00907: missing right parenthesis

The second one does work, but it have duplicate column name. Those distinct does seems to be working somehow and I have to use inner join.

enter image description here

Upvotes: 1

Views: 124

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

For the first query, the GROUP BY is inconsistent with the SELECT. Plus, the SUM() expression is not correct:

SELECT ACCOUNT.BillNo, UPPER(Non_resident) as Non_resident, 
       SUM(ConsumptionCharge + SupplyCharge) AS TotalCharge
FROM BILL JOIN
     ACCOUNT
     ON ACCOUNT.BILLNO = BILL.BILLNO
WHERE Non_resident = Upper('Yes') AND to_char(CreatedDate,'mm') = '09'
GROUP BY ACCOUNT.BILLNO;

Upvotes: 1

Related Questions