July 17, 2012 at 11:28 am
I am attempting to join the two queries together. The two queries function properly separately. I've gotten the CASE statement from query 2 to work with query 1.
Some background, this query is being run against a business intelligence program that creates graphs/charts off autorun inserts. Right now when no new data is inserted into the table for a specific IM.NAME the program looks back to the last record, which skews the chart results. I need a '1' to print out when one of the 5 IM.NAME fields does not return based on the master query below. I've included a SQLfiddle showing the 2nd query in action: SQL Fiddle
SELECT DISTINCT
SA.DELETED,
PAT.VERSION,
PAT.PATTERNDATE,
SA.AGENT_VERSION,
SCL.COMPUTER_NAME AS Computer_Name,
SCO.OPERATION_SYSTEM AS Operation_System,
dateadd(s,convert(bigint,SA.CREATION_TIME)/1000,
'01-01-1970 00:00:00') CREATION_DTTM,
dateadd(s,convert(bigint,SA.LAST_UPDATE_TIME)/1000,
'01-01-1970 00:00:00') Lastupdatetime,
DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000,
'01-01-1970 00:00:00')LAST_SCAN_TIME,
PAT.PATTERNDATE AS Pattern_Date,
SCL.USER_NAME AS User_Name,
VSC.IP_ADDR1_TEXT AS IP_Add,
CASE WHEN IM.NAME LIKE '%-FRN%' THEN IM.NAME
WHEN IM.NAME LIKE '%-BCK%' THEN IM.NAME
WHEN IM.NAME LIKE '%-PPP%' THEN IM.NAME
WHEN IM.NAME LIKE '%-MES%' THEN IM.NAME
WHEN IM.NAME LIKE '%-MMT%' THEN IM.NAME
ELSE '1' END AS Group_Name,
IM.NAME as test
FROM ((((SEM_AGENT SA
INNER JOIN SEM_CLIENT SCL
ON ((SA.COMPUTER_ID=SCL.COMPUTER_ID)
AND (SA.DOMAIN_ID=SCL.DOMAIN_ID))
AND (SA.GROUP_ID=SCL.GROUP_ID))
INNER JOIN SEM_COMPUTER SCO
ON ((SA.COMPUTER_ID=SCO.COMPUTER_ID)
AND (SA.DOMAIN_ID=SCO.DOMAIN_ID))
AND (SA.DELETED=SCO.DELETED))
INNER JOIN PATTERN PAT
ON SA.PATTERN_IDX=PAT.PATTERN_IDX)
INNER JOIN IDENTITY_MAP IM
ON SCL.GROUP_ID=IM.ID)
INNER JOIN V_SEM_COMPUTER VSC
ON SCO.COMPUTER_ID=VSC.COMPUTER_ID
AND SA.DELETED=0
WHERE PAT.Patterndate < (SELECT MAX(Patterndate) -1 FROM Pattern)
AND SCO.OPERATION_SYSTEM NOT LIKE '%2000%'
ORDER BY Computer_Name
2nd query
Select (CASE WHEN IM.NAME LIKE '%-FRN%' THEN IM.NAME
WHEN IM.NAME LIKE '%-BCK%' THEN IM.NAME
WHEN IM.NAME LIKE '%-PPP%' THEN IM.NAME
WHEN IM.NAME LIKE '%-MES%' THEN IM.NAME
WHEN IM.NAME LIKE '%-MMT%' THEN IM.NAME
ELSE '1' END) AS Group_Name,
tagTypes.tagType,
IM.NAME
From
(
Select '%-FRN%' AS tagType
UNION
Select '%-BCK%' AS tagType
UNION
Select '%-PPP%' AS tagType
UNION
Select '%-MES%' AS tagType
UNION
Select '%-MMT%' AS tagType
) AS tagTypes
left join
IDENTITY_MAP IM
on IM.NAME like tagTypes.tagType
And here is what I've gotten so far but I get the error "The multi-part identifier "tagTypes.tagType" could not be bound. The multi-part identifier "tagTypes.tagType" could not be bound"
SELECT DISTINCT
SA.DELETED,
PAT.VERSION,
PAT.PATTERNDATE,
SA.AGENT_VERSION,
SCL.COMPUTER_NAME AS Computer_Name,
SCO.OPERATION_SYSTEM AS Operation_System,
dateadd(s,convert(bigint,SA.CREATION_TIME)/1000,
'01-01-1970 00:00:00') CREATION_DTTM,
dateadd(s,convert(bigint,SA.LAST_UPDATE_TIME)/1000,
'01-01-1970 00:00:00') Lastupdatetime,
DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000,
'01-01-1970 00:00:00')LAST_SCAN_TIME,
PAT.PATTERNDATE AS Pattern_Date,
SCL.USER_NAME AS User_Name,
VSC.IP_ADDR1_TEXT AS IP_Add,
CASE WHEN IM.NAME LIKE '%-FRN%' THEN IM.NAME
WHEN IM.NAME LIKE '%-BCK%' THEN IM.NAME
WHEN IM.NAME LIKE '%-PPP%' THEN IM.NAME
WHEN IM.NAME LIKE '%-MES%' THEN IM.NAME
WHEN IM.NAME LIKE '%-MMT%' THEN IM.NAME
ELSE '1' END AS Group_Name,
IM.NAME AS test,
tagTypes.tagType,
(Select '%-FRT%' AS tagType
UNION
Select '%-BCK%' AS tagType
UNION
Select '%-PPP%' AS tagType
UNION
Select '%-MES%' AS tagType
UNION
Select '%-MMT%' AS tagType)
AS tagTypes
FROM ((((SEM_AGENT SA
INNER JOIN SEM_CLIENT SCL
ON ((SA.COMPUTER_ID=SCL.COMPUTER_ID)
AND (SA.DOMAIN_ID=SCL.DOMAIN_ID))
AND (SA.GROUP_ID=SCL.GROUP_ID))
INNER JOIN SEM_COMPUTER SCO
ON ((SA.COMPUTER_ID=SCO.COMPUTER_ID)
AND (SA.DOMAIN_ID=SCO.DOMAIN_ID))
AND (SA.DELETED=SCO.DELETED))
INNER JOIN PATTERN PAT
ON SA.PATTERN_IDX=PAT.PATTERN_IDX)
INNER JOIN IDENTITY_MAP IM
ON SCL.GROUP_ID=IM.ID)
INNER JOIN V_SEM_COMPUTER VSC
ON SCO.COMPUTER_ID=VSC.COMPUTER_ID
LEFT JOIN IDENTITY_MAP
ON IM.NAME = tagTypes.tagType
AND SA.DELETED=0
WHERE PAT.Patterndate < (SELECT MAX(Patterndate) -1 FROM Pattern)
AND SCO.OPERATION_SYSTEM NOT LIKE '%2000%'
ORDER BY Computer_Name
July 17, 2012 at 11:33 am
That error is saying that there is no column named tagType in the tagTypes table. In the second query, you have:
From
(
Select '%-FRN%' AS tagType
UNION
Select '%-BCK%' AS tagType
UNION
Select '%-PPP%' AS tagType
UNION
Select '%-MES%' AS tagType
UNION
Select '%-MMT%' AS tagType
) AS tagTypes
So you have a virtual table called tagTypes with a column called tagType.
In the query giving you an error, that is not the case. The subquery is in your SELECT clause, not the FROM.
Jared
CE - Microsoft
July 17, 2012 at 11:40 am
Try this:
SELECT DISTINCT
SA.DELETED,
PAT.VERSION,
PAT.PATTERNDATE,
SA.AGENT_VERSION,
SCL.COMPUTER_NAME AS Computer_Name,
SCO.OPERATION_SYSTEM AS Operation_System,
dateadd(s,convert(bigint,SA.CREATION_TIME)/1000,
'01-01-1970 00:00:00') CREATION_DTTM,
dateadd(s,convert(bigint,SA.LAST_UPDATE_TIME)/1000,
'01-01-1970 00:00:00') Lastupdatetime,
DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000,
'01-01-1970 00:00:00')LAST_SCAN_TIME,
PAT.PATTERNDATE AS Pattern_Date,
SCL.USER_NAME AS User_Name,
VSC.IP_ADDR1_TEXT AS IP_Add,
CASE WHEN IM.NAME LIKE '%-FRN%' THEN IM.NAME
WHEN IM.NAME LIKE '%-BCK%' THEN IM.NAME
WHEN IM.NAME LIKE '%-PPP%' THEN IM.NAME
WHEN IM.NAME LIKE '%-MES%' THEN IM.NAME
WHEN IM.NAME LIKE '%-MMT%' THEN IM.NAME
ELSE '1' END AS Group_Name,
IM.NAME AS test,
tagTypes.tagType
FROM SEM_AGENT SA
INNER JOIN SEM_CLIENT SCL
ON ((SA.COMPUTER_ID=SCL.COMPUTER_ID)
AND (SA.DOMAIN_ID=SCL.DOMAIN_ID))
AND (SA.GROUP_ID=SCL.GROUP_ID))
INNER JOIN SEM_COMPUTER SCO
ON ((SA.COMPUTER_ID=SCO.COMPUTER_ID)
AND (SA.DOMAIN_ID=SCO.DOMAIN_ID))
AND (SA.DELETED=SCO.DELETED))
INNER JOIN PATTERN PAT
ON SA.PATTERN_IDX=PAT.PATTERN_IDX)
INNER JOIN IDENTITY_MAP IM
ON SCL.GROUP_ID=IM.ID)
INNER JOIN V_SEM_COMPUTER VSC
ON SCO.COMPUTER_ID=VSC.COMPUTER_ID
LEFT JOIN IDENTITY_MAP
ON IM.NAME = tagTypes.tagType
AND SA.DELETED=0
LEFT JOIN (Select '%-FRT%' AS tagType
UNION
Select '%-BCK%' AS tagType
UNION
Select '%-PPP%' AS tagType
UNION
Select '%-MES%' AS tagType
UNION
Select '%-MMT%' AS tagType) tagTypes
ON IM.name = tagTypes.tagType
WHERE PAT.Patterndate < (SELECT MAX(Patterndate) -1 FROM Pattern)
AND SCO.OPERATION_SYSTEM NOT LIKE '%2000%'
ORDER BY Computer_Name
Jared
CE - Microsoft
July 17, 2012 at 11:43 am
Ugh... Hold on, this is messed up... Give me a minute to fix.
Jared
CE - Microsoft
July 17, 2012 at 11:45 am
Thanks for the response.
I tried the new query and received this error "Incorrect syntax near ')'. Incorrect syntax near 'tagTypes'. Incorrect syntax near the keyword 'AND'."
I know these are small errors but I've been staring at this all morning and I appreciate the help.
July 17, 2012 at 11:45 am
Ok, now I am lost... What is the point of your 2nd query? It does nothing for you.
Jared
CE - Microsoft
July 17, 2012 at 11:45 am
Ok, no problem. Thanks
July 17, 2012 at 11:49 am
The second query prints a row with '1's if no IM.NAME values exists that are like a value in the virtual table tagTypes
July 17, 2012 at 11:59 am
Ok, so just insert another CASE after our groupName:
SELECT DISTINCT SA.DELETED
,PAT.VERSION
,PAT.PATTERNDATE
,SA.AGENT_VERSION
,SCL.COMPUTER_NAME AS Computer_Name
,SCO.OPERATION_SYSTEM AS Operation_System
,dateadd(s, convert(BIGINT, SA.CREATION_TIME) / 1000, '01-01-1970 00:00:00') CREATION_DTTM
,dateadd(s, convert(BIGINT, SA.LAST_UPDATE_TIME) / 1000, '01-01-1970 00:00:00') Lastupdatetime
,DATEADD(s, convert(BIGINT, LAST_SCAN_TIME) / 1000, '01-01-1970 00:00:00') LAST_SCAN_TIME
,PAT.PATTERNDATE AS Pattern_Date
,SCL.USER_NAME AS User_Name
,VSC.IP_ADDR1_TEXT AS IP_Add
,CASE
WHEN IM.NAME LIKE '%-FRN%'
THEN IM.NAME
WHEN IM.NAME LIKE '%-BCK%'
THEN IM.NAME
WHEN IM.NAME LIKE '%-PPP%'
THEN IM.NAME
WHEN IM.NAME LIKE '%-MES%'
THEN IM.NAME
WHEN IM.NAME LIKE '%-MMT%'
THEN IM.NAME
ELSE '1'
END AS Group_Name
,IM.NAME AS test
,CASE
WHEN IM.NAME LIKE '%-FRN%'
THEN '%-FRN%'
WHEN IM.NAME LIKE '%-BCK%'
THEN '%-BCK%'
WHEN IM.NAME LIKE '%-PPP%'
THEN '%-PPP%'
WHEN IM.NAME LIKE '%-MES%'
THEN '%-MES%'
WHEN IM.NAME LIKE '%-MMT%'
THEN '%-MMT%'
ELSE '1'
END AS tagType
FROM SEM_AGENT SA
INNER JOIN SEM_CLIENT SCL
ON SA.COMPUTER_ID = SCL.COMPUTER_ID
AND SA.DOMAIN_ID = SCL.DOMAIN_ID
AND SA.GROUP_ID = SCL.GROUP_ID
INNER JOIN SEM_COMPUTER SCO
ON SA.COMPUTER_ID = SCO.COMPUTER_ID
AND SA.DOMAIN_ID = SCO.DOMAIN_ID
AND SA.DELETED = SCO.DELETED
INNER JOIN PATTERN PAT
ON SA.PATTERN_IDX = PAT.PATTERN_IDX
INNER JOIN IDENTITY_MAP IM
ON SCL.GROUP_ID = IM.ID
INNER JOIN V_SEM_COMPUTER VSC
ON SCO.COMPUTER_ID = VSC.COMPUTER_ID
AND SA.DELETED = 0
WHERE PAT.Patterndate < (
SELECT MAX(Patterndate) - 1
FROM Pattern
)
AND SCO.OPERATION_SYSTEM NOT LIKE '%2000%'
ORDER BY Computer_Name
Also, all of your parentheses were making this extremely hard to read. Try not to use parentheses unless they are required to make it easier to read.
Jared
CE - Microsoft
July 17, 2012 at 12:11 pm
Thanks, this query runs but its not giving me the result I was looking for.
For example, this query returned one row with MMT. However, I would like to receive the row with MMT as well as blank rows for the other 4 tagTypes with just a '1' in the row.
July 17, 2012 at 12:24 pm
neb2886 (7/17/2012)
Thanks, this query runs but its not giving me the result I was looking for.For example, this query returned one row with MMT. However, I would like to receive the row with MMT as well as blank rows for the other 4 tagTypes with just a '1' in the row.
I really don't know what that means being that you have not given any sample data. "The row with MMT" means nothing to me. For all I know, your table only has 1 row in it 🙂 Take a look at the link in my signature to an article by Jeff Moden on how to post. While you are getting that together, I will attempt to see if I can figure out what you are doing by attempting to simulate your tables and some data.
Jared
CE - Microsoft
July 17, 2012 at 12:26 pm
Try changing your INNER JOIN IDENTITY_MAP IM to a LEFT JOIN IDENTITY_MAP IM
Jared
CE - Microsoft
July 17, 2012 at 12:40 pm
This is an example of how the virtual table was working:
http://sqlfiddle.com/#!3/f2bb4/1
The whole larger query is checking an anti-virus database for hosts within 5 different groups that have out of date pattern dates.
Right now, today, I have one host within the 'MMT' group that has a definition out of date. Which means that all other hosts in the 'MMT' group as well as all of the hosts in the other 4 groups (FRT, BCK, PPP and MES) are up to date and therefore do not display.
I am using a tool that charts this data and it uses this query to insert data on the 1st and 15th of every month into a secondary database (which it then draws from to create its charts). Right now if the insert statement runs and there are no new updates for a particular group (i.e. on the 1st the FRT group had 2 hosts out of date but on the 15th has 0) the query will not show a blank row for the FRT group which would then be inserted into the secondary database and timestamped for the purposes of the chart.
The result right now is that I am only getting old data for some of the groups and the charts are displaying some as being out of date when they actually are not.
Does this help clarify? I can be more specific if need be.
July 17, 2012 at 12:44 pm
neb2886 (7/17/2012)
This is an example of how the virtual table was working:http://sqlfiddle.com/#!3/f2bb4/1
The whole larger query is checking an anti-virus database for hosts within 5 different groups that have out of date pattern dates.
Right now, today, I have one host within the 'MMT' group that has a definition out of date. Which means that all other hosts in the 'MMT' group as well as all of the hosts in the other 4 groups (FRT, BCK, PPP and MES) are up to date and therefore do not display.
I am using a tool that charts this data and it uses this query to insert data on the 1st and 15th of every month into a secondary database (which it then draws from to create its charts). Right now if the insert statement runs and there are no new updates for a particular group (i.e. on the 1st the FRT group had 2 hosts out of date but on the 15th has 0) the query will not show a blank row for the FRT group which would then be inserted into the secondary database and timestamped for the purposes of the chart.
The result right now is that I am only getting old data for some of the groups and the charts are displaying some as being out of date when they actually are not.
Does this help clarify? I can be more specific if need be.
I'm sorry... This means nothing to me. I need table definitions and sample data with expected results. Your virtual table is worthless because the data is already being returned, as far as I can see.
Jared
CE - Microsoft
July 17, 2012 at 12:53 pm
OH! You want all of those types no matter what. That is why you are doing that first... Ok. Do this:
;WITH tagTypes (tagType VARCHAR(10))
AS
(Select '%-FRN%' AS tagType
UNION
Select '%-BCK%' AS tagType
UNION
Select '%-PPP%' AS tagType
UNION
Select '%-MES%' AS tagType
UNION
Select '%-MMT%' AS tagType)
SELECT DISTINCT SA.DELETED
,PAT.VERSION
,PAT.PATTERNDATE
,SA.AGENT_VERSION
,SCL.COMPUTER_NAME AS Computer_Name
,SCO.OPERATION_SYSTEM AS Operation_System
,dateadd(s, convert(BIGINT, SA.CREATION_TIME) / 1000, '01-01-1970 00:00:00') CREATION_DTTM
,dateadd(s, convert(BIGINT, SA.LAST_UPDATE_TIME) / 1000, '01-01-1970 00:00:00') Lastupdatetime
,DATEADD(s, convert(BIGINT, LAST_SCAN_TIME) / 1000, '01-01-1970 00:00:00') LAST_SCAN_TIME
,PAT.PATTERNDATE AS Pattern_Date
,SCL.USER_NAME AS User_Name
,VSC.IP_ADDR1_TEXT AS IP_Add
,CASE
WHEN IM.NAME LIKE '%-FRN%'
THEN IM.NAME
WHEN IM.NAME LIKE '%-BCK%'
THEN IM.NAME
WHEN IM.NAME LIKE '%-PPP%'
THEN IM.NAME
WHEN IM.NAME LIKE '%-MES%'
THEN IM.NAME
WHEN IM.NAME LIKE '%-MMT%'
THEN IM.NAME
ELSE '1'
END AS Group_Name
,IM.NAME AS test
,tagTypes.tagType
FROM tagTypes
LEFT JOIN IDENTITY_MAP IM
ON IM.NAME like tagTypes.tagType
INNER JOIN SEM_CLIENT SCL
ON SCL.GROUP_ID = IM.ID
INNER JOIN SEM_AGENT SA
ON SA.COMPUTER_ID = SCL.COMPUTER_ID
AND SA.DOMAIN_ID = SCL.DOMAIN_ID
AND SA.GROUP_ID = SCL.GROUP_ID
INNER JOIN SEM_COMPUTER SCO
ON SA.COMPUTER_ID = SCO.COMPUTER_ID
AND SA.DOMAIN_ID = SCO.DOMAIN_ID
AND SA.DELETED = SCO.DELETED
INNER JOIN PATTERN PAT
ON SA.PATTERN_IDX = PAT.PATTERN_IDX
INNER JOIN V_SEM_COMPUTER VSC
ON SCO.COMPUTER_ID = VSC.COMPUTER_ID
AND SA.DELETED = 0
WHERE PAT.Patterndate < (
SELECT MAX(Patterndate) - 1
FROM Pattern
)
AND SCO.OPERATION_SYSTEM NOT LIKE '%2000%'
ORDER BY Computer_Name
If this does not solve your problem, I am not going to be able to help any more until I get the requsted tables and sample data. It is just a waste of my time and everyone else reading this if I am just shooting in the dark.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply