SQL question
A user wanted to merge three INSERT...SELECT statements (each scanning capfh0_temp with a different cah0dctyp value and grouping the same way) into one, to avoid three table scans. His attempt wrapped aggregates inside CASE expressions keyed on the non-grouped column cah0dctyp, which failed with error -294 (column must be in the GROUP BY list); he also noted COUNT(CASE...) wasn't available in his 11.5 server. Suggestions: put the CASE inside SUM() (SUM(CASE WHEN ... THEN 1 ELSE 0 END)), use an inline view that pre-aggregates counts/sums, or add cah0dctyp to the GROUP BY; Jonathan Leffler suggested upgrading to 11.70 or keeping the working three-statement version. No confirmation from the poster that any variant worked is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
HI,
in the batch job, I have below three SQL statement. they can run successful.
1)
EXEC SQL INSERT INTO capfi0_temp SELECT cah0cmng, cah0dpno, cah0type,
0 dbcnt,COUNT(*) crcnt,0.00 dbbal,SUM(cah0bal*cah0pecnt) crbal,
0.00 dbcml,SUM(cah0bal*cah0pecnt) crcml,0.00 ovbal FROM capfh0_temp
WHERE cah0dctyp='0' AND cah0enable = '0'
GROUP BY cah0cmng, cah0dpno, cah0type;
2)
EXEC SQL INSERT INTO capfi0_temp SELECT cah0cmng, cah0dpno, cah0type,
COUNT(*) dbcnt,0 crcnt,SUM(cah0bal*cah0pecnt) dbbal,0.00 crbal,
SUM(cah0bal*cah0pecnt) dbcml,0.00 crcml,0.00 ovbal FROM capfh0_temp
WHERE cah0dctyp='1' AND cah0enable = '0'
GROUP BY cah0cmng, cah0dpno, cah0type;
3) EXEC SQL INSERT INTO capfi0_temp SELECT cah0cmng, cah0dpno, cah0type,
0 dbcnt,0 crcnt,0.00 dbbal,0.00 crbal,0.00 dbcml,0.00 crcml,
SUM(cah0bal*cah0pecnt) ovbal FROM capfh0_temp
WHERE cah0dctyp='2' AND cah0enable = '0'
GROUP BY cah0cmng, cah0dpno, cah0type;
the table capfh0_temp totally will be scanned 3 times.
I want to merge those SQL into a single SQL. Below are the NEW SQL statement.
EXEC SQL INSERT INTO capfi0_temp
SELECT cah0cmng, cah0dpno, cah0type,
case cah0dctyp when '0' then sum(0) when '1' then count(*) when '2' then
sum(0) end case,
case cah0dctyp when '0' then COUNT(*) when '1' then sum(0) when '2' then
sum(0) end case,
case cah0dctyp when '0' then sum(0.00) when '1' then SUM(cah0bal*cah0pecnt)
when '2' then sum(0.00) end case,
case cah0dctyp when '0' then SUM(cah0bal*cah0pecnt) when '1' then sum(0.00)
when '2' then sum(0.00) end case,
case cah0dctyp when '0' then sum(0.00) when '1' then SUM(cah0bal*cah0pecnt)
when '2' then sum(0.00) end case,
case cah0dctyp when '0' then SUM(cah0bal*cah0pecnt) when '1' then sum(0.00)
when '2' then sum(0.00) end case,
case cah0dctyp when '0' then sum(0.00) when '1' then sum(0.00) when '2' then
SUM(cah0bal*cah0pecnt) end case
FROM capfh0_temp
WHERE cah0enable = '0'
GROUP BY cah0cmng, cah0dpno, cah0type;
BUT ids report -294 error.
>finderr 294
-294 The column column-name must be in the GROUP BY list.
In a grouping SELECT, you must list every nonaggregate column in the
GROUP BY clause to ensure that a well-defined value exists for each
selected column in each grouped row. A column contains either a single
aggregate value or a value unique to that group. If a selected column
were neither an aggregate nor in the list, two or more values for that
column might possibly exist in some group, and the database server
could not choose which value to display. Revise the query to include
either the column name or its positional number in the clause.
Do anybody know how to write those 3 SQL statment into a single one? thanks
for your time.
I'd need to check, but at least in the latests versions you could put the
case inside the SUM()...
Can you try it?
On Jul 19, 2012 7:59 AM, "CHUAN LU" <luchuan@cn.ibm.com> wrote:
> HI,
>
> in the batch job, I have below three SQL statement. they can run
> successful.
>
> 1)
>
> EXEC SQL INSERT INTO capfi0_temp SELECT cah0cmng, cah0dpno, cah0type,
>
> 0 dbcnt,COUNT(*) crcnt,0.00 dbbal,SUM(cah0bal*cah0pecnt) crbal,
>
> 0.00 dbcml,SUM(cah0bal*cah0pecnt) crcml,0.00 ovbal FROM capfh0_temp
>
> WHERE cah0dctyp='0' AND cah0enable = '0'
>
> GROUP BY cah0cmng, cah0dpno, cah0type;
>
> 2)
>
> EXEC SQL INSERT INTO capfi0_temp SELECT cah0cmng, cah0dpno, cah0type,
>
> COUNT(*) dbcnt,0 crcnt,SUM(cah0bal*cah0pecnt) dbbal,0.00 crbal,
>
> SUM(cah0bal*cah0pecnt) dbcml,0.00 crcml,0.00 ovbal FROM capfh0_temp
>
> WHERE cah0dctyp='1' AND cah0enable = '0'
>
> GROUP BY cah0cmng, cah0dpno, cah0type;
> 3) EXEC SQL INSERT INTO capfi0_temp SELECT cah0cmng, cah0dpno, cah0type,
>
> 0 dbcnt,0 crcnt,0.00 dbbal,0.00 crbal,0.00 dbcml,0.00 crcml,
>
> SUM(cah0bal*cah0pecnt) ovbal FROM capfh0_temp
>
> WHERE cah0dctyp='2' AND cah0enable = '0'
>
> GROUP BY cah0cmng, cah0dpno, cah0type;
> the table capfh0_temp totally will be scanned 3 times.
> I want to merge those SQL into a single SQL. Below are the NEW SQL
> statement.
>
> EXEC SQL INSERT INTO capfi0_temp
>
> SELECT cah0cmng, cah0dpno, cah0type,>
> case cah0dctyp when '0' then sum(0) when '1' then count(*) when '2' then
> sum(0) end case,
>
> case cah0dctyp when '0' then COUNT(*) when '1' then sum(0) when '2' then
> sum(0) end case,
>
> case cah0dctyp when '0' then sum(0.00) when '1' then SUM(cah0bal*cah0pecnt)
> when '2' then sum(0.00) end case,
>
> case cah0dctyp when '0' then SUM(cah0bal*cah0pecnt) when '1' then sum(0.00)
> when '2' then sum(0.00) end case,
>
> case cah0dctyp when '0' then sum(0.00) when '1' then SUM(cah0bal*cah0pecnt)
> when '2' then sum(0.00) end case,
>
> case cah0dctyp when '0' then SUM(cah0bal*cah0pecnt) when '1' then sum(0.00)
> when '2' then sum(0.00) end case,
>
> case cah0dctyp when '0' then sum(0.00) when '1' then sum(0.00) when '2'
> then
> SUM(cah0bal*cah0pecnt) end case
>
> FROM capfh0_temp
>
> WHERE cah0enable = '0'
>
> GROUP BY cah0cmng, cah0dpno, cah0type;
>
> BUT ids report -294 error.
>
> >finderr 294
> -294 The column column-name must be in the GROUP BY list.
>
> In a grouping SELECT, you must list every nonaggregate column in the
> GROUP BY clause to ensure that a well-defined value exists for each
> selected column in each grouped row. A column contains either a single
> aggregate value or a value unique to that group. If a selected column
> were neither an aggregate nor in the list, two or more values for that
> column might possibly exist in some group, and the database server
> could not choose which value to display. Revise the query to include
> either the column name or its positional number in the clause.
>
> Do anybody know how to write those 3 SQL statment into a single one? thanks
> for your time.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00248c70fddd7e9b9904c52c404e
my IDS version is 11.5. I can't use the count(case ... end case) statement in this version, I know it's new feature in 11.7. So I still don't know how to write it.
Try using SUM(CASE WHEN xxx THEN 1 ELSE 0 END) instead. --EEM >-----Original Message----- >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of >CHUAN LU >Sent: Thursday, July 19, 2012 7:13 AM >To: ids@iiug.org >Subject: Re: SQL question [27685] > >my IDS version is 11.5. I can't use the count(case ... end case) >statement in this version, I know it's new feature in 11.7. So I still >don't know how to write it. > > >************************************************************************ >******* > Forum Note: Use "Reply" to post a response in the discussion forum.
On Thu, Jul 19, 2012 at 5:12 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > my IDS version is 11.5. I can't use the count(case ... end case) statement > in > this version, I know it's new feature in 11.7. So I still don't know how to > write it. > Why can't you upgrade to 11.70 so the feature you would like to use is available? Also, IIRC from your original question, you have a working solution; why mess with what is working? -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d04016b1fd47cbc04c52f7d1f
After looking at your query on a bigger screen, I noticed you're
repeating/mixing fields, counts, and SUM()... Not sure if 11.7 would solve
it
If the base table is "small" you should probably use the working version as
Jonathan suggests. On the other hand, if the base table it too large and
the three scans can hava a real impact you can do it in two steps or use an
inline view...
Something like (needs checking):
INSERT INTO capfi0_temp
SELECT t1.cah0cmng, t1.cah0dpno, t1cah0type,
case t1.cah0dctyp
when '0' then
0
when '1' then
t1.my_count
when '2' then
0
end case,
case t1.cah0dctyp
when '0' then
t1.my_count
when '1' then
0
when '2' then
0
end case,
case t1.cah0dctyp
when '0' then
0.00
when '1' then
t1.my_sum
when '2' then
0.00
end case,
case t1.cah0dctyp
when '0' then
t1.my_sum
when '1' then
0.00
when '2' then
0.00
end case,
case t1.cah0dctyp
when '0' then
0.00
when '1' then
t1.my_sum
when '2' then
0.00
end case,
case t1.cah0dctyp
when '0' then
t1.my_sum
when '1' then
0.00
when '2' then
0.00
end case,
case t1.cah0dctyp
when '0' then
0.00
when '1' then
0.00
when '2' then
t1.my_sum
end case
FROM
(
SELECT
cah0cmng, cah0dpno, cah0type
count(*) my_count,
SUM(cah0bal*cah0pecnt) my_sum
FROM
capfh0_temp
WHERE
cah0enable = '0'
GROUP BY
cah0cmng, cah0dpno, cah0type
) t1
If this doesn't work (I think it should), you could use another temp table
and use two statements. If all this is better than the original only more
analysiz could tell.
Regards.
On Thu, Jul 19, 2012 at 1:12 PM, CHUAN LU <luchuan@cn.ibm.com> wrote:
> my IDS version is 11.5. I can't use the count(case ... end case) statement
> in
> this version, I know it's new feature in 11.7. So I still don't know how to
> write it.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--00235452ea5872789904c53054c7
Hi, It need to do lots of testing for the upgrading. The environment is a production env. I am tuning the application, so I want to reduce the time of table scans. I will try those suggestion today.
I think that this query produce the same result :
EXEC SQL INSERT INTO capfi0_temp
SELECT cah0cmng,
cah0dpno,
cah0type,
sum( case cah0dctyp
when '0' then 0
when '1' then 1
when '2' then 0
end case ),
sum( case cah0dctyp
when '0' then 1
when '1' then 0
when '2' then 0
end case ),
sum( case cah0dctyp
when '0' then 0.00
when '1' then cah0bal*cah0pecnt
when '2' then 0.00
end case ),
sum ( case cah0dctyp
when '0' then cah0bal*cah0pecnt
when '1' then 0.00
when '2' then 0.00
end case ),
sum( case cah0dctyp
when '0' then 0.00
when '1' then cah0bal*cah0pecnt
when '2' then 0.00
end case ),
sum( case cah0dctyp
when '0' then cah0bal*cah0pecnt
when '1' then 0.00
when '2' then 0.00
end case ),
sum( case cah0dctyp
when '0' then 0.00
when '1' then 0.00
when '2' then cah0bal*cah0pecnt
end case )
FROM capfh0_temp
WHERE cah0enable = '0'
GROUP BY cah0cmng, cah0dpno, cah0type;
Please, check it !!! (I'm not sure)
Best Regards
I think this alternative is closer to what you want:
is to add the field cah0dctyp in clause "group by"
EXEC SQL INSERT INTO capfi0_temp
SELECT cah0cmng,
cah0dpno,
cah0type,
cah0dctyp,sum( case cah0dctyp
when '0' then 0
when '1' then 1
when '2' then 0
end case ),
sum( case cah0dctyp
when '0' then 1
when '1' then 0
when '2' then 0
end case ),
sum( case cah0dctyp
when '0' then 0.00
when '1' then cah0bal*cah0pecnt
when '2' then 0.00
end case ),
sum ( case cah0dctyp
when '0' then cah0bal*cah0pecnt
when '1' then 0.00
when '2' then 0.00
end case ),
sum( case cah0dctyp
when '0' then 0.00
when '1' then cah0bal*cah0pecnt
when '2' then 0.00
end case ),
sum( case cah0dctyp
when '0' then cah0bal*cah0pecnt
when '1' then 0.00
when '2' then 0.00
end case ),
sum( case cah0dctyp
when '0' then 0.00
when '1' then 0.00
when '2' then cah0bal*cah0pecnt
end case )
FROM capfh0_temp
WHERE cah0enable = '0'
GROUP BY cah0cmng, cah0dpno, cah0type, cah0dctyp;
Please, check it !!! (I'm not sure)
Best Regards