asked on

Have query results pull data from another query if null values are present

I have the following Query:

SELECT *
FROM [IPOP].[dbo].[v_dxbyrecid]

Resulting data is in the attached file. If you notice, record_ids have values in dx1, dx2, dx3 etc, and some only have value in dx1. What I want to do is add values into the first first null "dx#" field in the above query.

The values I want to add are from the following query:
select record_id,
substring(dxe,1,5) as e1,
substring(dxe, 8, 1) as e1poa,
substring(dxe2,1,5) as e2,
substring(dxe2, 8, 1) as e2poa,
substring(dxe3,1,5) as e3,
substring(dxe3, 8, 1) as e3poa
from tblprimary

And the results are also in the file.

What I want to see for expected results are also included. Thanks in advance for the help!

experts-exchange.xls

mdagis

The only NON ugly approach I can think is to open 2 cursors, 1 for each select.

Then find the first not null column from tblprimary and insert it to the first not null column of v_dxbyrecid until you reach the last column of tblprimary .

Note that this will result to 1 update for each cell found so I would not recomend doing it for Heavy data tables.

mtuepker

ASKER

Im working on an ugly solution below...but getting confused in the logic. Could you point me in the right direction for using cursors? I've never used them before.

case 
      when isnull(dx1,'y') + isnull(dx2,'y') = 'yy' then e2
      when isnull(dx1,'y') + isnull(dx2,'y') = isnull(dx1,'y') + 'y' then e1
      else dx2 end as dx2
      ,[dx2pa]
      ,
      case 
      when isnull(dx1,'y') + isnull(dx2,'y') + isnull(dx3,'y')= 'yyy' then e3
      when isnull(dx1,'y') + isnull(dx2,'y') + isnull(dx3,'y')= isnull(dx1,'y') + 'yy' then e2
      when isnull(dx1,'y') + isnull(dx2,'y') + isnull(dx3,'y')= isnull(dx1,'y') + isnull(dx2,'y') + 'y' then e1
      else dx3 end as dx3

Open in new window

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mtuepker

ASKER

Cyber -

That makes my brain hurt!

I think I needed to be a bit more clear on what I am trying to do and that that was just a sample of data. There are actually a few million record_ids, so I dont think this would be practical to scale up. I just picked 3 records for the sample data.

cyberkiwi

It gives you an approach anyway and pivot, unpivot are not too slow. There's just a union thrown in there to get fields from main table and subs from the other select to line up in a single "pool"

You can always try with

select top 1000...
from @v_dxbyrecid order by record_id

select top 1000...
from @tblprimary order by record_id

in the first part to get a feel for how long it takes to do 1000 records

mtuepker

ASKER

Sorry Cyber, long day and brain is fried. Trying to pull from the actual tables and receive the following:

Msg 8167, Level 16, State 1, Line 2
The type of column "dx1pa" conflicts with the type of other columns specified in the UNPIVOT list.

I actually do not need the PA/POA so I removed them from the query and we're running. I did not verify the results, but to get through the first 50 rows, it took about 1'15", so Im not sure this will work.

mtuepker

ASKER

I should also mention that the v_dxbyrecid view is made up of the following, which could explain some of the slow processing

