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
LVL 1
mtuepkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mdagisCommented:
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.
0
mtuepkerAuthor Commented:
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

0
cyberkiwiCommented:
Try this
declare @v_dxbyrecid table (
record_id	varchar(100), 
dx1 varchar(10),dx1pa varchar(10),dx2 varchar(10),dx2pa varchar(10),dx3 varchar(10),dx3pa varchar(10),
dx4 varchar(10),dx4pa varchar(10),dx5 varchar(10),dx5pa varchar(10),dx6 varchar(10),dx6pa varchar(10),
dx7 varchar(10),dx7pa varchar(10),dx8 varchar(10),dx8pa varchar(10),dx9 varchar(10),dx9pa varchar(10),
dx10 varchar(10),dx10pa varchar(10),dx11 varchar(10),dx11pa varchar(10),dx12 varchar(10),dx12pa varchar(10),
dx13 varchar(10),dx13pa varchar(10),dx14 varchar(10),dx14pa varchar(10),dx15 varchar(10),dx15pa varchar(10),
dx16 varchar(10),dx16pa varchar(10),dx17 varchar(10),dx17pa varchar(10),dx18 varchar(10),dx18pa varchar(10),
dx19 varchar(10),dx19pa varchar(10),dx20 varchar(10),dx20pa varchar(10))
insert @v_dxbyrecid select
'57821080-93CC-43C9-ACBA-03FF6117FBDD','V726','',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null
insert @v_dxbyrecid select
'4ADBCE08-CC9D-4729-861B-03FF6117FBEA','53081','','311','','V1272','','V160','','V103','','V153','',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null
insert @v_dxbyrecid select
'2B713349-8797-477E-AFA7-03FF6117FBF6','66401','Y','V270','',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null
declare @tblprimary table (record_id varchar(100), dxe varchar(100), dxe2 varchar(100), dxe3 varchar(100))
insert @tblprimary select '57821080-93CC-43C9-ACBA-03FF6117FBDD', 'E9203', null, null
insert @tblprimary select '4ADBCE08-CC9D-4729-861B-03FF6117FBEA', 'E8199', 'E8859', 'E8499'
insert @tblprimary select '2B713349-8797-477E-AFA7-03FF6117FBF6', 'E9689  1', 'E8495  1', ''
;
--------- remove @ below this line to work with the actual tables instead of temp tables above
with cte(record_id, dxfield, dxvalue) as
(
select record_id, dxfield, dxvalue from (
	select record_id, dx1,dx1pa,dx2,dx2pa,dx3,dx3pa,dx4,dx4pa,dx5,dx5pa,dx6,dx6pa,dx7,dx7pa,
	dx8,dx8pa,dx9,dx9pa,dx10,dx10pa,dx11,dx11pa,dx12,dx12pa,dx13,dx13pa,dx14,dx14pa,
	dx15,dx15pa,dx16,dx16pa,dx17,dx17pa,dx18,dx18pa,dx19,dx19pa,dx20,dx20pa
	from @v_dxbyrecid) P
UNPIVOT	(
	dxValue FOR dxField IN 
		(dx1,dx1pa,dx2,dx2pa,dx3,dx3pa,dx4,dx4pa,dx5,dx5pa,dx6,dx6pa,dx7,dx7pa,
		dx8,dx8pa,dx9,dx9pa,dx10,dx10pa,dx11,dx11pa,dx12,dx12pa,dx13,dx13pa,dx14,dx14pa,
		dx15,dx15pa,dx16,dx16pa,dx17,dx17pa,dx18,dx18pa,dx19,dx19pa,dx20,dx20pa)
	) PVT
union all
select record_id, dxfield, dxvalue from (
	select record_id,
	substring(dxe,1,5) as e1,
	convert(varchar(5),substring(dxe, 8, 1)) as e1poa, 
	substring(dxe2,1,5) as e2,
	convert(varchar(5),substring(dxe2, 8, 1)) as e2poa,
	substring(dxe3,1,5) as e3,
	convert(varchar(5),substring(dxe3, 8, 1)) as e3poa from @tblprimary) P
UNPIVOT	(
	dxValue FOR dxField IN 
		(e1, e1poa, e2, e2poa, e3, e3poa)
	) PVT
)
select record_id,
	dx1,dx1pa,dx2,dx2pa,dx3,dx3pa,dx4,dx4pa,dx5,dx5pa,
	dx6,dx6pa,dx7,dx7pa,dx8,dx8pa,dx9,dx9pa,dx10,dx10pa,
	dx11,dx11pa,dx12,dx12pa,dx13,dx13pa,dx14,dx14pa,dx15,dx15pa,
	dx16,dx16pa,dx17,dx17pa,dx18,dx18pa,dx19,dx19pa,dx20,dx20pa
FROM
(
	select record_id, exfield=case when dxfield like 'dx%'
		then dxfield
		else 'dx'+cast(cast(substring(dxfield,2,1) as int)+(
			select max(substring(c2.dxfield,3,1))
			from cte c2
			where c2.record_id = c1.record_id and dxfield like 'dx_') as varchar)+
			case when len(dxfield)=2 then '' else 'pa' end end, dxvalue
	from cte c1
) P
PIVOT
(
MAX(dxvalue)
for exfield in
([dx1],[dx1pa],[dx2],[dx2pa],[dx3],[dx3pa],[dx4],[dx4pa],[dx5],[dx5pa],
[dx6],[dx6pa],[dx7],[dx7pa],[dx8],[dx8pa],[dx9],[dx9pa],[dx10],[dx10pa],
[dx11],[dx11pa],[dx12],[dx12pa],[dx13],[dx13pa],[dx14],[dx14pa],[dx15],[dx15pa],
[dx16],[dx16pa],[dx17],[dx17pa],[dx18],[dx18pa],[dx19],[dx19pa],[dx20],[dx20pa])
) pvt

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mtuepkerAuthor Commented:
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.

0
cyberkiwiCommented:
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
0
mtuepkerAuthor Commented:
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.

0
mtuepkerAuthor Commented:
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

0
cyberkiwiCommented:
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.
0
mtuepkerAuthor Commented:
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.
0
cyberkiwiCommented:
For this to work and efficiently, it would still be better to modify a copy of the view directly rather than trying to for its usage.  Also, cursor on a few million records is not going to be faster than the SQL data methods pivot and unpivot.  It's like using a general CPU to perform what a GPU handles using 32 pipelines.
0
mtuepkerAuthor Commented:
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

0
cyberkiwiCommented:
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.
0
mtuepkerAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.