Home » SQL & PL/SQL » SQL & PL/SQL » Complex Case Expression (9i)
Complex Case Expression [message #308399] Mon, 24 March 2008 03:54 Go to next message
jhedshi
Messages: 13
Registered: February 2008
Junior Member
Please guide if the syntax of this is right. Can the CASE expression be used this way ? or it there any other better way to accomplish this ? the execution of this query just hangs up.. there are maximum 150 rows which meet up my criteria in the case expression.

SELECT
DATE_FORMAT(b.dt,'%M-%Y') DATE,
SUM(CASE WHEN e.p= 'Pink' AND c.cat = 'PG' THEN
b.h ELSE 0 END) AS CUS
SUM(CASE WHEN e.p = 'Blue' AND c.cat = 'AG' THEN
b.h ELSE 0 END) AS SUP,
SUM(CASE WHEN e.p= 'Na' AND c.cat = 'PR' THEN
b.h ELSE 0 END) AS NCUS,
FROM bi b , res r , cat c , er e
WHERE r.loc = 'P'
AND b.res = r.res
AND b.cat = c.cat
GROUP BY DATE_FORMAT(b.dt,'%M-%Y')
ORDER BY DATE_FORMAT(b_dt,'%Y%m');


im using this sql in the MYSQL query browser thats some syntax might be different. please help ..
Regards

Re: Complex Case Expression [message #308403 is a reply to message #308399] Mon, 24 March 2008 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is correct in Oracle except that DATE_FORMAT is not an Oracle function.

Also, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Complex Case Expression [message #308570 is a reply to message #308399] Mon, 24 March 2008 23:11 Go to previous messageGo to next message
jhedshi
Messages: 13
Registered: February 2008
Junior Member
hi michel,

this query takes alteast half an hour to execute. i want to reduce the execution time. please help. can we use some kind of analytic functions here or any other approach to solve this.

please let me know if any information required from my side

thanks and regards
Re: Complex Case Expression [message #308582 is a reply to message #308570] Tue, 25 March 2008 00:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is nothing to do in the expression of your query (but using TO_CHAR Oracle function instead of your DATE_FORMAT).
Analytics are useless here, it is not their purpose.

Now check your execution plan, indexes, statistics and so on.
Read How to Identify Performance Problem and Bottleneck and OraFAQ Oracle SQL Tuning Guide.

Regards
Michel
Re: Complex Case Expression [message #308585 is a reply to message #308399] Tue, 25 March 2008 00:09 Go to previous message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
jhedshi wrote on Mon, 24 March 2008 01:54
im using this sql in the MYSQL query browser thats some syntax might be different. please help ..
Regards




The SQL syntax for MYSQL and Oracle can be drastically different, as well as other things. This forum is for Oracle SQL and PL/SQL only, not MySQL. You need to post your problem on a MySQL forum, not here.


Previous Topic: dblink between 10g and 9i
Next Topic: Subquery in where clauses
Goto Forum:
  


Current Time: Mon Dec 05 14:39:42 CST 2016

Total time taken to generate the page: 0.11931 seconds