CODEWITHSUNDEEP
sqloracle-databaseplsql
Remya
Remya

Reputation: 21

Joins and condition

I need to get the activity name where deletedsw='0' and eventdtm=sysdate-12. But inorder to get the activity full name it is doing the self join I think. But I am not sure how to include these condition in those query. Can anyone please help

SELECT  
  (DECODE(levelLess3.activitynm, NULL, '', levelLess3.activitynm || '/') ) || 
 (DECODE(levelLess2.activitynm, NULL, '', levelLess2.activitynm || '/') ) || 
 (DECODE(levelLess1.activitynm, NULL, '', levelLess1.activitynm || '/') ) || 
   wa.activitynm as ACTIVITYFULLNM
  FROM wfaactivity wa
    LEFT OUTER JOIN WFAACTIVITY levelless1 ON (wa.parentid = levelless1.wfaactivityid
   AND levelless1.wfaactivityid != wa.wfaactivityid)
 LEFT OUTER JOIN WFAACTIVITY levelless2 ON (levelless1.parentid = levelless2.wfaactivityid
   AND levelless2.wfaactivityid != levelless1.wfaactivityid)
 LEFT OUTER JOIN WFAACTIVITY levelless3 ON (levelless2.parentid = levelless3.wfaactivityid
   AND levelless3.wfaactivityid != levelless2.wfaactivityid)

I want to include the condition like

where wa.wfaactivityid=wspan.wfaactivityid
and wspan.deletedsw='0'
and to_char(wspan.eventdtm, 'yyyymmdd') >= to_char(sysdate-12,'yyyymmdd')

wspan is another table from where we are taking deletedsw and eventdtm

yeah I gave like this

SELECT  
  (DECODE(levelLess3.activitynm, NULL, '', levelLess3.activitynm || '/') ) || 
 (DECODE(levelLess2.activitynm, NULL, '', levelLess2.activitynm || '/') ) || 
 (DECODE(levelLess1.activitynm, NULL, '', levelLess1.activitynm || '/') ) || 
   wa.activitynm as ACTIVITYFULLNM
  FROM wfaactivity wa,WFAREPACTYSPAN wspan
  where
  wa.wfaactivityid=wspan.wfaactivityid
and wspan.deletedsw='0'
and to_char(wspan.eventdtm, 'yyyymmdd') >= to_char(sysdate-12,'yyyymmdd')
and
    LEFT OUTER JOIN WFAACTIVITY levelless1 ON (wa.parentid = levelless1.wfaactivityid
   AND levelless1.wfaactivityid != wa.wfaactivityid)
 LEFT OUTER JOIN WFAACTIVITY levelless2 ON (levelless1.parentid = levelless2.wfaactivityid
   AND levelless2.wfaactivityid != levelless1.wfaactivityid)
 LEFT OUTER JOIN WFAACTIVITY levelless3 ON (levelless2.parentid = levelless3.wfaactivityid
   AND levelless3.wfaactivityid != levelless2.wfaactivityid)

But I am getting the error ORA-00920 Invalid relational operator

Upvotes: 1

Views: 316

Answers (2)

Adam Musch
Adam Musch

Reputation: 13583

It looks like you're trying to build a hierarchy - why not use a hierarchical query instead?

select reverse (sys_connect_by_path (reverse (wf.activitynm), '/') as activityfullnm
  from wfaactivity wf
       inner join wfarepactyspan wspan
          on wa.wfaactivityid = wspan.wfaactivityid
 start with (     wspan.deletedsw = 0 and 
              and wspan.eventdtm >= sysdate - 12)
 connect by nocycle prior wf.wfactivityid = wf.parentid 
 where level <= 4

Upvotes: 0

Guillem Vicens
Guillem Vicens

Reputation: 3996

Your select is failing because you are putting the join conditions into the where clause. Try this instead:

Select (DECODE(levelLess3.activitynm, Null, '', levelLess3.activitynm || '/') ) || 
       (DECODE(levelLess2.activitynm, Null, '', levelLess2.activitynm || '/') ) || 
       (DECODE(levelLess1.activitynm, Null, '', levelLess1.activitynm || '/') ) || 
       wa.activitynm As ACTIVITYFULLNM
  From wfaactivity wa
  Join WFAREPACTYSPAN wspan On wa.wfaactivityid=wspan.wfaactivityid
  Left Outer Join WFAACTIVITY levelless1 On (wa.parentid = levelless1.wfaactivityid
                                             And levelless1.wfaactivityid != wa.wfaactivityid)
  Left Outer Join WFAACTIVITY levelless2 On (levelless1.parentid = levelless2.wfaactivityid
   And levelless2.wfaactivityid != levelless1.wfaactivityid)
  Left Outer Join WFAACTIVITY levelless3 On (levelless2.parentid = levelless3.wfaactivityid
   And levelless3.wfaactivityid != levelless2.wfaactivityid)
 Where wspan.deletedsw='0'
   And TO_CHAR(wspan.eventdtm, 'yyyymmdd') >= TO_CHAR(SYSDATE-12,'yyyymmdd')

EDIT

You can join any tables you want in the way you need, but you have to be sure on how you are joining them. From your question and comment it seems (sorry if it is not the case) you are not very familiar with SQL joins.

If that is so I suggest you read this excellent explanation on SQL joins. Depending on how you join the tables you might not get the data you want.

HTH

Upvotes: 1

Related Questions