Results 1 to 1 of 1
  1. #1
    Join Date
    Mar 2012
    Posts
    8

    Unanswered: Joining two queries

    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

    Code:
    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








    Code:
    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
    Last edited by neb2886; 07-17-12 at 11:51.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •