Incorrect syntax error when using group by

  • I'm trying to do a join and then a group by and order by, but I'm getting an "Incorrect syntax near 'ds' error. ds is the alias for the dataset which is the union of the 2 inner queries. Any help would be greatly appreciated:

    -- Add the parameters for the stored procedure here

    Declare @FromDt as date = '01-01-2011',

    @ThruDt as date = '03-11-2013',

    @Region as varchar(50) = 'Claims',

    @Queue as varchar(50) = 'Catch-All';

    Declare @RegionTbl as table(RegionName varchar(50));

    Declare @QueueTbl as table(QueueName varchar(50));

    Insert @RegionTbl

    select Value from hpexprpt.dbo.split(@Region,',');

    Insert @QueueTbl

    select Value from hpexprpt.dbo.split(@Queue,',');

    ( select users.last_name + ',' + users.first_name as [User ID]

    , workflow_regions.name as Region

    , queues.name as [Queue]

    , work_item_statuses.name as [Status]

    , case

    when convert(varchar,work_items.creation_date,110) < @FromDt then 'From Backlog'

    else 'Current'

    end as [Class]

    , wf_jobs.wf_job_id as [JobID]

    , work_items.elapsed_time

    , CONVERT(varchar, DATEADD(ms, work_items.elapsed_time * 1000, 0), 114) as [Total Time]

    , DATEADD(SECOND,(work_items.elapsed_time * -1),work_items.completion_date) as [Start_Date]

    , work_items.completion_date

    from hpexpprod.dbo.work_items join

    hpexpprod.dbo.queues on queues.queue_uid = work_items.queue_uid join

    hpexpprod.dbo.workflow_regions on workflow_regions.workflow_region_uid = work_items.workflow_region_uid join

    hpexpprod.dbo.work_item_statuses on work_item_statuses.work_item_status_uid = work_items.work_item_status_uid join

    HPEXPPROD.dbo.wf_jobs on wf_jobs.wf_job_uid = work_items.wf_job_uid join

    hpexpprod.dbo.actors on actors.actor_uid = work_items.actor_uid left join

    HPEXPPROD.dbo.users on users.actor_uid = actors.actor_uid

    where workflow_regions.name in (select * from @RegionTbl)

    and queues.name in (select * from @QueueTbl)

    and (

    cast(isnull(work_items.completion_date,'') as date) between @FromDt and @ThruDt

    or

    cast(work_items.last_updated_date as date) between @FromDt and @ThruDt

    )

    union

    SELECT users.last_name + ', ' + users.first_name as [User ID]

    ,workflow_regions.name as Region

    ,btr.name as [Queue]

    ,'Break' as [Status]

    ,case

    when convert(varchar, btt.creation_date,110) < @FromDt then 'From Backlog'

    else 'Current'

    end as [Class]

    , '' as [JobId]

    ,btt.elapsed_time

    ,CONVERT(varchar, DATEADD(ms,btt.elapsed_time * 1000, 0), 114) as [Total Time]

    ,DATEADD(SECOND,(btt.elapsed_time * -1),btt.creation_date) as [Start_Date]

    , btt.creation_date completion_date

    FROM

    HPEXPPROD.dbo.break_time_tracking btt join

    HPEXPPROD.dbo.break_time_reasons btr on btr.break_time_reason_uid = btt.break_time_reason_uid join

    hpexpprod.dbo.workflow_regions on workflow_regions.workflow_region_uid = btt.workflow_region_uid join

    HPEXPPROD.dbo.actors on actors.actor_uid = btt.actor_uid left join

    HPEXPPROD.dbo.users on users.actor_uid = actors.actor_uid

    where workflow_regions.name in (select * from @RegionTbl)

    and cast(isnull(btt.creation_date,'') as date) between @FromDt and @ThruDt)ds

    group by ds.

    order by 9 asc

  • whoops thought i had it...still looking

    ok got it;

    looks like you were starting to wrap the big query with SELECT * () ds

    WHERE....

    there's a beginning open parenthesis, but no select * FROM:

    -- Add the parameters for the stored procedure here

    DECLARE

    @FromDt AS DATE = '01-01-2011',

    @ThruDt AS DATE = '03-11-2013',

    @Region AS VARCHAR(50) = 'Claims',

    @Queue AS VARCHAR(50) = 'Catch-All';

    DECLARE @RegionTbl AS TABLE(

    RegionName VARCHAR(50));

    DECLARE @QueueTbl AS TABLE(

    QueueName VARCHAR(50));

    INSERT @RegionTbl

    SELECT

    Value

    FROM hpexprpt.dbo.SPLIT(@Region, ',');

    INSERT @QueueTbl

    SELECT

    Value

    FROM hpexprpt.dbo.SPLIT(@Queue, ',');

    SELECT

    *

    FROM (SELECT

    users.last_name + ',' + users.first_name AS [User ID],

    workflow_regions.name AS Region,

    queues.name AS [Queue],

    work_item_statuses.name AS [Status],

    CASE

    WHEN CONVERT(VARCHAR, work_items.creation_date, 110) < @FromDt

    THEN 'From Backlog'

    ELSE 'Current'

    END AS [Class],

    wf_jobs.wf_job_id AS [JobID],

    work_items.elapsed_time,

    CONVERT(VARCHAR, DATEADD(ms, work_items.elapsed_time * 1000, 0), 114) AS [Total Time],

    DATEADD(SECOND, ( work_items.elapsed_time * -1 ), work_items.completion_date) AS [Start_Date],

    work_items.completion_date

    FROM hpexpprod.dbo.work_items

    JOIN hpexpprod.dbo.queues

    ON queues.queue_uid = work_items.queue_uid

    JOIN hpexpprod.dbo.workflow_regions

    ON workflow_regions.workflow_region_uid = work_items.workflow_region_uid

    JOIN hpexpprod.dbo.work_item_statuses

    ON work_item_statuses.work_item_status_uid = work_items.work_item_status_uid

    JOIN HPEXPPROD.dbo.wf_jobs

    ON wf_jobs.wf_job_uid = work_items.wf_job_uid

    JOIN hpexpprod.dbo.actors

    ON actors.actor_uid = work_items.actor_uid

    LEFT JOIN HPEXPPROD.dbo.users

    ON users.actor_uid = actors.actor_uid

    WHERE workflow_regions.name IN (SELECT

    *

    FROM @RegionTbl)

    AND queues.name IN (SELECT

    *

    FROM @QueueTbl)

    AND ( CAST(ISNULL(work_items.completion_date, '') AS DATE) BETWEEN @FromDt AND @ThruDt

    OR CAST(work_items.last_updated_date AS DATE) BETWEEN @FromDt AND @ThruDt )

    UNION

    SELECT

    users.last_name + ', ' + users.first_name AS [User ID],

    workflow_regions.name AS Region,

    btr.name AS [Queue],

    'Break' AS [Status],

    CASE

    WHEN CONVERT(VARCHAR, btt.creation_date, 110) < @FromDt

    THEN 'From Backlog'

    ELSE 'Current'

    END AS [Class],

    '' AS [JobId],

    btt.elapsed_time,

    CONVERT(VARCHAR, DATEADD(ms, btt.elapsed_time * 1000, 0), 114) AS [Total Time],

    DATEADD(SECOND, ( btt.elapsed_time * -1 ), btt.creation_date) AS [Start_Date],

    btt.creation_date completion_date

    FROM HPEXPPROD.dbo.break_time_tracking btt

    JOIN HPEXPPROD.dbo.break_time_reasons btr

    ON btr.break_time_reason_uid = btt.break_time_reason_uid

    JOIN hpexpprod.dbo.workflow_regions

    ON workflow_regions.workflow_region_uid = btt.workflow_region_uid

    JOIN HPEXPPROD.dbo.actors

    ON actors.actor_uid = btt.actor_uid

    LEFT JOIN HPEXPPROD.dbo.users

    ON users.actor_uid = actors.actor_uid

    WHERE workflow_regions.name IN (SELECT

    *

    FROM @RegionTbl)

    AND CAST(ISNULL(btt.creation_date, '') AS DATE) BETWEEN @FromDt AND @ThruDt)ds

    GROUP BY ds.

    ORDER BY

    9 ASC

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi Lowell and All,

    I appreciate your input. I see what you're saying by how there's no "Select * From", but what I have is a union, so I'm not sure I'd need to include this, but I tried it and am receiving the error, "Column 'ds.Region' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." Any thoughts, anyone please?

  • Here is your query after running it through a formatter.

    SELECT *

    FROM (

    SELECT users.last_name + ',' + users.first_name AS [User ID]

    ,workflow_regions.NAME AS Region

    ,queues.NAME AS [Queue]

    ,work_item_statuses.NAME AS [Status]

    ,CASE

    WHEN convert(VARCHAR, work_items.creation_date, 110) < @FromDt

    THEN 'From Backlog'

    ELSE 'Current'

    END AS [Class]

    ,wf_jobs.wf_job_id AS [JobID]

    ,work_items.elapsed_time

    ,CONVERT(VARCHAR, DATEADD(ms, work_items.elapsed_time * 1000, 0), 114) AS [Total Time]

    ,DATEADD(SECOND, (work_items.elapsed_time * - 1), work_items.completion_date) AS [Start_Date]

    ,work_items.completion_date

    FROM hpexpprod.dbo.work_items

    INNER JOIN hpexpprod.dbo.queues ON queues.queue_uid = work_items.queue_uid

    INNER JOIN hpexpprod.dbo.workflow_regions ON workflow_regions.workflow_region_uid = work_items.workflow_region_uid

    INNER JOIN hpexpprod.dbo.work_item_statuses ON work_item_statuses.work_item_status_uid = work_items.work_item_status_uid

    INNER JOIN HPEXPPROD.dbo.wf_jobs ON wf_jobs.wf_job_uid = work_items.wf_job_uid

    INNER JOIN hpexpprod.dbo.actors ON actors.actor_uid = work_items.actor_uid

    LEFT JOIN HPEXPPROD.dbo.users ON users.actor_uid = actors.actor_uid

    WHERE workflow_regions.NAME IN (

    SELECT *

    FROM @RegionTbl

    )

    AND queues.NAME IN (

    SELECT *

    FROM @QueueTbl

    )

    AND (

    cast(isnull(work_items.completion_date, '') AS DATE) BETWEEN @FromDt

    AND @ThruDt

    OR cast(work_items.last_updated_date AS DATE) BETWEEN @FromDt

    AND @ThruDt

    )

    UNION

    SELECT users.last_name + ', ' + users.first_name AS [User ID]

    ,workflow_regions.NAME AS Region

    ,btr.NAME AS [Queue]

    ,'Break' AS [Status]

    ,CASE

    WHEN convert(VARCHAR, btt.creation_date, 110) < @FromDt

    THEN 'From Backlog'

    ELSE 'Current'

    END AS [Class]

    ,'' AS [JobId]

    ,btt.elapsed_time

    ,CONVERT(VARCHAR, DATEADD(ms, btt.elapsed_time * 1000, 0), 114) AS [Total Time]

    ,DATEADD(SECOND, (btt.elapsed_time * - 1), btt.creation_date) AS [Start_Date]

    ,btt.creation_date completion_date

    FROM HPEXPPROD.dbo.break_time_tracking btt

    INNER JOIN HPEXPPROD.dbo.break_time_reasons btr ON btr.break_time_reason_uid = btt.break_time_reason_uid

    INNER JOIN hpexpprod.dbo.workflow_regions ON workflow_regions.workflow_region_uid = btt.workflow_region_uid

    INNER JOIN HPEXPPROD.dbo.actors ON actors.actor_uid = btt.actor_uid

    LEFT JOIN HPEXPPROD.dbo.users ON users.actor_uid = actors.actor_uid

    WHERE workflow_regions.NAME IN (

    SELECT *

    FROM @RegionTbl

    )

    AND cast(isnull(btt.creation_date, '') AS DATE) BETWEEN @FromDt

    AND @ThruDt

    ) ds

    GROUP BY ds.

    ORDER BY 9 ASC

    You have a number of columns in your query and you are trying to group by only 1 of those columns. You can't do that. You would need to group by all the columns, but with no aggregation I wonder why you are using a group by here?

    Also you should never use ordinal position as your order by column. You should instead use the column name. This not only makes it much clearer what you are ordering by but also it prevents bugs from getting into the system when this query changes.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • You're grouping on , but none of the other fields returned are grouped on or are aggregated in any way - they need to be in order for it to work. How is the data intended to be aggregated?

    Thanks

  • Column 'ds.Region' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

    You are going to get this message one by one for all the columns other than ds. which are not used for aggregare calculations and are coming as result of SELECT for ds.

Viewing 6 posts - 1 through 5 (of 5 total)

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