?
Solved

Oracle SQL LISTAGG() syntax problem

Posted on 2012-09-22
7
Medium Priority
?
2,278 Views
Last Modified: 2012-09-23
I am using a larger version of the following query. EDW_CM_ID defines a unique patient. ICD9_DX_CD is a one to many diagnosis list per patient. I need only those patients who have at least one of the ICD9_DX_CD codes in the prescribed numeric text list (like postal codes).

After finding all patients who have the range of desired ICD9_DX_CDs, I want to use LISTAGG() to create a field that concatenates all related ICD codes for that patient. Using this syntax, however, all LISTAGG() returns is the one code that met the HAVING criteria. I have put the HAVING criteria both in a HAVING block and in the WHERE clause with similar results.

How do I fix it to return all ICD9_DX_CDs?

 SELECT a.ACCT_NO
          ,  a.FCILTY_ID
          ,  b.ICD9_DX_CD
          ,  a.EDW_CM_ID
          ,  LISTAGG ( b.ICD9_DX_CD, ',') WITHIN GROUP ( ORDER BY b.ICD9_DX_CD ) AS ICD9_LIST

     FROM  CM.CASEMIX_SMRY                  a  
LEFT JOIN  CM.ENCNTR_ICD9_DX             b  ON  ( a.EDW_CM_ID          = b.EDW_CM_ID        )
      WHERE  a.FCILTY_ID IN = ( 128, 154, 139, 143, 116, 128)
                 
GROUP BY      
          ,  a.EDW_CM_ID
          ,  a.FCILTY_ID
          ,  b.ICD9_DX_CD
          ,  a.ACCT_NO
                       
HAVING      b.ICD9_DX_CD in
               (   '433', '43300', '43301', '43311', '43320'
               , '43321' , '43331', '43380', '43381'
               , '43390', '43391',   '434', '43400', '43401'
               , '43410', '43411', '43490', '43491',   '435'
               ,  '4350',  '4351',  '4352',  '4358',  '4359'
               ,   '430',   '431',  '4329',   '436',  '4353'
               )
ORDER BY a.EDW_CM_ID, a.FCILTY_ID,  a.ACCT_NO
;
0
Comment
Question by:MarkJulie
  • 4
  • 3
7 Comments
 
LVL 2

Expert Comment

by:namethis
ID: 38425263
The fix to the having condition is simple.
And the column which is concatenated by listagg should not be used as a separate grouping column.

SELECT a.ACCT_NO
          ,  a.FCILTY_ID
--          ,  b.ICD9_DX_CD
          ,  a.EDW_CM_ID
          ,  LISTAGG ( b.ICD9_DX_CD, ',') WITHIN GROUP ( ORDER BY b.ICD9_DX_CD ) AS ICD9_LIST

     FROM  CM.CASEMIX_SMRY                  a  
LEFT JOIN  CM.ENCNTR_ICD9_DX             b  ON  ( a.EDW_CM_ID          = b.EDW_CM_ID        )
      WHERE  a.FCILTY_ID IN = ( 128, 154, 139, 143, 116, 128)
                 
GROUP BY      
          ,  a.EDW_CM_ID
          ,  a.FCILTY_ID
--          ,  b.ICD9_DX_CD
          ,  a.ACCT_NO
                        
HAVING   count( case when  b.ICD9_DX_CD in
               (   '433', '43300', '43301', '43311', '43320'
               , '43321' , '43331', '43380', '43381'
               , '43390', '43391',   '434', '43400', '43401'
               , '43410', '43411', '43490', '43491',   '435'
               ,  '4350',  '4351',  '4352',  '4358',  '4359'
               ,   '430',   '431',  '4329',   '436',  '4353'
               ) then 1 end)>0
ORDER BY a.EDW_CM_ID, a.FCILTY_ID,  a.ACCT_NO
;

Open in new window

0
 

Author Comment

by:MarkJulie
ID: 38425343
namethis,

That was the ideal fix for the ICD9_LIST field; however, in the process, I lose the value for b.ICD9_DX_CD as an independent value and the identifying criterion for each unique patient. What syntax will allow me to retain both b.ICD9_DX_CD and ICD9_LIST? An alias?
0
 
LVL 2

Expert Comment

by:namethis
ID: 38425778
This should work.

SELECT a.ACCT_NO
          ,  a.FCILTY_ID
          ,  b.ICD9_DX_CD
          ,  a.EDW_CM_ID
          ,  LISTAGG ( b.ICD9_DX_CD, ',') WITHIN GROUP ( ORDER BY b.ICD9_DX_CD ) 
over (partition by a.ACCT_NO ,  a.FCILTY_ID ,  a.EDW_CM_ID) AS ICD9_LIST

     FROM  CM.CASEMIX_SMRY                  a  
LEFT JOIN  CM.ENCNTR_ICD9_DX             b  ON  ( a.EDW_CM_ID          = b.EDW_CM_ID        )
      WHERE  a.FCILTY_ID IN = ( 128, 154, 139, 143, 116, 128)
                 
GROUP BY      
          ,  a.EDW_CM_ID
          ,  a.FCILTY_ID
          ,  b.ICD9_DX_CD
          ,  a.ACCT_NO
                        
HAVING   count( case when  b.ICD9_DX_CD in
               (   '433', '43300', '43301', '43311', '43320'
               , '43321' , '43331', '43380', '43381'
               , '43390', '43391',   '434', '43400', '43401'
               , '43410', '43411', '43490', '43491',   '435'
               ,  '4350',  '4351',  '4352',  '4358',  '4359'
               ,   '430',   '431',  '4329',   '436',  '4353'
               ) then 1 end)>0
ORDER BY a.EDW_CM_ID, a.FCILTY_ID,  a.ACCT_NO
;

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:MarkJulie
ID: 38425786
A follow-up question since I simplified the code for the purpose of the question:
If I actually have 32 SELECT fields (and 32 GROUP BY fields), do all of them need to be included in the OVER ( PARTITION BY... ) list?
0
 
LVL 2

Accepted Solution

by:
namethis earned 2000 total points
ID: 38425803
Ok, since EDW_CM_ID defines a unique patient, this field alone will be enough to aggregate all the codes for that patient. So the partition clause should be:
over (partition by a.EDW_CM_ID)

Open in new window

0
 

Author Comment

by:MarkJulie
ID: 38427594
FYI, the single partition value returned an error which was eliminated by including all the nonaggregate fields in SELECT and GROUP BY.

Thanks for all the help.
0
 

Author Closing Comment

by:MarkJulie
ID: 38427597
Rapid response with answers easily applicable to my larger question.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question