with cte3 as (SELECT     *, row_number() OVER (partition BY record_id
ORDER BY diagnosis_order) rn
FROM         tbldiagnosis)
    SELECT     record_id, max(CASE rn WHEN 1 THEN substring(dx, 1, 5) END) AS dx1, max(CASE rn WHEN 1 THEN substring(dx, 8, 1) END) AS dx1pa, 
                            max(CASE rn WHEN 2 THEN substring(dx, 1, 5) END) AS dx2, max(CASE rn WHEN 2 THEN substring(dx, 8, 1) END) AS dx2pa, 
                            max(CASE rn WHEN 3 THEN substring(dx, 1, 5) END) AS dx3, max(CASE rn WHEN 3 THEN substring(dx, 8, 1) END) AS dx3pa, 
                            max(CASE rn WHEN 4 THEN substring(dx, 1, 5) END) AS dx4, max(CASE rn WHEN 4 THEN substring(dx, 8, 1) END) AS dx4pa, 
                            max(CASE rn WHEN 5 THEN substring(dx, 1, 5) END) AS dx5, max(CASE rn WHEN 5 THEN substring(dx, 8, 1) END) AS dx5pa, 
                            max(CASE rn WHEN 6 THEN substring(dx, 1, 5) END) AS dx6, max(CASE rn WHEN 6 THEN substring(dx, 8, 1) END) AS dx6pa, 
                            max(CASE rn WHEN 7 THEN substring(dx, 1, 5) END) AS dx7, max(CASE rn WHEN 7 THEN substring(dx, 8, 1) END) AS dx7pa, 
                            max(CASE rn WHEN 8 THEN substring(dx, 1, 5) END) AS dx8, max(CASE rn WHEN 8 THEN substring(dx, 8, 1) END) AS dx8pa, 
                            max(CASE rn WHEN 9 THEN substring(dx, 1, 5) END) AS dx9, max(CASE rn WHEN 9 THEN substring(dx, 8, 1) END) AS dx9pa, 
                            max(CASE rn WHEN 10 THEN substring(dx, 1, 5) END) AS dx10, max(CASE rn WHEN 10 THEN substring(dx, 8, 1) END) AS dx10pa, 
                            max(CASE rn WHEN 11 THEN substring(dx, 1, 5) END) AS dx11, max(CASE rn WHEN 11 THEN substring(dx, 8, 1) END) AS dx11pa, 
                            max(CASE rn WHEN 12 THEN substring(dx, 1, 5) END) AS dx12, max(CASE rn WHEN 12 THEN substring(dx, 8, 1) END) AS dx12pa, 
                            max(CASE rn WHEN 13 THEN substring(dx, 1, 5) END) AS dx13, max(CASE rn WHEN 13 THEN substring(dx, 8, 1) END) AS dx13pa, 
                            max(CASE rn WHEN 14 THEN substring(dx, 1, 5) END) AS dx14, max(CASE rn WHEN 14 THEN substring(dx, 8, 1) END) AS dx14pa, 
                            max(CASE rn WHEN 15 THEN substring(dx, 1, 5) END) AS dx15, max(CASE rn WHEN 15 THEN substring(dx, 8, 1) END) AS dx15pa, 
                            max(CASE rn WHEN 16 THEN substring(dx, 1, 5) END) AS dx16, max(CASE rn WHEN 16 THEN substring(dx, 8, 1) END) AS dx16pa, 
                            max(CASE rn WHEN 17 THEN substring(dx, 1, 5) END) AS dx17, max(CASE rn WHEN 17 THEN substring(dx, 8, 1) END) AS dx17pa, 
                            max(CASE rn WHEN 18 THEN substring(dx, 1, 5) END) AS dx18, max(CASE rn WHEN 18 THEN substring(dx, 8, 1) END) AS dx18pa, 
                            max(CASE rn WHEN 19 THEN substring(dx, 1, 5) END) AS dx19, max(CASE rn WHEN 19 THEN substring(dx, 8, 1) END) AS dx19pa, 
                            max(CASE rn WHEN 20 THEN substring(dx, 1, 5) END) AS dx20, max(CASE rn WHEN 20 THEN substring(dx, 8, 1) END) AS dx20pa
     FROM         CTE3
     WHERE     rn < 21
     GROUP BY record_id

Open in new window

cyberkiwi

My goodness, it looks like v_dxbyrecid is already PIVOT-ing on a single column dx.
And here we are unpivoting it again back to dx, then finally, re-pivoting to the 20 columns.

It is no wonder it is so slow.... (pivot - not using pivot keyword, unpivot+add subs, pivot again)

You might want to rework that view to work on the base data directly. The 20 lines of max(case.. doesn't look very promising.

mtuepker

ASKER

I created that for another process. Basically, I have a table w/ record_id multiple times and multiple different DX's. I needed to have 1 row of data for each record_id to link to something else I was doing.

Now this dxe issue came up for something different, so trying to find a way to make it work.

SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mtuepker

ASKER

Alright, here is what I have come up with, works reasonably fast and will let me go to bed tonight.

I created a new view w/ the code below and then pointed the view v_dxbyrecid to pull data from the view below. May not be the most elegant, but works.

Im still open to other suggestions though. Thanks!


SELECT     [record_id], [dx], [diagnosis_order]
FROM         [IPOP].[dbo].[tblDiagnosis]
UNION
SELECT     record_id, substring(dxe, 1, 5) AS dx, '21' AS diagnosis_order
FROM         tblprimary
WHERE     dxe <> ''
UNION
SELECT     record_id, substring(dxe2, 1, 5) AS dx, '22' AS diagnosis_order
FROM         tblprimary
WHERE     dxe2 <> ''
UNION
SELECT     record_id, substring(dxe3, 1, 5) AS dx, '23' AS diagnosis_order
FROM         tblprimary
WHERE     dxe3 <> ''

Open in new window

cyberkiwi

Adding 3 lines to 20 of

max(CASE rn WHEN 18 THEN substring(dx, 1, 5) END) AS dx18, max(CASE rn WHEN 18 THEN substring(dx, 8, 1) END) AS dx18pa,
max(CASE rn WHEN 19 THEN substring(dx, 1, 5) END) AS dx19, max(CASE rn WHEN 19 THEN substring(dx, 8, 1) END) AS dx19pa,
max(CASE rn WHEN 20 THEN substring(dx, 1, 5) END) AS dx20, max(CASE rn WHEN 20 THEN substring(dx, 8, 1) END) AS dx20pa
.. etc

can't be any less elegant :)

Glad you sorted it.

mtuepker

ASKER

The solution kiwi posted did indeed work, but it ended up being easier to go back to the initial query and change it to meet my needs. Look at my last post for the final solution.