Query not returning expected records

• kcbjcb.brown

  Old Hand

  Points: 304

  More actions

  January 19, 2011 at 6:44 am

  #233435

  I have a query where I know the data exists but the resulting query does not give me the records I am expecting. The where clause is a simple "state= 'NY'" but nothing is returned. If I remove the where clause, you would see the NY records. I think it could be caused how the state is found, but not sure.

  Any suggestions?

  SELECT yn, ClaimMst_I, ClaimAdj_I, Claim_Num, Date_Rec, Due_Date, Invoice_Da,

  Bill_Numbr, Ext_Patien, Catg_ID, #ClaimAdj1.Site_ID, Rend_ID, Depend_ID,

  #ClaimAdj1.prov_id, Adjud_Date, #ClaimAdj1.Touch_Date, Net_Status, Adjud_Doll,

  Pay_Doll, Act_Doll, Ded_Doll, Cop_Doll, Units, Whld_Amt, WhldCheck_ID,

  Age_Day, Age_Rec, Patient_ID, ServCp_ID, Agre_Mode, LOC_ID, Auth_Numb,

  Old_ID, InsuSpan_I, Insu_ID, Vend_Desc,

  ISNULL(payBank.Eft_Ind, ISNULL(mastbank.Eft_Ind,0)) AS Eft_Ind,

  Last_Name, Middle, First_Name, BirthDate, Sex,

  MSI_Number, Phead_ID, Grp_ID, contr_name,

  InsuStat_ID, Pay_Flag,

  Address1 = CASE WHEN ProvSite2.Bill_Addr1 IS NULL THEN

  CASE WHEN ProvSite3.Bill_Addr1 <> '' THEN ProvSite3.Bill_Addr1

  WHEN ProvSite3.Mail_Addr1 <> '' THEN ProvSite3.Mail_Addr1

  ELSE ProvSite3.Phys_Addr1 END

  ELSE

  CASE WHEN ProvSite2.Bill_Addr1 <> '' THEN ProvSite2.Bill_Addr1

  WHEN ProvSite2.Mail_Addr1 <> '' THEN ProvSite2.Mail_Addr1

  ELSE ProvSite2.Phys_Addr1 END

  END,

  Address2 = CASE WHEN ProvSite2.Bill_Addr1 IS NULL THEN

  CASE WHEN ProvSite3.Bill_Addr1 <> '' THEN ProvSite3.Bill_Addr2

  WHEN ProvSite3.Mail_Addr1 <> '' THEN ProvSite3.Mail_Addr2

  ELSE ProvSite3.Phys_Addr2 END

  ELSE

  CASE WHEN ProvSite2.Bill_Addr1 <> '' THEN ProvSite2.Bill_Addr2

  WHEN ProvSite2.Mail_Addr1 <> '' THEN ProvSite2.Mail_Addr2

  ELSE ProvSite2.Phys_Addr2 END

  END,

  Address3 = CASE WHEN ProvSite2.Bill_Addr1 IS NULL THEN

  CASE WHEN ProvSite3.Bill_Addr1 <> '' THEN ProvSite3.Bill_Addr3

  WHEN ProvSite3.Mail_Addr1 <> '' THEN ProvSite3.Mail_Addr3

  ELSE ProvSite3.Phys_Addr3 END

  ELSE

  CASE WHEN ProvSite2.Bill_Addr1 <> '' THEN ProvSite2.Bill_Addr3

  WHEN ProvSite2.Mail_Addr1 <> '' THEN ProvSite2.Mail_Addr3

  ELSE ProvSite2.Phys_Addr3 END

  END,

  City = CASE WHEN ProvSite2.Bill_Addr1 IS NULL THEN

  CASE WHEN ProvSite3.Bill_Addr1 <> '' THEN ProvSite3.Bill_City

  WHEN ProvSite3.Mail_Addr1 <> '' THEN ProvSite3.Mail_City

  ELSE ProvSite3.Phys_City END

  ELSE

  CASE WHEN ProvSite2.Bill_Addr1 <> '' THEN ProvSite2.Bill_City

  WHEN ProvSite2.Mail_Addr1 <> '' THEN ProvSite2.Mail_City

  ELSE ProvSite2.Phys_City END

  END,

  State = CASE WHEN ProvSite2.Bill_Addr1 IS NULL THEN

  CASE WHEN ProvSite3.Bill_Addr1 <> '' THEN ProvSite3.Bill_State

  WHEN ProvSite3.Mail_Addr1 <> '' THEN ProvSite3.Mail_State

  ELSE ProvSite3.Phys_State END

  ELSE

  CASE WHEN ProvSite2.Bill_Addr1 <> '' THEN ProvSite2.Bill_State

  WHEN ProvSite2.Mail_Addr1 <> '' THEN ProvSite2.Mail_State

  ELSE ProvSite2.Phys_State END

  END,

  Zip = CASE WHEN ProvSite2.Bill_Addr1 IS NULL THEN

  CASE WHEN ProvSite3.Bill_Addr1 <> '' THEN ProvSite3.Bill_Zip

  WHEN ProvSite3.Mail_Addr1 <> '' THEN ProvSite3.Mail_Zip

  ELSE ProvSite3.Phys_Zip END

  ELSE

  CASE WHEN ProvSite2.Bill_Addr1 <> '' THEN ProvSite2.Bill_Zip

  WHEN ProvSite2.Mail_Addr1 <> '' THEN ProvSite2.Mail_Zip

  ELSE ProvSite2.Phys_Zip END

  END,

  ISNULL(ProvSite2.taxa_fed_I, ProvSite3.taxa_fed_I) AS taxa_fed_I,

  name, non_par, ClaimDet_I, aBank_ID, ap_acct, cash_acct

  FROM #ClaimAdj1

  LEFT OUTER JOIN ProvSite ProvSite2 ON ProvSite2.Prov_ID = #ClaimAdj1.Prov_ID

  AND ProvSite2.PayToSiteFlag = 1

  INNER JOIN ProvSite ProvSite3 ON ProvSite3.Site_ID = #ClaimAdj1.Site_ID

  LEFT OUTER JOIN ProvBank paybank ON paybank.Site_ID = ProvSite2.Site_ID

  LEFT OUTER JOIN ProvBank mastbank ON mastbank.Site_ID = ProvSite3.Site_ID

  where state = 'NY'

  ORDER BY #ClaimAdj1.touch_date

• Ronald H

  SSCarpal Tunnel

  Points: 4053

  More actions

  January 19, 2011 at 8:40 am

  #1275354

  Copying just the entire SQL query text without any table desc often won't result in many replies, like this time.

  However, I see State is in this query an alias, in the where clause you should use the original CASE statement, like this:

  SELECT yn, ClaimMst_I, ClaimAdj_I, Claim_Num, Date_Rec, Due_Date, Invoice_Da,

  Bill_Numbr, Ext_Patien, Catg_ID, #ClaimAdj1.Site_ID, Rend_ID, Depend_ID,

  #ClaimAdj1.prov_id, Adjud_Date, #ClaimAdj1.Touch_Date, Net_Status, Adjud_Doll,

  Pay_Doll, Act_Doll, Ded_Doll, Cop_Doll, Units, Whld_Amt, WhldCheck_ID,

  Age_Day, Age_Rec, Patient_ID, ServCp_ID, Agre_Mode, LOC_ID, Auth_Numb,

  Old_ID, InsuSpan_I, Insu_ID, Vend_Desc,

  ISNULL(payBank.Eft_Ind, ISNULL(mastbank.Eft_Ind,0)) AS Eft_Ind,

  Last_Name, Middle, First_Name, BirthDate, Sex,

  MSI_Number, Phead_ID, Grp_ID, contr_name,

  InsuStat_ID, Pay_Flag,

  Address1 = CASE WHEN ProvSite2.Bill_Addr1 IS NULL THEN

  CASE WHEN ProvSite3.Bill_Addr1 <> '' THEN ProvSite3.Bill_Addr1

  WHEN ProvSite3.Mail_Addr1 <> '' THEN ProvSite3.Mail_Addr1

  ELSE ProvSite3.Phys_Addr1 END

  ELSE

  CASE WHEN ProvSite2.Bill_Addr1 <> '' THEN ProvSite2.Bill_Addr1

  WHEN ProvSite2.Mail_Addr1 <> '' THEN ProvSite2.Mail_Addr1

  ELSE ProvSite2.Phys_Addr1 END

  END,

  Address2 = CASE WHEN ProvSite2.Bill_Addr1 IS NULL THEN

  CASE WHEN ProvSite3.Bill_Addr1 <> '' THEN ProvSite3.Bill_Addr2

  WHEN ProvSite3.Mail_Addr1 <> '' THEN ProvSite3.Mail_Addr2

  ELSE ProvSite3.Phys_Addr2 END

  ELSE

  CASE WHEN ProvSite2.Bill_Addr1 <> '' THEN ProvSite2.Bill_Addr2

  WHEN ProvSite2.Mail_Addr1 <> '' THEN ProvSite2.Mail_Addr2

  ELSE ProvSite2.Phys_Addr2 END

  END,

  Address3 = CASE WHEN ProvSite2.Bill_Addr1 IS NULL THEN

  CASE WHEN ProvSite3.Bill_Addr1 <> '' THEN ProvSite3.Bill_Addr3

  WHEN ProvSite3.Mail_Addr1 <> '' THEN ProvSite3.Mail_Addr3

  ELSE ProvSite3.Phys_Addr3 END

  ELSE

  CASE WHEN ProvSite2.Bill_Addr1 <> '' THEN ProvSite2.Bill_Addr3

  WHEN ProvSite2.Mail_Addr1 <> '' THEN ProvSite2.Mail_Addr3

  ELSE ProvSite2.Phys_Addr3 END

  END,

  City = CASE WHEN ProvSite2.Bill_Addr1 IS NULL THEN

  CASE WHEN ProvSite3.Bill_Addr1 <> '' THEN ProvSite3.Bill_City

  WHEN ProvSite3.Mail_Addr1 <> '' THEN ProvSite3.Mail_City

  ELSE ProvSite3.Phys_City END

  ELSE

  CASE WHEN ProvSite2.Bill_Addr1 <> '' THEN ProvSite2.Bill_City

  WHEN ProvSite2.Mail_Addr1 <> '' THEN ProvSite2.Mail_City

  ELSE ProvSite2.Phys_City END

  END,

  State = CASE WHEN ProvSite2.Bill_Addr1 IS NULL THEN

  CASE WHEN ProvSite3.Bill_Addr1 <> '' THEN ProvSite3.Bill_State

  WHEN ProvSite3.Mail_Addr1 <> '' THEN ProvSite3.Mail_State

  ELSE ProvSite3.Phys_State END

  ELSE

  CASE WHEN ProvSite2.Bill_Addr1 <> '' THEN ProvSite2.Bill_State

  WHEN ProvSite2.Mail_Addr1 <> '' THEN ProvSite2.Mail_State

  ELSE ProvSite2.Phys_State END

  END,

  Zip = CASE WHEN ProvSite2.Bill_Addr1 IS NULL THEN

  CASE WHEN ProvSite3.Bill_Addr1 <> '' THEN ProvSite3.Bill_Zip

  WHEN ProvSite3.Mail_Addr1 <> '' THEN ProvSite3.Mail_Zip

  ELSE ProvSite3.Phys_Zip END

  ELSE

  CASE WHEN ProvSite2.Bill_Addr1 <> '' THEN ProvSite2.Bill_Zip

  WHEN ProvSite2.Mail_Addr1 <> '' THEN ProvSite2.Mail_Zip

  ELSE ProvSite2.Phys_Zip END

  END,

  ISNULL(ProvSite2.taxa_fed_I, ProvSite3.taxa_fed_I) AS taxa_fed_I,

  name, non_par, ClaimDet_I, aBank_ID, ap_acct, cash_acct

  FROM #ClaimAdj1

  LEFT OUTER JOIN ProvSite ProvSite2 ON ProvSite2.Prov_ID = #ClaimAdj1.Prov_ID

  AND ProvSite2.PayToSiteFlag = 1

  INNER JOIN ProvSite ProvSite3 ON ProvSite3.Site_ID = #ClaimAdj1.Site_ID

  LEFT OUTER JOIN ProvBank paybank ON paybank.Site_ID = ProvSite2.Site_ID

  LEFT OUTER JOIN ProvBank mastbank ON mastbank.Site_ID = ProvSite3.Site_ID

  where CASE WHEN ProvSite2.Bill_Addr1 IS NULL THEN

  CASE WHEN ProvSite3.Bill_Addr1 <> '' THEN ProvSite3.Bill_State

  WHEN ProvSite3.Mail_Addr1 <> '' THEN ProvSite3.Mail_State

  ELSE ProvSite3.Phys_State END

  ELSE

  CASE WHEN ProvSite2.Bill_Addr1 <> '' THEN ProvSite2.Bill_State

  WHEN ProvSite2.Mail_Addr1 <> '' THEN ProvSite2.Mail_State

  ELSE ProvSite2.Phys_State END

  END = 'NY'

  ORDER BY #ClaimAdj1.touch_date

  or the entire query without the where clause into a subquery like this:

  SELECT * FROM (SELECT ... ) WHERE State = 'NY' ORDER BY #ClaimAdj1.touch_date

  Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2