Oracle complex sql select query

Question

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?

How can a timebased period be greater than '2013-2014', would this equal '-1', does it need to be greater then 2013 or 2014? — Mathieu Brouwers
– Mathieu Brouwers, Commented Jan 18, 2016 at 12:11
Mathieu the PERIODID is a Varchar column and the values for example is: '2013-2014' '2014-2015' etc — Giorgos
– Giorgos, Commented Jan 18, 2016 at 12:14
I'm not great with oracle, but would this work? 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, 4) AND LD.PERIODID > '2013-2014') OR (LD.PERIODID = L.PERIODID AND LD.FORMTYPE IN (1, 2, 3) AND LD.PERIODID < '2013-2014')) — venkatKA
– venkatKA, Commented Jan 18, 2016 at 12:19

ScaisEdge · Accepted Answer · 2016-01-18 12:20:39Z

1

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) )

answered Jan 18, 2016 at 12:20

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2016-01-18 12:21:20Z

1

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')

answered Jan 18, 2016 at 12:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Oracle complex sql select query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related