SQL Group by in STUFF function

  • chowdarydeepus@gmail.com

    Old Hand

    Points: 338

    I have one question in SQL and would like to take some help from you. I am using the below query to group by based on the test results and then can able to send these results to the recipients.

    SELECT alertQueue.[Prod No] AS ProdNumber
    , SUBSTRING(alertQueue.[Prod No], PATINDEX('%[^0]%', alertQueue.[Prod No] +'.'), LEN(alertQueue.[Prod No])) AS ProducerNumber
    , alertQueue.[Tank No] AS TankNo
    , alertQueue.[Sequence_Number] AS SeqNumber
    , alertQueue.[Notification_Account] AS NotificationAccount
    , alertQueue.[Login] AS [Login]
    , alertQueue.[Manifest_Number] AS ManifestNumber
    , prod.[Prod Div] AS Division
    , prod.[Prod Name] AS Name
    , alertQueue.[Notification_Type] AS NotificationType
    , alertQueue.[Pickup_Date] AS PickupDate
    , STUFF((SELECT CASE alertQueue.[Notification_Type]
    WHEN 'Phone'
    THEN ' <br/> ' + nestedAlertQueue.[Test_Name] + ':' + CAST(nestedAlertQueue.[Test_Value] AS varchar(MAX))
    WHEN 'Email'
    THEN ' <br/> ' + nestedAlertQueue.[Test_Name] + ' is ' + CAST(nestedAlertQueue.[Test_Value] AS varchar(MAX))
    + CASE nestedAlertQueue.[Test_Name]
    WHEN 'BF' THEN ' ( ' + alertQueue.Threshold + ' ' + alertQueue.Threshold_Value + ' )'
    WHEN 'LPC' THEN ' ( ' + alertQueue.Threshold + ' ' + alertQueue.Threshold_Value + ' )'
    WHEN 'MUN' THEN ' ( ' + alertQueue.Threshold + ' ' + alertQueue.Threshold_Value + ' )'
    WHEN 'PIC' THEN ' ( ' + alertQueue.Threshold + ' ' + alertQueue.Threshold_Value + ' )'
    WHEN 'PRO' THEN ' ( ' + alertQueue.Threshold + ' ' + alertQueue.Threshold_Value + ' )'
    WHEN 'SCC' THEN ' ( ' + alertQueue.Threshold + ' ' + alertQueue.Threshold_Value + ' )'
    ELSE ''
    END
    ELSE ''
    END
    FROM [LIBECIRTP].[API].[Test_Alert_Queue] (NOLOCK) AS nestedAlertQueue
    INNER JOIN [LIBECIRTP].[report].[Producer] (NOLOCK) AS nestedProducer
    ON nestedProducer.[Prod No KEY] = nestedAlertQueue.[Prod No]
    WHERE nestedAlertQueue.[Prod No] = alertQueue.[Prod No]
    AND nestedAlertQueue.[Tank No] = alertQueue.[Tank No]
    AND nestedAlertQueue.[Sequence_Number] = alertQueue.[Sequence_Number]
    AND nestedAlertQueue.[Notification_Account] = alertQueue.[Notification_Account]
    AND nestedAlertQueue.[Login] = alertQueue.[Login]
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') ,1,2,'') AS TestDetails
    FROM [LIBECIRTP].[API].[Test_Alert_Queue] (NOLOCK) AS alertQueue
    INNER JOIN [LIBECIRTP].[report].[Producer] (NOLOCK) AS prod
    ON prod.[Prod No KEY] = alertQueue.[Prod No]
    WHERE alertQueue.[Notification_Type] = 'Email'
    GROUP BY alertQueue.[Prod No]
    , alertQueue.[Tank No]
    , alertQueue.[Sequence_Number]
    , alertQueue.[Notification_Account]
    , alertQueue.[Login]
    , alertQueue.[Manifest_Number]
    , prod.[Prod Div]
    , prod.[Prod Name]
    , alertQueue.[Notification_Type]
    , alertQueue.[Pickup_Date]
    ORDER BY alertQueue.[Prod No]
    , alertQueue.[Notification_Account]
    , alertQueue.[Login] ASC

    But when I run this query I am getting an error message saying

    > Msg 8120, Level 16, State 1, Line 18
    Column 'LIBECIRTP.API.Test_Alert_Queue.Threshold' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    After that, I have added the missing columns to both SELECT and GROUP BY which is working fine but returning more records than needed.

    So, if the alertQueue.[Notification_Type] = 'Phone' there shouldn't be alertQueue.[Threshold] and alertQueue.[Threshold_Value] and if  alertQueue.[Notification_Type] = 'Email' there should be alertQueue.[Threshold] and alertQueue.[Threshold_Value] in both SELECT and GROUP BY.

    Can any one of you please help me with this. I really appreciate any help.

  • scdecade

    SSChasing Mays

    Points: 654

    The 2nd item in the select list is not included in the GROUP BY clause.  Shouldn't it be?  The tables which are nested inside the STUFF function join to the outer tables... maybe there's some issue there.  I'd guess that's what's causing the error.

    • This reply was modified 4 weeks, 1 day ago by  scdecade.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • chowdarydeepus@gmail.com

    Old Hand

    Points: 338

    Thanks, it worked. I really appreciate your help. Made below changes.

    Instead of alertQueue used nestedAlertQueue and it resolved my issue.

    SELECT alertQueue.[Prod No] AS ProdNumber
    , SUBSTRING(alertQueue.[Prod No], PATINDEX('%[^0]%', alertQueue.[Prod No] +'.'), LEN(alertQueue.[Prod No])) AS ProducerNumber
    , alertQueue.[Tank No] AS TankNo
    , alertQueue.[Sequence_Number] AS SeqNumber
    , alertQueue.[Notification_Account] AS NotificationAccount
    , alertQueue.[Login] AS [Login]
    , alertQueue.[Manifest_Number] AS ManifestNumber
    , prod.[Prod Div] AS Division
    , prod.[Prod Name] AS Name
    , alertQueue.[Notification_Type] AS NotificationType
    , alertQueue.[Pickup_Date] AS PickupDate
    , STUFF((SELECT CASE alertQueue.[Notification_Type]
    WHEN 'Phone'
    THEN CAST(CONCAT(' <br/> ', nestedAlertQueue.[Test_Name], ': ', nestedAlertQueue.[Test_Value]) AS varchar(MAX))
    WHEN 'Email'
    THEN CAST(CONCAT(' <br/> '
    , nestedAlertQueue.[Test_Name]
    , ' is '
    , nestedAlertQueue.[Test_Value]
    , CASE nestedAlertQueue.[Test_Name]
    WHEN 'BF' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    WHEN 'LPC' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    WHEN 'MUN' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    WHEN 'PIC' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    WHEN 'PRO' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    WHEN 'SCC' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    WHEN 'COLI' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    WHEN 'FFA' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    WHEN 'FRZP' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    WHEN 'INH' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    WHEN 'ROPY' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    WHEN 'SED' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    WHEN 'SPC' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    WHEN 'TEMP' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
    ELSE ''
    END) AS VARCHAR(MAX))
    ELSE ''
    END
    FROM [LIBECIRTP].[API].[Test_Alert_Queue] (NOLOCK) AS nestedAlertQueue
    INNER JOIN [LIBECIRTP].[report].[Producer] (NOLOCK) AS nestedProducer
    ON nestedProducer.[Prod No KEY] = nestedAlertQueue.[Prod No]
    WHERE nestedAlertQueue.[Prod No] = alertQueue.[Prod No]
    AND nestedAlertQueue.[Tank No] = alertQueue.[Tank No]
    AND nestedAlertQueue.[Sequence_Number] = alertQueue.[Sequence_Number]
    AND nestedAlertQueue.[Notification_Account] = alertQueue.[Notification_Account]
    AND nestedAlertQueue.[Login] = alertQueue.[Login]
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') ,1,2,'') AS TestDetails
    FROM [LIBECIRTP].[API].[Test_Alert_Queue] (NOLOCK) AS alertQueue
    INNER JOIN [LIBECIRTP].[report].[Producer] (NOLOCK) AS prod
    ON prod.[Prod No KEY] = alertQueue.[Prod No]
    GROUP BY alertQueue.[Prod No]
    , alertQueue.[Tank No]
    , alertQueue.[Sequence_Number]
    , alertQueue.[Notification_Account]
    , alertQueue.[Login]
    , alertQueue.[Manifest_Number]
    , prod.[Prod Div]
    , prod.[Prod Name]
    , alertQueue.[Notification_Type]
    , alertQueue.[Pickup_Date]
    ORDER BY alertQueue.[Prod No]
    , alertQueue.[Notification_Account]
    , alertQueue.[Login] ASC

     

     

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply