SQLTeam.com | Weblogs | Forums

Alias Query


#1

I am very much a newbie to SQL and am trying to write a query for a report where I need an alias as a parameter.
Code ID Parent QualID
PAP052-PFI2I 29319 PAP052-PFI2I NULL
FIGQD2-QGICI 29319 NULL 50078252
FRPROG-QGICI 29319 NULL ZPROG001
FSNEN2-QGICI 29319 NULL 50094695
GLOQC2-QGICI 29319 NULL 50077685
The 'Parent' column is my alias with the following code [,CASE WHEN O.QualID IS NULL THEN O.Code END AS Parent]
I need to populate all the parent with the code PAP052-PFI2I for that ID, if that makes sense. Any ideas would be greatly appreciated, thank you.


#2

"if that makes sense" - Sorry but no, not to me. Let's see if we can bring the issue into focus. Is the rule for the Parent value "If ANY QualID is Null within the same ID group, then use the Code of the record that has the Null value"? What to use if none of the QualIDs are Null? What to do if more than one QualID is Null within the group?
Help us to understand the problem and it's likely that someone can supply a solution.


#3

So sorry, I'm never good at explaining these things.....Yes you have it! The Parent Value rule is "If ANY QualID is Null within the same ID group, then use the Code of the record that has the Null value in the QualID.

This will be a major breakthough for me if I can get this sorted, thank you for your patience.


#4

create table #test ( code varchar(50),ID int, QualID varchar(50) Null)

insert into #test (Code,ID,QualID )
values
('PAP052-PFI2I',29319,Null),
('FIGQD2-QGICI',29319,'50078252'),
('FRPROG-QGICI',29319,'ZPROG001'),
('FSNEN2-QGICI',29319,'50094695'),
('GLOQC2-QGICI',29319,'50077685'),

('GK0QR1-QGICI',29000,NULL),
('GK0QT1-QGICI',29000,'50077685'),
('GK0QR2-QGICI',29000,NULL)

Select
--Your line of code
CASE WHEN QualID IS NULL THEN Code END AS Parent
,*
from #test


#5

Using the above code for ID 29000 which parent code will you use?


#6

Thank you for looking at this for me, it's the "PAP" code that is the main one.


#7

no but what if you get more then 1 null as shown in the example above(for ID 29000 - it has 2 nulls with different codes) what is the logic then?


#8

Not sure your logic but this is one way of doing it, assuming you only get 1 Null per ID.

create table #test ( code varchar(50),ID int, QualID varchar(50) Null)

insert into #test (Code,ID,QualID )
values
('PAP052-PFI2I',29319,Null),
('FIGQD2-QGICI',29319,'50078252'),
('FRPROG-QGICI',29319,'ZPROG001'),
('FSNEN2-QGICI',29319,'50094695'),
('GLOQC2-QGICI',29319,'50077685'),

('GK0QR1-QGICI',29000,NULL),
('GK0QT1-QGICI',29000,'50077685'),
('GK0QR2-QGICI',29000,'500776T5')

Select
a.*,
b.code as Parent
from #test a
left join (
Select ID,code from #test
where QualID IS NULL
) b on a.ID = b.ID


#9


#10

Thank you so much for your help, there shouldn't be two null's unless there are two separate courses. This is such a key advancement as we are trying to fit a square peg in a round hole here. The new system works differently to the old which is making it incredibly difficult for reporting.