February 23, 2016 at 4:21 am
Help please,
This statment give me an error "HAVING can only have expressions with aggregates or columns in GROUP BY clause.":
SELECT
UNIQUE a.whse_id,
a.ord_id,
b.prod_id,
d.ord_qty*d.unit_ship_cse qty_orig,
b.ckpt_id,
Sum(trunc(b.prod_qty)) qty_final
FROM system:iord a, system:ishd b, archive:stord c, archive:stordd d
WHERE a.dc_id = b.dc_id
AND a.whse_id = b.whse_id
AND a.ord_id = b.ord_id
AND b.sgmt_id = a.sgmt_id
AND b.prod_qty > 0
AND c.seeb_key = d.seeb_key
AND CAST(b.invc_id AS INT) = CAST(c.invc_id AS INT)
AND CAST(b.prod_id AS INT) = CAST(d.prod_id AS INT)
AND a.whse_id = 5
AND b.ckpt_id = 1199625
GROUP BY 1,2,3,4,5
HAVING CAST(d.ord_qty*d.unit_ship_cse AS INT) < CAST(SUM(b.prod_qty) AS INT)
ORDER BY 2,3
Where is the error and how i can fix please?
tHKS,
JC
February 23, 2016 at 4:25 am
Start by specifying column names in group by and order by, If, once you've finished doing that there's still an error, post the revised query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2016 at 4:39 am
I do not understand? If remove the line from having run perfectly 🙁
Thks,
JC
February 23, 2016 at 4:47 am
I didn't say remove the HAVING line.
The ordinal specification of columns is a really bad idea, hard to read and prone to errors. Go and fix the group by and order by to use column names, then when the query's readable, if it still gives an error post the revised query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2016 at 4:53 am
ok,
I already change but have the same error:
SELECT
UNIQUE a.whse_id,
a.ord_id,
b.prod_id,
d.ord_qty*d.unit_ship_cse qty_orig,
b.ckpt_id,
Sum(trunc(b.prod_qty)) qty_final
FROM system:iord a, system:ishd b, archive:stord c, archive:stordd d
WHERE a.dc_id = b.dc_id
AND a.whse_id = b.whse_id
AND a.ord_id = b.ord_id
AND b.sgmt_id = a.sgmt_id
AND b.prod_qty > 0
AND c.seeb_key = d.seeb_key
AND CAST(b.invc_id AS INT) = CAST(c.invc_id AS INT)
AND CAST(b.prod_id AS INT) = CAST(d.prod_id AS INT)
AND a.whse_id = 5
AND b.ckpt_id = 1199625
GROUP BY a.whse_id,a.ord_id,b.prod_id
HAVING CAST(d.ord_qty*d.unit_ship_cse as int) < CAST(SUM(b.prod_qty) AS INT)
ORDER BY a.ord_id,b.prod_id
February 23, 2016 at 4:59 am
The revised only has three columns in the group by, the original had 5.
Hang on. That's not even SQL Server. What database engine are you using?
This site is specifically for Microsoft SQL Server, answers you get here assume that you're using SQL Server. If you're using something else it's probably better to find a site specifically for that tool, you'll get more usable help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2016 at 5:14 am
Hi,
It is relevant to the number of columns in the group by?
Thks for your help,
JC
February 23, 2016 at 5:53 am
What database are you using? That is not SQL Server, and since it's not SQL Server, you're unlikely to get help here (this is a Microsoft SQL Server site).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2016 at 6:03 am
Ok, sorry but the error is common on both platforms and the error should be the same and the way to solve it 🙁
Thank you in advance for your help,
JC
February 23, 2016 at 6:19 am
The use of TRUNC on a suspected numeric column makes me suspect Oracle. The archive: in your FROM clause tells me I have no clue.
The first step is to fix your GROUP BY clause to include your non-aggregated columns. After that, the syntax is all yours.
I don't know if the non-SARGable predicates will cause a performance problem on your platform or not.
February 23, 2016 at 6:24 am
jcachado (2/23/2016)
Ok, sorry but the error is common on both platforms and the error should be the same and the way to solve it 🙁Thank you in advance for your help,
JC
We don't know what you mean by 'both'.
But if one of the platforms you are referring to is SQL Server, please post the T-SQL version of the query here.
Otherwise, I would recommend that you find a different forum which can help you with the specifics of your particular DBMS. They are not all the same.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 23, 2016 at 6:46 am
Agreed with all previous comments on getting the right tech forum for help. That said the error is fairly descriptive about the issue. You are comparing a non-aggregate to an aggregate: since the HAVING clause executes after the aggregation has occurred, the non-aggregated value is going to cause a problem.
You likely will have to create a subquery to run the aggregate and somehow compare the detail to the total.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 23, 2016 at 7:34 am
The error seems to be the CAST() functions. Use TRUNC() as you did on your column list.
For SQL Server, you can't use UNIQUE or TRUNC(), for Oracle your can't use CAST().
The languages of Oracle (PL/SQL) and SQL Server (T-SQL) are different and won't be interchangeable most of the times.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply