SQL 2005 / 2008
I have SQL QUery and its ouput attached in this Question.
The query runs on a Left Join Condition ..
Which picks First 9 digit of NDC on either side and results ..!
From the result, we came across few rows for Generic Code has NULL Value it ..!
In order to fix or update the NULL Value, we need to run an Update Left join Query with new condition ... By picking First 10 digits of NDC on either Side.
The second condition has to run only if i got NULL Values on Generic Code for those specific rows ..!
How should i have two conditional join .. with select and update ..
select Distinct PC.ID,
from PlacedOrderDetails PC
Left Join Drug Dr
on convert(varchar, Left(PC.NDC ,9),101) = convert(varchar, Left(case when Dr.DrugNDCType = 49 then Dr.DrugNDCNbr else stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6 when Dr.DrugNDCType = 52 then 10 end, 0, '0') end ,9),101)
Order by PC.ID ASC