[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 666
  • Last Modified:

SQL - Conditional Join Query

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
0
chokka
Asked:
chokka
1 Solution
 
Brendt HessSenior DBACommented:
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.
0
 
radcaesarCommented:
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?
0
 
cojdevCommented:
Do you really want to join on the first 10 if the value is null. Or do you want to join on less characters of 8.

I have attached aquery that might work based on what you have stated. The query uses a second left join and the result is only used if the first value is null.
select	Distinct	
	PC.ID,
	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

Open in new window

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
chokkaAuthor 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
0
 
cojdevCommented:
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

0
 
chokkaAuthor Commented:
Cojdev, i have opened a seperate thread. In this thread, i have clarified my issue.
Thank you
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now