Running a query within a query

I want to be able to run another query using the same fields but to find all places where U_IIS_EXT text is not null and then output the data underneath the current set of data. There would be no data in T0.U_IIS_DDATE or SLPNAME but it would fill data in U_IIS_INADR , T0.Dscription and U_IIS_EXT
SELECT  T2.U_IIS_INADR, T0.[Dscription], T0.U_iis_ddate, T1.[SlpName], T0.U_IIS_EXT as Work_Requiring_attention FROM DLN1 T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] = '[%0]' and T2.[CardName] = '[%1]'

Open in new window

KeirMcCannAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you want either a UNION query?
could you show the query for the data that you want to have "below"?
0
KeirMcCannAuthor Commented:
SELECT  T2.U_IIS_INADR, T0.[Dscription, T0.U_IIS_EXT as Work_Requiring_attention where T0.U_IIS_EXT is not Null
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, a UNION:
SELECT  T2.U_IIS_INADR, T0.[Dscription], T0.U_iis_ddate, T1.[SlpName], T0.U_IIS_EXT as Work_Requiring_attention FROM DLN1 T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] = '[%0]' and T2.[CardName] = '[%1]'
 
UNION ALL
 
SELECT  T2.U_IIS_INADR, T0.[Dscription], null, null, T0.U_IIS_EXT as Work_Requiring_attention where T0.U_IIS_EXT is not Null

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

KeirMcCannAuthor Commented:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "T0.U_IIS_EXT" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "T2.U_IIS_INADR" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "T0.Dscription" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "T0.U_IIS_EXT" could not be bound.

Thanks for your time angel
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
your query had no FROM, I overlooked that ..
SELECT  T2.U_IIS_INADR, T0.[Dscription], T0.U_iis_ddate, T1.[SlpName], T0.U_IIS_EXT as Work_Requiring_attention FROM DLN1 T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] = '[%0]' and T2.[CardName] = '[%1]'
 
UNION ALL
 
SELECT  T2.U_IIS_INADR, T0.[Dscription], null, null, T0.U_IIS_EXT as Work_Requiring_attention 
FROM DLN1 T0  INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] = '[%0]' and T2.[CardName] = '[%1]'
AND T0.U_IIS_EXT is not Null

Open in new window

0
KeirMcCannAuthor Commented:
thanks again, it isnt bringing up an error but nor it isnt pulling anything up for U_IIS_EXT either

Any chance you know why?  I have one field with the text "Test" in it but it doesnt pull this data up
untitled2.JPG
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, I must have missed this.

>it isnt bringing up an error but nor it isnt pulling anything up for U_IIS_EXT either
well, I don't know if the individual queries are doing what you need, I just "put" them together with the UNION syntax...
from my point of view, you have to find out what data you exactly have and what you need to get...
based on that, the JOIN ON resp WHERE conditions have to be written...
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
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 2005

From novice to tech pro — start learning today.