none
UNION ALL HELPS

    Question

  • I would like to create a header or dummy record by using SELECT A, B, C, etc and then I use UNION ALL to select my detail record.

    If I don't UNION ALL to my header record, my detail record works the way I wanted but when I used UNION ALL with my header record, SQL is complaining about my ORDER BY in my detail record selection.

    How can I get around this error?  Is there another way to create a header/dummy record without using UNION ALL?

    Thank you very much for all your helps.

    Msg 104, Level 16, State 1, Line 183

    ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

    My code is as follow:

    DECLARE  @EventIDs INT
    SET @EventIDs = 24065
    
    DECLARE @ReportSortOrder VARCHAR(50)
    SET @ReportSortOrder='Defendant Name'--'DEFENDANT NAME'
    SET @ReportSortOrder='Violation Date'--'BY VIOLATION DATE'    
    SET @ReportSortOrder='Officer'--'BY OFFICER'  
    SET @ReportSortOrder='Case Number' --BY CASE NUMBER
    
        
    DECLARE @SealPin INT
    SET @SealPin = (SELECT TOP 1 ls.LookupKey FROM tblLookup ls WITH(NOLOCK) 
                            WHERE ls.LookupGroup='ReportVariables'
                            AND ls.Code='_ClerkSealPin')
    
    DECLARE @CourtAddressPIN INT
    SET @CourtAddressPIN = CONVERT(INT, (SELECT TOP 1 LookupKey FROM tblLookup WITH(NOLOCK) WHERE LookupGroup = 'reportvariables' AND Code = '_courtpin'))
    
    
    SELECT
      
      RecordType = 'Header'
    
      ,CourtPINProperName = dbo.fnGetProperNameByPartyID(@CourtAddressPIN)
      ,FormCaseNumber = ''
      ,CaseID ='',
      CaseNumber = '',
      CourtResult = '',
      CaseStatus = '',
      CaseStatusDescription = '',
      
      JudgeID = '',
      Disposition = '',
      DispositionCode = '',
      DispositionDate = '',
      FirstDefendantID = '',
      
      AmountPaid = 0,
    					
      TotalChargesPerCharge = 0,
    
      ReceiptNumber = 0
      
      
    UNION ALL
    
    
    SELECT
    
      RecordType = 'Detail'
      ,CourtPINProperName = dbo.fnGetProperNameByPartyID(@CourtAddressPIN)
      ,FormCaseNumber = c.CaseNumber
      ,c.CaseID,
      c.CaseNumber,
      ce.CourtResult,
      c.CaseStatus,
      casestatus.Description CaseStatusDescription,
      
      c.JudgeID,
      c.Disposition,
      c.DispositionCode,
      c.DispositionDate,
      c.FirstDefendantID,
      
      AmountPaid = (SELECT SUM(cf1.AmountPaid) FROM tblCase c1 with(nolock)
    					LEFT OUTER JOIN tblCaseFee cf1 
    					ON c1.CaseID = cf1.CaseID WHERE c1.CaseID = c.CaseID),
    					
      ISNULL((SELECT ISNULL(SUM(tcpccf.TotalFee),0) FROM tblCase tcpcc WITH(NOLOCK)
    		INNER JOIN tblCaseCharge tcpccc WITH(NOLOCK)
    			ON tcpcc.CaseID = tcpccc.CaseID
    		LEFT OUTER JOIN tblCaseFee tcpccf WITH(NOLOCK)
    			ON tcpccf.CaseChargeID = tcpccc.CaseChargeID
    			
    				WHERE tcpcc.CaseID = c.CaseID AND tcpccc.CaseChargeID = cc.CaseChargeID
    
    			GROUP BY tcpccc.CaseChargeID),0) TotalChargesPerCharge,
    
      ISNULL((SELECT TOP 1 t.ReceiptNumber from tblTransactionDetail td WITH (NOLOCK)
    				INNER JOIN tblTransaction t WITH (NOLOCK)
    					ON td.TransactionID = t.TransactionID
    			WHERE td.CaseID = c.CaseID ORDER BY t.CashierDate DESC),0) ReceiptNumber		
    
      
    FROM
      tblCase c WITH(NOLOCK) 
      INNER JOIN tblCourtType crt WITH(NOLOCK)
        ON c.CourtTypeID = crt.CourtTypeID  
      INNER JOIN tblCaseCharge cc WITH(NOLOCK)
        ON c.CaseID = cc.CaseID
      
      INNER JOIN tblCaseEvent ce WITH(NOLOCK)
        ON c.CaseID = ce.CaseID
      
      INNER JOIN tblEvent ev WITH(NOLOCK)
        ON ce.EventID = ev.EventID
            
      LEFT OUTER JOIN tblCitation cit WITH(NOLOCK)
           ON cc.CitationID = cit.CitationID      
      LEFT OUTER JOIN tblLookup cfp WITH(NOLOCK)
        ON cc.CourtFinalPlea = cfp.Code AND cfp.LookupGroup = 'CourtFinalPlea'
      
      LEFT OUTER JOIN tblLookup casestatus WITH(NOLOCK)
        ON c.CaseStatus = casestatus.Code AND casestatus.LookupGroup = 'CaseStatus'
            
      LEFT OUTER JOIN tblLookup cat WITH(NOLOCK)
        ON cc.CourtActionTaken = cat.Code AND cat.LookupGroup = 'CourtActionTaken'    
      LEFT OUTER JOIN tblCaseBond cb WITH(NOLOCK)
        ON cc.CaseID = cb.CaseID AND cc.CaseChargeID = cb.CaseChargeID AND cb.BondStatus LIKE 'POSTED'
        
      LEFT OUTER JOIN tblCourtTypeCourtResult ctcrn WITH(NOLOCK)
    	ON c.CourtTypeID = ctcrn.CourtTypeID AND ce.CourtResult = ctcrn.CourtResult
    						      
      LEFT OUTER JOIN tblLookup l WITH(NOLOCK) 
        ON l.Code = ev.CourtRoomCode AND l.lookupgroup = 'courtroom'   
      
      LEFT OUTER JOIN tblEventType et WITH(NOLOCK)
        ON ce.CaseEventTypeID = et.EventTypeID
      LEFT OUTER JOIN tblStatute st  WITH (NOLOCK)
        ON 
          (
          SELECT 
            CASE cc.ChargePhase
              WHEN 'I' THEN cc.InitialStatuteID
              WHEN 'P' THEN cc.ProsecutorStatuteID
              WHEN 'C' THEN cc.CourtStatuteID
              ELSE cc.StatuteID
            END
          )  = st.StatuteID
      INNER JOIN tblPartyName pn WITH(NOLOCK)
        ON c.FirstDefendantID = pn.PartyID
      LEFT OUTER JOIN tblLookup lc WITH(NOLOCK)
        ON c.CustodyLocationCode = lc.Code AND lc.LookupGroup='CustodyLocation'
      --  Court Results for Selected CaseEvent
      LEFT OUTER JOIN tblCourtTypeCourtResult ctcr WITH(NOLOCK)
        ON c.CourtTypeID = ctcr.CourtTypeID AND
           ce.CourtResult = ctcr.CourtResult
        
      LEFT OUTER JOIN tblCaseParty cp WITH(NOLOCK)
           ON c.CaseID = cp.CaseID
      LEFT OUTER JOIN tblCaseParty pcp WITH(NOLOCK)
           ON cp.ParentCasePartyID = pcp.CasePartyID       
      LEFT OUTER JOIN tblParty p WITH(NOLOCK)
           ON c.FirstDefendantID = p.PartyID
      --LEFT OUTER JOIN tblParty p WITH(NOLOCK)
      --     ON cp.PartyID = p.PartyID       
      LEFT OUTER JOIN tblCasePartyType cpt WITH (NOLOCK)
          ON c.CourtTypeID = cpt.CourtTypeID AND cp.CasePartyType = cpt.CasePartyType         
           
          
      OUTER APPLY dbo.fnGetCasePartyAddressByCasePartyIDPartyID(cp.CasePartyID,cp.PartyID) cpacp
      OUTER APPLY dbo.fnGetCaseEventCourtRoomAddress(ev.CourtRoomCode) cecra
      
      OUTER APPLY dbo.fnGetNumberOfCasePartyTypesByCaseParty(c.CaseID, cp.CasePartyType) gncpp
      OUTER APPLY dbo.fnGetDepartmentPartyInfoByCourtType(crt.CourtType) dpi 
      OUTER Apply dbo.fnGetOrdinalDateSuffix(GetDate()) gods
      
              
    WHERE
    
    
      (ce.EventID IN(@EventIDs)) AND
      (ce.CourtResult NOT IN ('CONTINUED','COMPLETED')) AND
      (CaseStatus.Description NOT IN ('CLOSED'))
      --ISNULL(ctcr.Disposed, 0) = 0  --AND  -- Exclude Disposed Case Events for Selected Event
    
    
    ORDER BY
      CASE @ReportSortOrder
        WHEN 'Case Number'  THEN c.CaseNumber + ' -- ' + dbo.fnGetDisplayNameByPartyID(c.FirstPlaintiffID) + ' -- ' + (CONVERT (VARCHAR(MAX), c.FirstPlaintiffID))
        WHEN 'Defendant Name'  THEN dbo.fnGetDisplayNameByPartyID(c.FirstDefendantID) + ' -- ' + (CONVERT (VARCHAR(MAX), c.FirstDefendantID))
        WHEN 'Violation Date'  THEN CONVERT(VARCHAR,cc.OffenseDate,23) + ' -- ' + (CONVERT (VARCHAR(MAX), c.FirstDefendantID))
        WHEN 'Officer'  THEN dbo.fnGetDisplayNameByPartyID(
    												(SELECT TOP 1 cpd.PartyID 
    														FROM 
    															tblCaseParty cpd WITH(NOLOCK)
    																WHERE cpd.CaseID = c.CaseID AND 
    																	cpd.CasePartyType = 'OFF')) + ' -- ' + (CONVERT (VARCHAR(MAX), c.FirstDefendantID))
        ELSE ''
       END ASC

    Friday, August 01, 2014 3:06 PM

