Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Question Unanswered: Select FROM (Select - Temporary Tables

    Hi, I need to right a fairly complex report using SQL but am coming unstuck when trying to query two temporty tables that i have created in my SQL statement.

    Heres my code:

    Code:
    SELECT     SUBQU.current_fl_advisor
    , SUM(SUBQU.CPS) AS IncompleteLines
    , SUM(SUBQU1.CPS) AS NewThisWeek
    FROM         
    
    
    (SELECT tblFixedLine_order_summary_1.current_fl_advisor
    , COUNT(tblFixedLine_cps.fllog_ref) AS CPS
     FROM tblFixedLine_order_summary AS tblFixedLine_order_summary_1 
    FULL OUTER JOIN tblFixedLine_cps
     ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_cps.fllog_ref
    WHERE (tblFixedLine_cps.status <> 'Order Complete')
     AND tblFixedLine_cps.status <> 'Cancelled')
     GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
    UNION ALL
    SELECT tblFixedLine_order_summary_1.current_fl_advisor
    , COUNT(tblFixedLine_call_1.fllog_ref) AS CALL
     FROM tblFixedLine_order_summary AS tblFixedLine_order_summary_1
     FULL OUTER JOIN tblFixedLine_call AS tblFixedLine_call_1
     ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_call_1.fllog_ref
     WHERE (tblFixedLine_call_1.status <> 'Order Complete')
     AND (tblFixedLine_call_1.status <> 'Cancelled')
     GROUP BY tblFixedLine_order_summary_1.current_fl_advisor) AS SUBQU 
    
    CROSS JOIN
    
    (SELECT     tblFixedLine_order_summary_1.current_fl_advisor
    , COUNT(tblFixedLine_cps_1.fllog_ref) AS CPS
     FROM tblFixedLine_order_summary AS tblFixedLine_order_summary_1
     FULL OUTER JOIN tblFixedLine_cps AS tblFixedLine_cps_1
     ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_cps_1.fllog_ref
     WHERE (tblFixedLine_cps_1.status <> 'Order Complete')
     AND (tblFixedLine_cps_1.status <> 'Cancelled')
     AND (DATEPART(wk, tblFixedLine_cps_1.created_date) 
    = DATEPART(wk, GETDATE()))
     GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
    UNION ALL
    SELECT tblFixedLine_order_summary_1.current_fl_advisor
    , COUNT tblFixedLine_call_1.fllog_ref) AS CALL
     FROM tblFixedLine_order_summary AS tblFixedLine_order_summary_1
     FULL OUTER JOIN tblFixedLine_call AS tblFixedLine_call_1
     ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_call_1.fllog_ref
     WHERE (tblFixedLine_call_1.status <> 'Order Complete')
     AND (tblFixedLine_call_1.status <> 'Cancelled')
     AND (DATEPART(wk, tblFixedLine_call_1.created_date) 
    = DATEPART(wk, GETDATE()))
     GROUP BY tblFixedLine_order_summary_1.current_fl_advisor) AS SUBQU1
    
    GROUP BY SUBQU.current_fl_advisor
    The issue is the first line is doubling the values that come out of the database so it is correctly counting 'SUM(SUBQU.CPS) AS IncompleteLines' but is then doubling this value as it moves on to SUBQU1.

    Any ideas what i have done wrong? I am using the SQL Bible by Alex Kriegel and Boris Trukhnov as reference but they dont seem to go this advanced in the book.
    Last edited by Pat Phelan; 07-06-09 at 08:51. Reason: Fixed the BBCODE markers and code as originally intended

  2. #2
    Join Date
    Jul 2009
    Posts
    32
    Appologies the [b] within the code are not part of my sql statement i was trying to get these sections of my query to stand out from the rest.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Your query doesn't compile. Can you correct it and present it in a way that is readable by humans? Something in the line of this:
    Code:
    SELECT     SUBQU.current_fl_advisor, 
    	SUM(SUBQU.CPS) AS IncompleteLines, 
    	SUM(SUBQU1.CPS) AS NewThisWeek
    FROM
    	(SELECT tblFixedLine_order_summary_1.current_fl_advisor, 
    			COUNT(tblFixedLine_cps.fllog_ref) AS CPS 
    	FROM tblFixedLine_order_summary AS tblFixedLine_order_summary_1 
    		FULL OUTER JOIN tblFixedLine_cps ON 
    			tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_cps.fllog_ref
    	WHERE (tblFixedLine_cps.status <> 'Order Complete') AND 
    			tblFixedLine_cps.status <> 'Cancelled'
    	)
    GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
    
    	UNION ALL
    
    SELECT tblFixedLine_order_summary_1.current_fl_advisor, 
    		COUNT(tblFixedLine_call_1.fllog_ref) AS CALL 
    FROM tblFixedLine_order_summary AS tblFixedLine_order_summary_1 
    	FULL OUTER JOIN tblFixedLine_call AS tblFixedLine_call_1 ON 
    		tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_call_1.fllog_ref 
    WHERE (tblFixedLine_call_1.status <> 'Order Complete') AND 
    		(tblFixedLine_call_1.status <> 'Cancelled')
    GROUP BY tblFixedLine_order_summary_1.current_fl_advisor) AS SUBQU 
    		CROSS JOIN
    	(SELECT tblFixedLine_order_summary_1.current_fl_advisor, 
    			COUNT(tblFixedLine_cps_1.fllog_ref) AS CPS 
    	FROM tblFixedLine_order_summary AS tblFixedLine_order_summary_1 
    		FULL OUTER JOIN tblFixedLine_cps AS tblFixedLine_cps_1 ON 
    			tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_cps_1.fllog_ref 
    	WHERE (tblFixedLine_cps_1.status <> 'Order Complete') AND 
    			(tblFixedLine_cps_1.status <> 'Cancelled') AND 
    			(DATEPART(wk, tblFixedLine_cps_1.created_date) = DATEPART(wk, GETDATE())) 
    	GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
    		UNION ALL
    	SELECT tblFixedLine_order_summary_1.current_fl_advisor, 
    			COUNT tblFixedLine_call_1.fllog_ref) AS CALL 
    	FROM tblFixedLine_order_summary AS tblFixedLine_order_summary_1 
    		FULL OUTER JOIN tblFixedLine_call AS tblFixedLine_call_1 ON 
    			tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_call_1.fllog_ref 
    	WHERE (tblFixedLine_call_1.status <> 'Order Complete') AND 
    			(tblFixedLine_call_1.status <> 'Cancelled') AND 
    			(DATEPART(wk, tblFixedLine_call_1.created_date) = DATEPART(wk, GETDATE())) 
    	GROUP BY tblFixedLine_order_summary_1.current_fl_advisor) AS SUBQU1
    GROUP BY SUBQU.current_fl_advisor
    Chances are high you will find your error now the query is in a readable lay-out.

    SQL Server gives these messages :
    Code:
    Msg 156, Level 15, State 1, Line 13
    Incorrect syntax near the keyword 'GROUP'.
    Msg 102, Level 15, State 1, Line 24
    Incorrect syntax near ')'.
    Msg 102, Level 15, State 1, Line 37
    Incorrect syntax near '.'.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jul 2009
    Posts
    32
    I cut it down so as not to fill the page as its an extremly lengthy query, its does compile (im using VS2005) but the two expressions are multiplied and incorrect (Expr2 & Expr3).

    Here is the current revision of my sql query:

    (on next post as its too long)

  5. #5
    Join Date
    Jul 2009
    Posts
    32
    Code:
    SELECT     USERS.nt_username, SUM(SUBQU.CPS) AS Expr2, SUM(SUBQU1.CPS) AS Expr3
    FROM         tblFixedLine_provisioners AS USERS RIGHT OUTER JOIN
                              (SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_cps.fllog_ref) AS CPS
                                FROM          tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                       tblFixedLine_cps ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_cps.fllog_ref
                                WHERE      (tblFixedLine_cps.status <> 'Order Complete') AND (tblFixedLine_cps.status <> 'Cancelled')
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
                                UNION ALL
                                SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_call_1.fllog_ref) AS CALL
                                FROM         tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                      tblFixedLine_call AS tblFixedLine_call_1 ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_call_1.fllog_ref
                                WHERE     (tblFixedLine_call_1.status <> 'Order Complete') AND (tblFixedLine_call_1.status <> 'Cancelled')
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
                                UNION ALL
                                SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_call_isdn.fllog_ref) AS ISDN
                                FROM         tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                      tblFixedLine_call_isdn AS tblFixedLine_call_isdn ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_call_isdn.fllog_ref
                                WHERE     (tblFixedLine_call_isdn.status <> 'Order Complete') AND (tblFixedLine_call_isdn.status <> 'Cancelled')
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
                                UNION ALL
                                SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_orderdetails_broadband.fllog_ref) AS BB
                                FROM         tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                      tblFixedLine_orderdetails_broadband AS tblFixedLine_orderdetails_broadband ON 
                                                      tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_orderdetails_broadband.fllog_ref
                                WHERE     (tblFixedLine_orderdetails_broadband.status <> 'Order Complete') AND (tblFixedLine_orderdetails_broadband.status <> 'Cancelled')
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
                                UNION ALL
                                SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_other.fllog_ref) AS OTHER
                                FROM         tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                      tblFixedLine_other AS tblFixedLine_other ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_other.fllog_ref
                                WHERE     (tblFixedLine_other.status <> 'Order Complete') AND (tblFixedLine_other.status <> 'Cancelled')
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
                                UNION ALL
                                SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_private.fllog_ref) AS PRIVATE
                                FROM         tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                      tblFixedLine_private AS tblFixedLine_private ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_private.fllog_ref
                                WHERE     (tblFixedLine_private.status <> 'Order Complete') AND (tblFixedLine_private.status <> 'Cancelled')
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor) AS SUBQU ON USERS.nt_username = SUBQU.current_fl_advisor

  6. #6
    Join Date
    Jul 2009
    Posts
    32
    Code:
    RIGHT OUTER JOIN
                              (SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_cps_1.fllog_ref) AS CPS
                                FROM          tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                       tblFixedLine_cps AS tblFixedLine_cps_1 ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_cps_1.fllog_ref
                                WHERE      (tblFixedLine_cps_1.status <> 'Order Complete') AND (tblFixedLine_cps_1.status <> 'Cancelled') AND (DATEPART(hh, 
                                                       tblFixedLine_cps_1.created_date) = DATEPART(hh, tblFixedLine_cps_1.created_date - 24)) AND (CAST(CONVERT(varchar(8), 
                                                       tblFixedLine_cps_1.created_date) AS datetime) = CAST(CONVERT(varchar(8), GETDATE()) AS datetime))
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
                                UNION ALL
                                SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_call_1.fllog_ref) AS CALL
                                FROM         tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                      tblFixedLine_call AS tblFixedLine_call_1 ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_call_1.fllog_ref
                                WHERE     (tblFixedLine_call_1.status <> 'Order Complete') AND (tblFixedLine_call_1.status <> 'Cancelled') AND (DATEPART(hh, 
                                                      tblFixedLine_call_1.created_date) = DATEPART(hh, tblFixedLine_call_1.created_date - 24)) AND (CAST(CONVERT(varchar(8), 
                                                      tblFixedLine_call_1.created_date) AS datetime) = CAST(CONVERT(varchar(8), GETDATE()) AS datetime))
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
                                UNION ALL
                                SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_call_isdn.fllog_ref) AS ISDN
                                FROM         tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                      tblFixedLine_call_isdn AS tblFixedLine_call_isdn ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_call_isdn.fllog_ref
                                WHERE     (tblFixedLine_call_isdn.status <> 'Order Complete') AND (tblFixedLine_call_isdn.status <> 'Cancelled') AND (DATEPART(hh, 
                                                      tblFixedLine_call_isdn.created_date) = DATEPART(hh, tblFixedLine_call_isdn.created_date - 24)) AND (CAST(CONVERT(varchar(8), 
                                                      tblFixedLine_call_isdn.created_date) AS datetime) = CAST(CONVERT(varchar(8), GETDATE()) AS datetime))
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
                                UNION ALL
                                SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_orderdetails_broadband.fllog_ref) AS BB
                                FROM         tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                      tblFixedLine_orderdetails_broadband AS tblFixedLine_orderdetails_broadband ON 
                                                      tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_orderdetails_broadband.fllog_ref
                                WHERE     (tblFixedLine_orderdetails_broadband.status <> 'Order Complete') AND (tblFixedLine_orderdetails_broadband.status <> 'Cancelled') AND 
                                                      (DATEPART(hh, tblFixedLine_orderdetails_broadband.created_date) = DATEPART(hh, 
                                                      tblFixedLine_orderdetails_broadband.created_date - 24)) AND (CAST(CONVERT(varchar(8), 
                                                      tblFixedLine_orderdetails_broadband.created_date) AS datetime) = CAST(CONVERT(varchar(8), GETDATE()) AS datetime))
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
                                UNION ALL
                                SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_other.fllog_ref) AS OTHER
                                FROM         tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                      tblFixedLine_other AS tblFixedLine_other ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_other.fllog_ref
                                WHERE     (tblFixedLine_other.status <> 'Order Complete') AND (tblFixedLine_other.status <> 'Cancelled') AND (DATEPART(hh, 
                                                      tblFixedLine_other.created_date) = DATEPART(hh, tblFixedLine_other.created_date - 24)) AND (CAST(CONVERT(varchar(8), 
                                                      tblFixedLine_other.created_date) AS datetime) = CAST(CONVERT(varchar(8), GETDATE()) AS datetime))
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
                                UNION ALL
                                SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_private.fllog_ref) AS PRIVATE
                                FROM         tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                      tblFixedLine_private AS tblFixedLine_private ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_private.fllog_ref
                                WHERE     (tblFixedLine_private.status <> 'Order Complete') AND (tblFixedLine_private.status <> 'Cancelled') AND (DATEPART(hh, 
                                                      tblFixedLine_private.created_date) = DATEPART(hh, tblFixedLine_private.created_date - 24)) AND (CAST(CONVERT(varchar(8), 
                                                      tblFixedLine_private.created_date) AS datetime) = CAST(CONVERT(varchar(8), GETDATE()) AS datetime))
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor) AS SUBQU1 ON USERS.nt_username = SUBQU1.current_fl_advisor
    GROUP BY USERS.nt_username, SUBQU1.current_fl_advisor

  7. #7
    Join Date
    Jul 2009
    Posts
    32
    Right i have simplified my query to try and get some assistance...

    Code:
    SELECT     USERS.nt_username, SUM(SUBQU.CALL) AS IncompleteLines, SUM(SUBQU.CALL) AS IncompleteLines
    FROM         tblFixedLine_provisioners AS USERS LEFT OUTER JOIN
                              (SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_call_1.fllog_ref) AS CALL
                                FROM          tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                       tblFixedLine_call AS tblFixedLine_call_1 ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_call_1.fllog_ref
                                WHERE      (tblFixedLine_call_1.status <> 'Order Complete') AND (tblFixedLine_call_1.status <> 'Cancelled')
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
                                UNION ALL
                                SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_orderdetails_broadband.fllog_ref) AS BB
                                FROM         tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                      tblFixedLine_orderdetails_broadband AS tblFixedLine_orderdetails_broadband ON 
                                                      tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_orderdetails_broadband.fllog_ref
                                WHERE     (tblFixedLine_orderdetails_broadband.status <> 'Order Complete') AND (tblFixedLine_orderdetails_broadband.status <> 'Cancelled')
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor) AS SUBQU ON USERS.nt_username = SUBQU.current_fl_advisor LEFT OUTER JOIN
                              (SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_call_1.fllog_ref) AS CALL
                                FROM          tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                       tblFixedLine_call AS tblFixedLine_call_1 ON tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_call_1.fllog_ref
                                WHERE      (tblFixedLine_call_1.status <> 'Order Complete') AND (tblFixedLine_call_1.status <> 'Cancelled') AND (DATEPART(wk, 
                                                       tblFixedLine_call_1.created_date) = DATEPART(wk, GETDATE()))
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor
                                UNION ALL
                                SELECT     tblFixedLine_order_summary_1.current_fl_advisor, COUNT(tblFixedLine_orderdetails_broadband.fllog_ref) AS BB
                                FROM         tblFixedLine_order_summary AS tblFixedLine_order_summary_1 FULL OUTER JOIN
                                                      tblFixedLine_orderdetails_broadband AS tblFixedLine_orderdetails_broadband ON 
                                                      tblFixedLine_order_summary_1.fllog_ref = tblFixedLine_orderdetails_broadband.fllog_ref
                                WHERE     (tblFixedLine_orderdetails_broadband.status <> 'Order Complete') AND (tblFixedLine_orderdetails_broadband.status <> 'Cancelled') AND 
                                                      (DATEPART(wk, tblFixedLine_orderdetails_broadband.created_date) = DATEPART(wk, GETDATE()))
                                GROUP BY tblFixedLine_order_summary_1.current_fl_advisor) AS SUBQU1 ON USERS.nt_username = SUBQU1.current_fl_advisor
    GROUP BY USERS.nt_username, SUBQU.current_fl_advisor
    SUBQU should look like:

    Username | 3

    SUB QU1 should look like:

    Username | 3

    And if i run those tables individually they are current but when i join the two to create one output (as the code above does) its doubles these up like:

    Username | 6 | 6

    Im beaten i have no idea why? Can you help?

Posting Permissions

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