Skip to Content
0
Former Member
Oct 08, 2014 at 01:13 PM

SQL group by error

24 Views

Hi,

I have some problem with my sql skill. I would like to make a simple query with Crystal Report XI. but I have some problem with group by and/or order by clause. I use some CASE fields and that is the root of the problem . (btw. I connect to an oracle database. )

When I make a group by (with group by expert menu) then the SQL will be broken, and I get this error: "Failed to retrieve data from the database......"

This is a short part of my query:

SELECT ....some fields....

CASE

WHEN SUBSTR("OPERATION_HISTORY\"."ORDER_NO",1,1)='N' and TO_NUMBER(SUBSTR("OPERATION_HISTORY\"."ORDER_NO",-2,2))>=60 THEN 'ModeA'

WHEN SUBSTR("OPERATION_HISTORY\"."ORDER_NO",1,1)='N' and TO_NUMBER(SUBSTR("OPERATION_HISTORY\"."ORDER_NO",-2,2))<60 THEN 'ModeB'

WHEN "OPERATION_HISTORY\"."PART_NO"='6-8003' THEN 'ModeC'

ELSE 'ModeD'

END as "D.I.R.T.",

....more fields....

FROM ....tables......

WHERE .....some contitions.....

ORDER BY CASE

WHEN SUBSTR("OPERATION_HISTORY\"."ORDER_NO",1,1)='N' and TO_NUMBER(SUBSTR("OPERATION_HISTORY\"."ORDER_NO",-2,2))>=60 THEN 'ModeA'

WHEN SUBSTR("OPERATION_HISTORY\"."ORDER_NO",1,1)='N' and TO_NUMBER(SUBSTR("OPERATION_HISTORY\"."ORDER_NO",-2,2))<60 THEN 'ModeB'

WHEN "OPERATION_HISTORY\"."PART_NO"='6-8003-000000001-00-00' THEN 'ModeC'

ELSE 'ModeD'

END as "D.I.R.T."

I think the end of this sql is not validate. If I change that to order by "D.I.R.T" and I run it in PL/SQL then it will be ok, but I can't to do it in crystal reports, because I can't change the SQL in direct.....

Does anybody have an idea what I should to do?

thx.

pal.lazar

ps.: sorry for my bad english