CODEWITHSUNDEEP
sqloracle-database
Giorgos
Giorgos

Reputation: 677

Oracle complex sql select query

I have the following query:

SELECT SUM (UNCOLLECTED)
            FROM LIQUIDATIONSDETAILS LD
           WHERE     LD.COMPANYID = L.COMPANYID
                 AND LD.GROUPID = L.GROUPID
                 AND LD.PERIODID = L.PERIODID
                 AND LD.FORMTYPE IN (1, 2, 3)

I need when L.PERIODID > '2013-2014' then the LD.FORMTYPE IN (1,2,3,4) else LD.FORMTYPE IN (1,2,3)

any idea?

Upvotes: 0

Views: 60

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Your query is malformed. You have two table aliases, L and LD, but L is not defined.

Because you intend a JOIN, you should be using explicit JOIN syntax. A simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax:

SELECT SUM(UNCOLLECTED)
FROM LIQUIDATIONSDETAILS LD JOIN
     ??? L       -- I don't know what table "L" refers to
     ON LD.COMPANYID = L.COMPANYID AND
        LD.GROUPID = L.GROUPID AND
        LD.PERIODID = L.PERIODID 
WHERE LD.FORMTYPE IN (1, 2, 3) OR
      (LD.FORMTYPE = 4 AND L.PERIODID > '2013-2014')

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

This could be the answer

SELECT SUM (UNCOLLECTED)
        FROM LIQUIDATIONSDETAILS LD
       WHERE     LD.COMPANYID = L.COMPANYID
             AND LD.GROUPID = L.GROUPID
             AND LD.PERIODID = L.PERIODID
             AND (L.PERIODID > '2013-2014' AND LD.FORMTYPE IN (1, 2, 3) ) 
             OR (L.PERIODID <= '2013-2014' AND LD.FORMTYPE IN (1, 2, 3,4) )

Upvotes: 1

Related Questions