Decode in Group BY

[JACKJPR]

Hi

I am new to INFORMIX....Not to Database..I am Working with Oracle for a long time.

I am Using a decode statement & Count in my select...So i have to use a group by function in my SQL.

If i use the Decode in my group by then i am getting an error

"Syntax Error Has Occured-201"

Any Idea????...


Thanks
Jack

 

[PHV]

Any chance you could post your SQL code ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[vpshriyan]

Hi Jack,

I think decode() is not supported in the group clause of the SQL SELECT statement in Informix. A workaround would be to use it in the select list and use the place-holder number as the group to aggragate on. Example:

SELECT DECODE(column_name,'first pattern','other pattern'),
COUNT(*)
FROM table_name WHERE where_clause
GROUP BY 1

Regards,
Shriyan

 

[JACKJPR]

Here is the Sample Query

SELECT
replace(TABLE A.test_num,'^',' '),
Decode(TABLE A.in_service,'Y','IN-SERVICE','N','NOT IN-SERVICE'),
TABLE B.testtype,
TABLE A.aim_count,
count(TABLE A.test_id)
FROM
TABLE A,
TABLE B,
TABLE C,
TABLE D
WHERE
( TABLE A.test_type=TABLE B.test_type )
AND ( TABLE C.test_id=TABLE D.test_id )
AND ( TABLE A.test_mon=TABLE D.test_mon )
AND (
( ( TABLE C.test_name ) LIKE 'South'
)
GROUP BY
replace(TABLE A.test_num,'^',' '),
Decode(TABLE A.in_service,'Y','IN-SERVICE','N','NOT IN-SERVICE'),
TABLE B.testtype,
TABLE A.aim_count

 

[PHV]

Have you tried this ?
SELECT
replace(TABLE A.test_num,'^',' '),
Decode(TABLE A.in_service,'Y','IN-SERVICE','N','NOT IN-SERVICE'),
TABLE B.testtype,
TABLE A.aim_count,
count(TABLE A.test_id)
FROM
TABLE A,
TABLE B,
TABLE C,
TABLE D
WHERE
( TABLE A.test_type=TABLE B.test_type )
AND ( TABLE C.test_id=TABLE D.test_id )
AND ( TABLE A.test_mon=TABLE D.test_mon )
AND ( TABLE C.test_name LIKE 'South' )
GROUP BY 1,2,3,4

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884