April 15, 2014 at 10:25 am
Hi All,
I'm trying to take a portion of a stored procedure and perform a union with another table I've created and populated, but I keep running into errors,the latest of which states "Incorrect syntax near the keyword 'UNION'." Can anyone please advise on what I'm doing wrong? Thanks.
SELECT
case
when
ath_stlmnt_instr_id is null then
(select ae.stlmnt_line_instr_id from Accting_body ae where ae.header_id = ath_header_id and
ae.body_id = ath_body_id)
else
ath_stlmnt_instr_id
end
as ath_instr_id,
ath_instr_id as orig_instr_id,
ath_gl_num,
ath_cost_cntr,
case
when ath_postype = 'GLD' then
sum(ath_usdamt)
else
case
when ath_postype = 'GLC' then
sum(ath_usdamt) * -1
end
end
as ath_usdamt,
count(*)
from dbo.ACCTING_TRANSACTION_HISTORY
where ath_gl_num in (select acct from MTB_CHART_ACCTS where acct_category = 'AST')
and ath_postype in ('GLD','GLC')
group by ath_stlmnt_instr_id, ath_instr_id, ath_gl_num, ath_cost_cntr
order by ath_instr_id
UNION
SELECT stlmnt_instr_id,instr_id,gl_num, cost_cntr, usdamt
FROM dbo.ACCTING_ADJUST
group by stlmnt_instr_id, instr_id, gl_num, cost_cntr
order by instr_id
April 15, 2014 at 10:35 am
Try commenting out the first "order by" clause. It makes no sense to order the data before UNION anyway
April 15, 2014 at 10:37 am
There must be only one... ORDER BY... at the end of your query.
--Vadim R.
April 15, 2014 at 11:33 am
Thanks for your input, all. So, I tried using the Group By and Order By at the end of the query, but now it's returning a "Column 'dbo.ACCTING_TRANSACTION_HISTORY.ath_stlmnt_instr_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." error. Any ideas?
SELECT
case
when
ath_stlmnt_instr_id is null then
(select ae.stlmnt_line_instr_id from Accting_body ae where ae.header_id = ath_header_id and
ae.body_id = ath_body_id)
else
ath_stlmnt_instr_id
end
as ath_instr_id,
ath_instr_id as orig_instr_id,
ath_gl_num,
ath_cost_cntr,
case
when ath_postype = 'GLD' then
sum(ath_usdamt)
else
case
when ath_postype = 'GLC' then
sum(ath_usdamt) * -1
end
end
as ath_usdamt,
count(*)
from dbo.ACCTING_TRANSACTION_HISTORY
where ath_gl_num in (select acct from MTB_CHART_ACCTS where acct_category = 'AST')
and ath_postype in ('GLD','GLC')
--group by ath_stlmnt_instr_id, ath_instr_id, ath_gl_num, ath_cost_cntr
--order by ath_instr_id
UNION
SELECT stlmnt_instr_id,instr_id,gl_num, cost_cntr, usdamt
FROM dbo.ACCTING_ADJUST
group by stlmnt_instr_id, instr_id, gl_num, cost_cntr
order by instr_id
April 15, 2014 at 11:41 am
In this case the error message means exactly what it says. In general, when you have aggregates (like the SUM() function in your query), you have to list *all* the non-aggregated columns in the group-by clause.
April 15, 2014 at 12:28 pm
daniness (4/15/2014)
Thanks for your input, all. So, I tried using the Group By and Order By at the end of the query, but now it's returning a "Column 'dbo.ACCTING_TRANSACTION_HISTORY.ath_stlmnt_instr_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." error. Any ideas?
Why did you remove the group by? I have a feeling you don't understand what a UNION does. It take the results of two queries and combines them into a single result set. Each query MUST execute and return the correct rows independently. Your top query has aggregate data, when you remove the group by it isn't going to work.
Of course, in this case even putting the group by back in isn't going to help because you have a different number of columns in each query. Your first query has 6 columns and the second one has only 5.
You also need to be aware of datatypes. If you mix datatypes you are likely to run into implicit conversion errors.
I think you could greatly simplify your query by removing several case expressions and subselects. Something like this might be close but it is hard to tell for sure without ddl and sample data. The case expression evaluating ath-postype has me a bit baffled. You are performing an aggregate based on the value of a single row. That means the results are unpredictable if you have a row with each of those two values in a given group.
SELECT ISNULL(ath_stlmnt_instr_id, ae.stlmnt_line_instr_id) AS ath_instr_id
,ath_instr_id AS orig_instr_id
,ath_gl_num
,ath_cost_cntr
,CASE
WHEN ath_postype = 'GLD'
THEN sum(ath_usdamt)
WHEN ath_postype = 'GLC'
THEN sum(ath_usdamt) * - 1
END AS ath_usdamt
,count(*)
FROM dbo.ACCTING_TRANSACTION_HISTORY
left join Accting_body ae on ae.header_id = ath_header_id AND ae.body_id = ath_body_id
inner join MTB_CHART_ACCTS mca on mca.acct = ath_gl_num
WHERE mca.acct_category = 'AST'
AND ath_postype IN ('GLD', 'GLC')
group by ath_stlmnt_instr_id, ath_instr_id, ath_gl_num, ath_cost_cntr
_______________________________________________________________
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/
April 16, 2014 at 5:20 am
Maybe the Case/Sum part is intended to be;
SELECT ISNULL(ath_stlmnt_instr_id, ae.stlmnt_line_instr_id) AS ath_instr_id
,ath_instr_id AS orig_instr_id
,ath_gl_num
,ath_cost_cntr
,sum(CASE
WHEN ath_postype = 'GLD'
THEN ath_usdamt
WHEN ath_postype = 'GLC'
THEN ath_usdamt * - 1
END) AS ath_usdamt
,count(*)
FROM dbo.ACCTING_TRANSACTION_HISTORY
left join Accting_body ae on ae.header_id = ath_header_id AND ae.body_id = ath_body_id
inner join MTB_CHART_ACCTS mca on mca.acct = ath_gl_num
WHERE mca.acct_category = 'AST'
AND ath_postype IN ('GLD', 'GLC')
group by ath_stlmnt_instr_id, ath_instr_id, ath_gl_num, ath_cost_cntr
or perhaps the query should also be grouped by ath_postype.
April 16, 2014 at 7:55 am
Hi Sean and everyone,
Thanks for your input. So I tried the following per your suggstion:
SELECT ISNULL(ath_stlmnt_instr_id, ae.stlmnt_line_instr_id) AS ath_instr_id
,ath_instr_id AS orig_instr_id
,ath_gl_num
,ath_cost_cntr
,CASE
WHEN ath_postype = 'GLD'
THEN sum(ath_usdamt)
WHEN ath_postype = 'GLC'
THEN sum(ath_usdamt) * - 1
END AS ath_usdamt
,count(*)
FROM dbo.ACCTING_TRANSACTION_HISTORY
left join Accting_body ae on ae.header_id = ath_header_id AND ae.body_id = ath_body_id
inner join MTB_CHART_ACCTS mca on mca.acct = ath_gl_num
WHERE mca.acct_category = 'AST'
AND ath_postype IN ('GLD', 'GLC')
group by ath_stlmnt_instr_id, ath_instr_id, ath_gl_num, ath_cost_cntr
Union
SELECT stlmnt_instr_id,instr_id,gl_num, cost_cntr, usdamt
FROM dbo.ACCTING_ADJUST
group by stlmnt_instr_id, instr_id, gl_num, cost_cntr
but amd now receiving a "Column 'Accting_body.stlmnt_line_instr_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."...isn't the count(*) an aggregate fuction? Please advise how I can correct this. Thanks.
April 16, 2014 at 8:16 am
I think you need to modify the Group By clause in the first select to
group by ath_stlmnt_instr_id, ath_instr_id, ae.stlmnt_line_instr_id, ath_gl_num, ath_cost_cntr
but you should also review the way you're handling the ath_usdamt calculation.
April 16, 2014 at 10:04 am
Hi Chris and everyone,
Thanks for the suggestion. However from what has been posted here earlier, the fields in both Group By sections need to match. I tried it, nonetheless, and am now receiving "Columnt dbo.ACCTING_TRANSACTION_HISTORY.ath_postype' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" errors.
April 16, 2014 at 10:08 am
daniness (4/16/2014)
Hi Chris and everyone,Thanks for the suggestion. However from what has been posted here earlier, the fields in both Group By sections need to match. I tried it, nonetheless, and am now receiving "Columnt dbo.ACCTING_TRANSACTION_HISTORY.ath_postype' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" errors.
What is the actual query you are running?
_______________________________________________________________
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/
April 16, 2014 at 10:21 am
Hi Sean,
This is the query:
SELECT ISNULL(ath_stlmnt_instr_id, ae.stlmnt_line_instr_id) AS ath_instr_id
,ath_instr_id AS orig_instr_id
,ath_gl_num
,ath_cost_cntr
,CASE
WHEN ath_postype = 'GLD'
THEN sum(ath_usdamt)
WHEN ath_postype = 'GLC'
THEN sum(ath_usdamt) * - 1
END AS ath_usdamt
,count(*)
FROM dbo.ACCTING_TRANSACTION_HISTORY
left join Accting_body ae on ae.header_id = ath_header_id AND ae.body_id = ath_body_id
inner join MTB_CHART_ACCTS mca on mca.acct = ath_gl_num
WHERE mca.acct_category = 'AST'
AND ath_postype IN ('GLD', 'GLC')
group by ath_stlmnt_instr_id, ath_instr_id, ath_gl_num, ath_cost_cntr
Union
SELECT stlmnt_instr_id,instr_id,gl_num, cost_cntr, usdamt
FROM dbo.ACCTING_ADJUST
group by stlmnt_instr_id, instr_id, gl_num, cost_cntr
April 16, 2014 at 10:23 am
Daniness,
see if this works for you.. might be a starting point atleast..
;WITH CTE AS
(SELECT case when ath_stlmnt_instr_id is null then (select ae.stlmnt_line_instr_id from Accting_body ae where ae.header_id = ath_header_id and
ae.body_id = ath_body_id)
else ath_stlmnt_instr_id end as ath_instr_id,
ath_instr_id as orig_instr_id,
ath_gl_num,
ath_cost_cntr,
case when ath_postype = 'GLD' then ath_usdamt
when ath_postype = 'GLC' then ath_usdamt * -1 end as ath_usdamt
from dbo.ACCTING_TRANSACTION_HISTORY
where ath_gl_num in (select acct from MTB_CHART_ACCTS where acct_category = 'AST') and ath_postype in ('GLD','GLC')
)
SELECT ath_instr_id, orig_instr_id, ath_gl_num, ath_cost_cntr, SUM(ath_usdamt), COUNT(*)
FROM CTE
group by ath_instr_id, orig_instr_id, ath_gl_num, ath_cost_cntr
UNION
SELECT stlmnt_instr_id,instr_id,gl_num, cost_cntr, usdamt
FROM dbo.ACCTING_ADJUST
order by instr_id
I may be missing something too, so make sure you see what you want to see...
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
April 16, 2014 at 10:42 am
daniness (4/16/2014)
Hi Sean,This is the query:
SELECT ISNULL(ath_stlmnt_instr_id, ae.stlmnt_line_instr_id) AS ath_instr_id
,ath_instr_id AS orig_instr_id
,ath_gl_num
,ath_cost_cntr
,CASE
WHEN ath_postype = 'GLD'
THEN sum(ath_usdamt)
WHEN ath_postype = 'GLC'
THEN sum(ath_usdamt) * - 1
END AS ath_usdamt
,count(*)
FROM dbo.ACCTING_TRANSACTION_HISTORY
left join Accting_body ae on ae.header_id = ath_header_id AND ae.body_id = ath_body_id
inner join MTB_CHART_ACCTS mca on mca.acct = ath_gl_num
WHERE mca.acct_category = 'AST'
AND ath_postype IN ('GLD', 'GLC')
group by ath_stlmnt_instr_id, ath_instr_id, ath_gl_num, ath_cost_cntr
Union
SELECT stlmnt_instr_id,instr_id,gl_num, cost_cntr, usdamt
FROM dbo.ACCTING_ADJUST
group by stlmnt_instr_id, instr_id, gl_num, cost_cntr
Why do you have a group by in the second query? The second query can't possibly work because you are grouping by columns not in the select list.
I think you need to do some reading on grouping and aggregate data. You seem to be very confused about how it works. You need to first run each query independent of the other (get rid of the union for now). Once you have each query returning the results you want, then you can use your UNION. You might also be able to use UNION ALL instead of just union but that depends on the data and the requirements.
_______________________________________________________________
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/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply