Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem when outer join not found but there's cure

Author  Topic 

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2014-03-19 : 11:38:58
In this query, we have left outer join to the formulary table.

Now I learnt that, some records not in this table based on NDC
but ARE in based on a SEQNO.

SO we have this alternative join if you will. how to code that
if the join on ndc is not found, let's join on SEQNO = SEQ_NO

"SELECT distinct f.FACILITY_NAME, ID_KEY, [BATCH] AS column1, [IMPORTDATE], [DATEBILLED], [RX], [DATEDISPENSED], [DAYSUPPLY], [PAYTYPE], [NPI], [PHYSICIAN], [COST], [QUANTITY], [MEDICATION], A.[NDC], " +
" case when COST > 0 then (COST / DAYSUPPLY) * 30 else 0 end [30DayCost] , [PATIENTNAME], [ROUTEOFADMIN], [INVOICECAT], [COPAY], [BRAND], [TIER], [SKILLLEVEL], [STAT] STATUS, [LASTTASKDATE],SEQNO,B.[SUBST_INSTRUCTIONS] , f.FACILITY_ID " +
" FROM [PBM].[T_CHARGES] A LEFT OUTER JOIN [OGEN].[NDC_M_FORMULARY] B ON A.[NDC] = B.[NDC] Left Outer Join PBM.FACILITY f on A.FACILITYNPI = f.FACILITY_NPI Where [STAT] not in (3, 4, 5) AND SUBST_INSTRUCTIONS is not null AND [TIER] <> 'T1' " +
sqlWhere + " AND f.FACILITY_ID IN (" + selected + ")";

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-19 : 12:15:19
Sample data? Expected output?
Go to Top of Page
   

- Advertisement -