V6 OS/390 Distinct(case gives -127

vcei

V6 OS/390 Distinct(case gives -127
Hello List(en)ers,

DB2 V6 on OS/390

This query returns a -127 sqlcode (more than 1 distinct in a query).

SELECT COL1
FROM TABLEX WHERE COL1 <> ` `
GROUP BY COL1
HAVING COUNT(DISTINCT CASE WHEN COLSTATUS = `NOK` THEN `X` ELSE `O` END) > 1

Very simple question: why ?
I suppose it `s got something to do with how the distinct - case combination
is "rewritten" by the optimizer, but nevertheless...

This one works fine:

SELECT COL1
, COUNT(DISTINCT CASE WHEN COLSTATUS = `NOK` THEN `X` ELSE `O`
END)
FROM TABLEX WHERE COL1 <> ` `
GROUP BY COL1
ORDER BY 2 DESC

PS: I know how to rewrite the first one (using SUM) to get it working, so
that`s not the main problem.

Thanks for your time,
JP



Matthias Pohlig

Re: V6 OS/390 Distinct(case gives -127
(in response to vcei)
Funny...

I tried excactly your statement on a newly created, empty table
(no runstats)
-just changed the apostrophes to my codepage-

Works flawlessly !

Also a V6 OS/390 ...



Kirk Hampton

Re: V6 OS/390 Distinct(case gives -127
(in response to Matthias Pohlig)
Is your "TABLEX" actually a view containing another DISTINCT ?






"VCEI," <[login to unmask email]> on 12/19/2000 03:28:42 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Kirk Hampton/Texas Utilities)
Subject: V6 OS/390 Distinct(case gives -127



Hello List(en)ers,

DB2 V6 on OS/390

This query returns a -127 sqlcode (more than 1 distinct in a query).

SELECT COL1
FROM TABLEX WHERE COL1 <> ` `
GROUP BY COL1
HAVING COUNT(DISTINCT CASE WHEN COLSTATUS = `NOK` THEN `X` ELSE `O` END) > 1

Very simple question: why ?
I suppose it `s got something to do with how the distinct - case combination
is "rewritten" by the optimizer, but nevertheless...

This one works fine:

SELECT COL1
, COUNT(DISTINCT CASE WHEN COLSTATUS = `NOK` THEN `X` ELSE `O`
END)
FROM TABLEX WHERE COL1 <> ` `
GROUP BY COL1
ORDER BY 2 DESC

PS: I know how to rewrite the first one (using SUM) to get it working, so
that`s not the main problem.

Thanks for your time,
JP








vcei

Re: V6 OS/390 Distinct(case gives -127
(in response to Kirk Hampton)
Excellent idea !
But, no it isn`t. It is a basic view on one table (select all columns from
TABLEX).

Still, brilliant idea because, (hadn`t thought of this myself...) when I
replace the view by the table... It works !! At least... It `s working
better.
We`re not home, yet:

<recapitulation>
DOESN`T WORK---(SQLcode -127)---------------:
SELECT COL1
FROM VIEWX WHERE COL1 <> ` `
GROUP BY COL1
HAVING COUNT(DISTINCT CASE WHEN COLSTATUS = `NOK` THEN `X` ELSE `O` END) >
1
(I can only see one DISTINCT, and believe me, I`ve triple checked the view)

DOES WORK-----------------------------------:
SELECT COL1
, COUNT(DISTINCT CASE WHEN COLSTATUS = `NOK` THEN `X` ELSE `O` END)
FROM VIEWX WHERE COL1 <> ` `
GROUP BY COL1
<end recapitulation>

DOES WORK------------from-now-on---table-instead-of-view:
SELECT COL1
FROM TABLEX WHERE COL1 <> ` `
GROUP BY COL1
HAVING COUNT(DISTINCT CASE WHEN COLSTATUS = `NOK` THEN `X` ELSE `O` END) >
1

(that`s nice already)

STILL DOESN`T WORK--------------------------:
SELECT COL1
, COUNT(DISTINCT CASE WHEN COLSTATUS = `NOK` THEN `X` ELSE `O` END)
FROM TABLEX WHERE COL1 <> ` `
GROUP BY COL1
HAVING COUNT(DISTINCT CASE WHEN COLSTATUS = `NOK` THEN `X` ELSE `O` END) >
1

(always gives a "You cannot use DISTINCT more than once in a query")
I would believe what this message says, but:

DOES WORK-----------------------------------:
SELECT COL1
, COUNT(DISTINCT COLSTATUS)
FROM TABLEX WHERE COL1 <> ` `
GROUP BY COL1
HAVING COUNT(DISTINCT COLSTATUS) > 1

Uh... I "used DISTINCT more than once in this query" !

Ever seen anything like this ? :-)

Regards,
JP

PS: thanks to Gail Myers, Nicole Cook and Martin Balz for letting me know
they `re "out of office". Nice to know :-)

Wanna disclaimer ? I am my own employer, so I can`t be held responsible for
myself.


-----Original Message-----
From: Kirk Hampton [mailto:[login to unmask email]
Sent: 19 December 2000 16:58
To: [login to unmask email]
Subject: Re: V6 OS/390 Distinct(case gives -127


Is your "TABLEX" actually a view containing another DISTINCT ?






"VCEI," <[login to unmask email]> on 12/19/2000 03:28:42 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Kirk Hampton/Texas Utilities)
Subject: V6 OS/390 Distinct(case gives -127



Hello List(en)ers,

DB2 V6 on OS/390

This query returns a -127 sqlcode (more than 1 distinct in a query).

SELECT COL1
FROM TABLEX WHERE COL1 <> ` `
GROUP BY COL1
HAVING COUNT(DISTINCT CASE WHEN COLSTATUS = `NOK` THEN `X` ELSE `O` END) > 1

Very simple question: why ?
I suppose it `s got something to do with how the distinct - case combination
is "rewritten" by the optimizer, but nevertheless...

This one works fine:

SELECT COL1
, COUNT(DISTINCT CASE WHEN COLSTATUS = `NOK` THEN `X` ELSE `O`
END)
FROM TABLEX WHERE COL1 <> ` `
GROUP BY COL1
ORDER BY 2 DESC

PS: I know how to rewrite the first one (using SUM) to get it working, so
that`s not the main problem.

Thanks for your time,
JP