By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,384 Members | 671 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,384 IT Pros & Developers. It's quick & easy.

I have a list- null, 1,2,3 and am asking for only the 1 but keep getting 1 and null.

P: 3
In a subquery I only want the line 1 from a list of null, 1,2 and 3, but I keep getting line 1 and null. How can I get only line 1?
Expand|Select|Wrap|Line Numbers
  1.   (select HOSPICE_DX.PAT_ENC_CSN_ID
  2.            , edg.DX_NAME
  3.            , HOSPICE_DX.LINE
  4.             from HOSPICE_DX
  5.            inner join EDG on    HOSPICE_DX.HOSPICE_DX_CODED_ID=edg.DX_ID
  6.  
  7.            ) hospice on PAT_ENC.PAT_ENC_CSN_ID=hospice.PAT_ENC_CSN_ID and hospice.LINE=1  
thanks!
Jul 26 '13 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,359
We need to see the rest of the SQL.
Jul 26 '13 #2

P: 3
Expand|Select|Wrap|Line Numbers
  1. select PATIENT.PAT_MRN_ID as MRN
  2. , PATIENT.PAT_NAME as Patient
  3. , convert(date,HH_EPSD_EVNT_DATES.DATE_OF_EVENT,101) as SOC
  4. , CASE when ZC_ACCT_CLASS_HA.NAME='Home Health' then diagnosis.DX_NAME else hospice.DX_NAME end as 'PRIMARY DX'
  5. , ZC_TEAM_ASSIGNMENT.NAME as TEAM
  6. , ZC_ACCT_CLASS_HA.NAME AS PROGRAM
  7. , hospice.LINE
  8.  
  9.  
  10.  
  11. from HSP_ACCOUNT
  12. inner join ZC_ACCT_CLASS_HA on HSP_ACCOUNT.ACCT_CLASS_HA_C=ZC_ACCT_CLASS_HA.ACCT_CLASS_HA_C
  13. inner join HSP_ACCOUNT_3 on HSP_ACCOUNT.HSP_ACCOUNT_ID=HSP_ACCOUNT_3.HSP_ACCOUNT_ID
  14. inner join HSP_TRANSACTIONS on HSP_ACCOUNT.HSP_ACCOUNT_ID=HSP_TRANSACTIONS.HSP_ACCOUNT_ID
  15. inner join PATIENT on HSP_ACCOUNT.PAT_ID=PATIENT.PAT_ID
  16. inner join PAT_ENC on HSP_ACCOUNT.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID
  17. inner join EPISODE on HSP_ACCOUNT_3.HH_HSB_ID=EPISODE.EPISODE_ID
  18. inner join HH_EPSD_INFO on EPISODE.EPISODE_ID=HH_EPSD_INFO.SUMMARY_BLOCK_ID
  19. left join ZC_TEAM_ASSIGNMENT on HH_EPSD_INFO.TEAM_ASSIGNMENT_C=ZC_TEAM_ASSIGNMENT.TEAM_ASSIGNMENT_C
  20. inner join HH_EPSD_EVNT_DATES on HH_EPSD_INFO.SUMMARY_BLOCK_ID=HH_EPSD_EVNT_DATES.SUMMARY_BLOCK_ID
  21. inner join ZC_DATE_EVENT on HH_EPSD_EVNT_DATES.DATE_EVENT_C=ZC_DATE_EVENT.DATE_EVENT_C
  22. inner join HH_EPSD_EVNT_DATES as ed on HH_EPSD_INFO.SUMMARY_BLOCK_ID=ed.SUMMARY_BLOCK_ID
  23. inner join ZC_MC_PAT_STATUS on HH_EPSD_INFO.HC_DC_DISPOSITION_C=ZC_MC_PAT_STATUS.PAT_STATUS_C
  24. inner join CLARITY_DEP on HSP_TRANSACTIONS.DEPARTMENT=CLARITY_DEP.DEPARTMENT_ID
  25. left join (select HH_PAT_ENC.PAT_ID
  26.             , ROW_NUMBER() over(partition by hh_pat_enc.pat_id order by hh_pat_enc.prim_dx_start_date desc)enc_rank
  27.             , CLARITY_EDG.DX_NAME
  28.              from HH_PAT_ENC
  29.               inner join CLARITY_EDG on HH_PAT_ENC.PRIMARY_DX_ID=CLARITY_EDG.DX_ID
  30.                ) diagnosis on PATIENT.PAT_ID=diagnosis.PAT_ID and diagnosis.enc_rank='1'
  31. left join (select HOSPICE_DX.PAT_ENC_CSN_ID
  32.            , HOSPICE_DX.CONTACT_DATE
  33.            , edg.DX_NAME
  34.            , HOSPICE_DX.LINE
  35.             from HOSPICE_DX
  36.            inner join CLARITY_EDG as edg on HOSPICE_DX.HOSPICE_DX_CODED_ID=edg.DX_ID
  37.  
  38.            ) hospice on PAT_ENC.PAT_ENC_CSN_ID=hospice.PAT_ENC_CSN_ID and hospice.LINE=1 
  39.  
  40.  
  41. where ZC_DATE_EVENT.NAME in ('Start of Care Date (M0030,CMS 485.2)', 'Hospice Admission')
  42. and ZC_MC_PAT_STATUS.NAME <>  'Left Against Medical Advice or Discontinued Care'
  43. and EPISODE.STATUS_C<>'3'
  44.  
  45.  
  46.  
  47.  
  48.  
  49. group by EPISODE.EPISODE_ID
  50. , PATIENT.PAT_MRN_ID 
  51. , PATIENT.PAT_NAME
  52. , HH_EPSD_EVNT_DATES.DATE_OF_EVENT
  53. , ZC_TEAM_ASSIGNMENT.NAME 
  54. , ZC_ACCT_CLASS_HA.NAME
  55. , hospice.DX_NAME 
  56. , hospice.LINE
  57. , diagnosis.DX_NAME 
  58.  
  59.  
  60. order by 1 
Jul 26 '13 #3

Rabbit
Expert Mod 10K+
P: 12,359
It's because you did a left join. A left join returns any rows in the left table that match the right table in addition to any rows in the left table that don't match any rows in the right table.
Jul 26 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Browse more MySQL Database Questions on Bytes