Home » SQL & PL/SQL » SQL & PL/SQL » Help with GROUP BY in subquery (Oracle Database 10g Enterprise Edition 64-bit RAC version 10.2.0.3.0)
Help with GROUP BY in subquery [message #350816] Fri, 26 September 2008 08:58 Go to next message
jmarendo
Messages: 5
Registered: September 2008
Junior Member
Hello,

I have a query that works fine in MySQL but not in Oracle:

SELECT sc_hst_str, id_opr, ts_tm_srt, dc_dy_bsn
FROM
    (SELECT sc_hst_str, sh.id_opr, ts_tm_srt, dc_dy_bsn
        FROM le_hst_str sh, ca_dy_bsn bd, ca_prd_rp rp
        WHERE id_str_rt = '00320' AND sh.fy = rp.fy
            AND sh.ty_pr_prd = rp.ty_pr_prd
            AND sh.id_pr_prd = rp.id_pr_prd
            AND rp.fy = bd.fy
            AND rp.fw_nmb = bd.fw_nmb
            AND rp.wd_dy_fsc = bd.wd_dy_fsc) as result
WHERE dc_dy_bsn = MAX(dc_dy_bsn)
GROUP BY bd.dc_dy_bsn;


When I attempt to run this in Oracle, I get an 'ORA-00933: SQL command not properly ended' error with a SQLState of 42000. So far it seems that Oracle might be complaining about the "as" (in the "as result" portion of the sub-query) along with how I'm trying to use the aggregate function, MAX, and the GROUP BY clause.

If one cannot tell, the sub-query is composed of inner joins between 3 tables that returns multiple rows. What I'm trying to do is to select one row from the results based upon the maximum value of dc_dy_bsn, which is a field representing a date (yyyy-mm-dd format) stored as a VARCHAR in the ca_dy_bsn table.

Any ideas how I can make what I have work in Oracle? Or does anyone have suggestions for trying to achieve the same thing in another way? The SQL needs to be able to work for both MySQL and Oracle because it is embedded in some Java code for an application that needs to communicate to either database depending on the environment in which it is being executed.

Any help will be greatly appreciated. Thank you!

Jeff
Re: Help with GROUP BY in subquery [message #350825 is a reply to message #350816] Fri, 26 September 2008 09:58 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Oracle syntax doesn't accept "as" in from clause to assign a table alias. So to solve this specific issue just don't use that keyword in the from clause.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2126863


Once you did it, you'll have a problem about the max() in where clause. It isn't supported there and not in that way.

One way to rewrite it may be the following.

Not tested

SELECT sc_hst_str, id_opr, ts_tm_srt, dc_dy_bsn
FROM (
		SELECT sc_hst_str, sh.id_opr, ts_tm_srt, dc_dy_bsn,
			MAX(dc_dy_bsn) over() as MAX_dc_dy_bsn
		FROM le_hst_str sh, 
			ca_dy_bsn bd, 
			ca_prd_rp rp
		WHERE id_str_rt = '00320' AND sh.fy = rp.fy
			AND sh.ty_pr_prd = rp.ty_pr_prd
			AND sh.id_pr_prd = rp.id_pr_prd
			AND rp.fy = bd.fy
			AND rp.fw_nmb = bd.fw_nmb
			AND rp.wd_dy_fsc = bd.wd_dy_fsc
	) result
WHERE dc_dy_bsn = MAX_dc_dy_bsn
GROUP BY bd.dc_dy_bsn


Bye Alessandro

[Updated on: Fri, 26 September 2008 10:00]

Report message to a moderator

Re: Help with GROUP BY in subquery [message #351422 is a reply to message #350825] Tue, 30 September 2008 14:50 Go to previous messageGo to next message
jmarendo
Messages: 5
Registered: September 2008
Junior Member
Thank you for the earlier reply. I've tried working through this and am having major difficulties--mainly due to my lack of SQL knowledge I suppose. In any event, what I'm trying to do is straightforward.

That is, this code:

SELECT sc_hst_str, sh.id_opr, ts_tm_srt, dc_dy_bsn
FROM le_hst_str sh, ca_dy_bsn bd, ca_prd_rp rp
WHERE id_str_rt = '00320' AND sh.fy = rp.fy
  AND sh.ty_pr_prd = rp.ty_pr_prd
  AND sh.id_pr_prd = rp.id_pr_prd
  AND rp.fy = bd.fy
  AND rp.fw_nmb = bd.fw_nmb
  AND rp.wd_dy_fsc = bd.wd_dy_fsc


returns a result set like so:

sc_hst_str  id_opr  ts_tm_srt              dc_dy_bsn
2           104700  2008-09-22 11:27:17.0  2008-09-11
2           104700  2008-09-22 12:45:56.0  2008-09-12


How can I get all of the data for the one row that has the greatest value for dc_dy_bsn (that is, 2008-09-12) from the preliminary results?

This gets me the maximum value, but I need the rest of the data from the one row to which the maximum corresponds.

SELECT max(dc_dy_bsn)
FROM
    (SELECT sc_hst_str, sh.id_opr, ts_tm_srt, dc_dy_bsn
        FROM le_hst_str sh, ca_dy_bsn bd, ca_prd_rp rp
        WHERE id_str_rt = '00320' AND sh.fy = rp.fy
            AND sh.ty_pr_prd = rp.ty_pr_prd
            AND sh.id_pr_prd = rp.id_pr_prd
            AND rp.fy = bd.fy
            AND rp.fw_nmb = bd.fw_nmb
            AND rp.wd_dy_fsc = bd.wd_dy_fsc) result;


I wouldn't think this to be so difficult. Any ideas?

Jeff
Re: Help with GROUP BY in subquery [message #351491 is a reply to message #351422] Wed, 01 October 2008 02:09 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
The greatest function may do that job.

Anyway remember that, for this kind of questions, a search on google may give you a quick answer.

If you don't believe click here.

Bye Alessandro
Re: Help with GROUP BY in subquery [message #351508 is a reply to message #351422] Wed, 01 October 2008 03:55 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link and see if this is of any help to you.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions056.htm#SQLRF00641

Regards

Raj
Re: Help with GROUP BY in subquery [message #351562 is a reply to message #350816] Wed, 01 October 2008 09:37 Go to previous messageGo to next message
jmarendo
Messages: 5
Registered: September 2008
Junior Member
Thank you to those who've replied so far. This strikes me as a straightforward problem, but I can't get over how difficult it is to get what I want to work in both MySQL and Oracle.

Again, I'm not just trying to get the MAX value for the dc_dy_bsn field--that I can do. What I need is data for each field in the row that has the maximum or greatest value for dc_dy_bsn returned from the subquery. Also, the SQL needs to work in both MySQL and Oracle.

Here's some stuff that I've tried with notes about success, failure or other considerations.

1. This works in Oracle but not in MySQL ("ROWNUM" is not recognized):

SELECT *
FROM
    (SELECT sc_hst_str, sh.id_opr, ts_tm_srt, dc_dy_bsn
        FROM le_hst_str sh, ca_dy_bsn bd, ca_prd_rp rp
        WHERE id_str_rt = '00320' AND sh.fy = rp.fy
            AND sh.ty_pr_prd = rp.ty_pr_prd
            AND sh.id_pr_prd = rp.id_pr_prd
            AND rp.fy = bd.fy
            AND rp.fw_nmb = bd.fw_nmb
            AND rp.wd_dy_fsc = bd.wd_dy_fsc
        ORDER BY dc_dy_bsn DESC) result
WHERE ROWNUM < 2;


2. This works in MySQL but not in Oracle ("LIMIT" is not recognized):

SELECT *
FROM
    (SELECT sc_hst_str, sh.id_opr, ts_tm_srt, dc_dy_bsn
        FROM le_hst_str sh, ca_dy_bsn bd, ca_prd_rp rp
        WHERE id_str_rt = '00320' AND sh.fy = rp.fy
            AND sh.ty_pr_prd = rp.ty_pr_prd
            AND sh.id_pr_prd = rp.id_pr_prd
            AND rp.fy = bd.fy
            AND rp.fw_nmb = bd.fw_nmb
            AND rp.wd_dy_fsc = bd.wd_dy_fsc) result
ORDER BY result.dc_dy_bsn DESC LIMIT 0,1;


3. This works in both MySQL and Oracle but is a problem because there's an entry in the ca_dy_bsn table (probably for application convenience, I imagine) that has a ridiculous value of "2207-02-09" for dc_dy_bsn so no rows are returned.

SELECT sc_hst_str, sh.id_opr, ts_tm_srt, dc_dy_bsn
FROM le_hst_str sh, ca_dy_bsn bd, ca_prd_rp rp
WHERE id_str_rt = '00320' AND sh.fy = rp.fy
    AND sh.ty_pr_prd = rp.ty_pr_prd
    AND sh.id_pr_prd = rp.id_pr_prd
    AND rp.fy = bd.fy
    AND rp.fw_nmb = bd.fw_nmb
    AND rp.wd_dy_fsc = bd.wd_dy_fsc
    AND dc_dy_bsn = (SELECT MAX(dc_dy_bsn) FROM ca_dy_bsn);


Any more ideas?

Jeff
Re: Help with GROUP BY in subquery [message #351582 is a reply to message #351562] Wed, 01 October 2008 11:29 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You should always use the appropriate tool for the problem rather than going with a generic tool. It won't work out well. So my point is don't try to write a query which works with all the databases. You are asking for trouble and nothing else.

Just a word of advice.

Regards

Raj
Re: Help with GROUP BY in subquery [message #351588 is a reply to message #350816] Wed, 01 October 2008 11:47 Go to previous messageGo to next message
jmarendo
Messages: 5
Registered: September 2008
Junior Member
Thanks again to everyone who took the time to reply and offer their insight--it was definitely helpful.

To answer Raj's latest reply, the SQL has to work for both MySQL and Oracle due to, in part, our environment (that is, we use MySQL in the field for our stores and Oracle in our central office; the schemas are the same across databases) and also because the SQL is embedded in application code written in Java.

In any event, I asked a developer in our area for suggestions, and he suggested to refer to another table in order to get the maximum value of dc_dy_bsn, which will allow me to get all of the data in the corresponding row. Here's the latest SQL that will likely be what I'll use because it fulfills the need (the change is in the final AND operator):

SELECT sc_hst_str, sh.id_opr, ts_tm_srt, dc_dy_bsn
FROM le_hst_str sh, ca_dy_bsn bd, ca_prd_rp rp
WHERE id_str_rt = '00320' AND sh.fy = rp.fy
    AND sh.ty_pr_prd = rp.ty_pr_prd
    AND sh.id_pr_prd = rp.id_pr_prd
    AND rp.fy = bd.fy
    AND rp.fw_nmb = bd.fw_nmb
    AND rp.wd_dy_fsc = bd.wd_dy_fsc
    AND bd.dc_dy_bsn = (SELECT MAX(dc_dy_bsn) FROM tr_str_opn_cl WHERE id_str_rt = '00320' AND ty_trn = '6');


Jeff
Re: Help with GROUP BY in subquery [message #351685 is a reply to message #351588] Thu, 02 October 2008 08:01 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
jmarendo wrote on Wed, 01 October 2008 18:47
the SQL has to work for both MySQL and Oracle due to, in part, our environment (that is, we use MySQL in the field for our stores and Oracle in our central office; the schemas are the same across databases) and also because the SQL is embedded in application code written in Java.


If you buy Oracle, and if you pay it (a lot, probably too much), you must use it with all the available features to to give a sense to your investment. If you don't use all its features you wasted your money.

SQL is a standard language, but any DBMS has its own SQL dialect with its own added value.

What you pay, when you buy Oracle or any commercial RDBMS, is mostly that added value. And if you buy Oracle to do the same things you were doing on MySQL and in the same way, you're making a bad choice.

Remember that generally, in the development of a portable SQL application, there are different implementations of the same procedure developed to take advantages of many feature of the hosting database.
In Java you can do it using different classes implementing the same interface and in the RDBMS you can use views.


Coming back to the solution of your problem: you can opt for this code that fetches only the first row of the ordered query.
String query  = 
		"SELECT sc_hst_str, sh.id_opr, ts_tm_srt, dc_dy_bsn "+
		"FROM le_hst_str sh, ca_dy_bsn bd, ca_prd_rp rp "+
		"WHERE id_str_rt = '00320' AND sh.fy = rp.fy "+
		"	AND sh.ty_pr_prd = rp.ty_pr_prd "+
		"	AND sh.id_pr_prd = rp.id_pr_prd "+
		"	AND rp.fy = bd.fy "+
		"	AND rp.fw_nmb = bd.fw_nmb "+
		"	AND rp.wd_dy_fsc = bd.wd_dy_fsc "+
		"ORDER BY dc_dy_bsn DESC";

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);
if (resultSet.next()) /* use if instead of using while */ {
	// Process the first row
}


Bye Alessandro

[Updated on: Thu, 02 October 2008 08:07]

Report message to a moderator

Re: Help with GROUP BY in subquery [message #351706 is a reply to message #350816] Thu, 02 October 2008 10:27 Go to previous messageGo to next message
jmarendo
Messages: 5
Registered: September 2008
Junior Member
Hello,

Thank you, Alessandro, for the reply and info. I understand the justification behind the reasoning of your message. However, I didn't design (nor have input) our application architecture and environment so the code needs to run against both MySQL and Oracle. Period. To try to explain more would be to lay out the entire structure of our environment, which would get us way off track. Smile

The example for getting the very first row in the ResultSet is good, but the problem right now is that the SQL should have been written to return a single row instead of multiple. In our production environment, it's returning 500+, which is not desirable. The change I'm trying to make is to help make the query more efficient--at least, that's how I understand it.

I just found out this morning that the latest modification to the query, which does work well, won't work in the case where we have a new store (no data is being returned b/c there's nothing in the tr_str_opn_cl table for the store) so you can imagine my enthusiasm for the wonderful field of information technology at this point...

Jeff
Re: Help with GROUP BY in subquery [message #351803 is a reply to message #351706] Fri, 03 October 2008 04:47 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Now I don't remember if MySQL gives the opportunity to create views but I think so and you can use this opportunity to create different environments on the hosting databases that interface with your java code in the same way.

So if you're in Oracle you create a view with the query using Oracle SQL and if you're in MySQL you create a different view but with the same name using MySQL SQL. Obviously the output of both queries should be the same.

This is a thing you must understand. If you want to run your application against different databases, sometime you have to develop the same routine more than once.

Bye Alessandro

Previous Topic: Trigger to call a procedure that loads updated and insert records
Next Topic: SYSDATE Performance
Goto Forum:
  


Current Time: Thu Dec 08 20:28:56 CST 2016

Total time taken to generate the page: 0.29374 seconds