We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL - Conditional Join Query

Medium Priority
678 Views
Last Modified: 2012-05-11
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,
							Dr.GenericCode,
							PC.[Generic Name],
							PC.[NDC]
				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

Open in new window

GetGenericCode.xls
Comment
Watch Question

Brendt HessSenior DBA
CERTIFIED EXPERT

Commented:
As I am understanding your question:  The output for the above query returns some items with a NULL Dr.GenericCode value.  These are cases where the entry in the Drug table is missing.  This, however, leads to a bit of confusion on my part.  You state:

    "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"

From this, I assume that the Drug table has entries that need to be modified?  Or did you mean that you needed to add entries?  I'm just not certain what table is being updated, with what value, for what record.  An example of data and expected results for this data modification would be very helpful.
Dont messup your null update with this join.

1. Use a transaction.

2. Create an ALIAS table (Inmemory) to hold your outupt with null.

3. Run an update on the alias table to update the null.

3. Close the Transaction.

Also, What is the volume of data in your tables, Is is too high?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
chokkaStudent

Author

Commented:
cojdev: Thank you.  

Current Syntax results duplication .. ( I am getting duplicate even before .. just because of Left Join )

Id                   Generic Code  Generic Name                   NDC
475      47780      INSULIN GLARGINE      00088222033
475      50836      INSULIN GLARGINE      00088222033
475      62867      INSULIN GLARGINE      00088222033


Whether it is possible for us to tune the your syntax in such a way to pick MAX(Generic Code) by using
 row_number() over by partition ...!

On doing so, i think we can eliminate duplicate Id's

Commented:
Does this work to remove the dupes?
select	Distinct	
	PC.ID,
	MAX(COALESCE(Dr.GenericCode, Dr2.GenericCode)) 'GenericCode',
	PC.[Generic Name],
	PC.[NDC]
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)
Left Join	Drug Dr2 on convert(varchar, Left(PC.NDC ,10),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 ,10),101)
Order by	PC.ID ASC 
group by PC.ID, 	PC.[Generic Name],	PC.[NDC]

Open in new window

chokkaStudent

Author

Commented:
Cojdev, i have opened a seperate thread. In this thread, i have clarified my issue.
Thank you
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.