Answers

  • Hi,

    In your order by you are referencing the alias name 'c.'. So it is trying to look for the column in that table.

    You can populate the rank for the header and the detail row and can remove the alias name. Include the rank in your order by, so that header row will be on the top and the detail row will be sorted based on your condition.

    Regards,

    Brindha.

    • Marked as answer by Douang Friday, August 01, 2014 3:50 PM
    Friday, August 01, 2014 3:36 PM
  • I see the problem! those columns don't exist in the header.

    Add your case statement as a column and call it "Sorter"

    then change your order by to

    ORDER BY sorter ASC


    Don't forget to add a Sorter column to your header.
    • Edited by Patrick Hurst Friday, August 01, 2014 3:44 PM
    • Marked as answer by Douang Friday, August 01, 2014 3:50 PM
    Friday, August 01, 2014 3:43 PM

All replies

  • What's the error?

    You could insert the dummy row right into the table, or into a staging table as a real record.

    Friday, August 01, 2014 3:22 PM
  • Hi,

    Please help to recreate the issue and our server and to check the error. For this please post DDL+DML for relevant tables.


    [Personal Site] [Blog] [Facebook]signature

    Friday, August 01, 2014 3:26 PM
    Moderator
  • The error is pointing to my ORDER BY.

    Msg 104, Level 16, State 1, Line 183

    ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

    If I don't UNION ALL to my header record section, SQL does not complain about my ORDER BY.

    Thank you for your help!

    Friday, August 01, 2014 3:31 PM
  • Hi,

    In your order by you are referencing the alias name 'c.'. So it is trying to look for the column in that table.

    You can populate the rank for the header and the detail row and can remove the alias name. Include the rank in your order by, so that header row will be on the top and the detail row will be sorted based on your condition.

    Regards,

    Brindha.

    • Marked as answer by Douang Friday, August 01, 2014 3:50 PM
    Friday, August 01, 2014 3:36 PM
  • I see the problem! those columns don't exist in the header.

    Add your case statement as a column and call it "Sorter"

    then change your order by to

    ORDER BY sorter ASC


    Don't forget to add a Sorter column to your header.
    • Edited by Patrick Hurst Friday, August 01, 2014 3:44 PM
    • Marked as answer by Douang Friday, August 01, 2014 3:50 PM
    Friday, August 01, 2014 3:43 PM
  • Thank you for your help!
    Friday, August 01, 2014 3:50 PM
  • Thank you for your help!
    Friday, August 01, 2014 3:50 PM
  • >> I would like to create a header or dum by using SELECT A, B, C, etc and then I use UNION ALL to select my detail records [sic].<<

    Besides not following Netiquette, you are ignorant. A row is not a records!! You are trying to write 1950's COBOL in SQL. Every row in table has to be structured exactly the same. Your “record_type” says this is not a table, but a stupid attempt to mimic a COBOL file with variant records. 

    I see you also tibble – the design flaw of “tbl-” as a prefix. Google  this for the articles that make fun of you. Why do you think that “code” is a clear, precise data element name? What is a “type_id”? Do you know about the ISO-11179 naming standards? 

    I see you miss FORTRAN II. It required that you prefix functions with “fn_” prefixes; SQL programmers do to do that! 

    You have just been told in public by a well-known SQL expert ("wrote the Standards" level expert) that your SQL stinks. You need to stop programming and find help. Your entire approach is totally wrong.  Please consider this and show it to your boss so he can replace you. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, August 01, 2014 10:05 PM
  • If to prefix all tables with tbl is to 'tibble' then, to prefix all functions with fn is to... 'finble'? ?

    I can find no reference in Google to any reliable literature that describes this act as 'tibbling'- is it perhaps buried deep within the heart of ISO-11179?  :-]

    However.. it does seem to be part of the Leszynski naming convention. A type of arcane Hungarian notation that was used by Access 97 programmers... http://en.wikipedia.org/wiki/Leszynski_naming_convention




    Thanks! Josh



    Tuesday, August 12, 2014 9:41 AM