• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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

0
KeirMcCann
Asked:
KeirMcCann
  • 4
  • 3
1 Solution
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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