?
Solved

Running a query within a query

Posted on 2008-11-10
8
Medium Priority
?
255 Views
Last Modified: 2012-05-05
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
Comment
Question by:KeirMcCann
  • 4
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22920760
I think you want either a UNION query?
could you show the query for the data that you want to have "below"?
0
 

Author Comment

by:KeirMcCann
ID: 22920921
SELECT  T2.U_IIS_INADR, T0.[Dscription, T0.U_IIS_EXT as Work_Requiring_attention where T0.U_IIS_EXT is not Null
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22921036
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:KeirMcCann
ID: 22921078

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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22921145
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
 

Author Comment

by:KeirMcCann
ID: 22921237
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22952557
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question