asked on

Why this doesnt work?

Hi,

I tried this in the JSP:

sql = "Select A.AssetNo, A.Currency, A.BuyPrice, A.SellPrice, A.NavPrice from tblUTDISPLIST A "+
       "where A.id = "+
       "("+
       "select max(B.ID) from tblUTDISPLIST B where B.assetno = A.assetno "+
       ")";
ps2 = conn.prepareStatement(sql);
rs2 = ps2.executeQuery();

It returns the error: java.sql.SQLException: Invalid column name

But why when i querying it via SQLPlus there is not error occurs? What's wrong with my code?

Also can comment at:
https://www.experts-exchange.com/questions/20796284/Urgent-How-to-create-this-query.html

jimmack

sql = "Select A.AssetNo, A.Currency, A.BuyPrice, A.SellPrice, A.NavPrice from tblUTDISPLIST A "+

This line is missing and "AS" keyword:

sql = "Select A.AssetNo, A.Currency, A.BuyPrice, A.SellPrice, A.NavPrice from tblUTDISPLIST AS A "+

jimmack

Oops. So is this one:

>> "select max(B.ID) from tblUTDISPLIST B where B.assetno = A.assetno "+

"select max(B.ID) from tblUTDISPLIST AS B where B.assetno = A.assetno "+

Ryan Chong

ASKER

sql = "Select A.AssetNo, A.Currency, A.BuyPrice, A.SellPrice, A.NavPrice from tblUTDISPLIST As A "+
                          "where A.id = "+
                          "("+
                          "select max(B.ID) from tblUTDISPLIST As B where B.assetno = A.assetno "+
                          ")";

i got: ORA-00933: SQL command not properly ended ..

jimmack

Hmm. On MySQL, I get a syntax error with the "select max..." line. I don't think MySQL can handle the nested select.

Other than that, I can't see anything else wrong with this (though I'm no SQL expert!).

You can type this line in at a prompt and it works OK?

Ryan Chong

ASKER

Problem resolved, actually there is nothing wrong with the SQL statement above! But it's something typo error on other parts. My bad... I must be very stressful recently doing multiple applications..

FYI, above is connect to an Oracle database, not MySQL

Sorry about that, will ask a question at CS to delete this question. Thanks for trying to help, jimmack, cheers

jimmack

;-)

For my information, does that SQL line work with out "As" keywords?

Ryan Chong

ASKER

Unfortunately, no luck in Oracle. I got an error when try add "As" in the query in sqlplus, that's why i remove the "As" :-)

Are you know PL/SQL well, jimmack? Also can take a look on my other question. Damn! i need solve it asap.

Oh.. if no objection, i will post a request at CS to delete this question, is this fine with you?

cheers

jimmack

I'm afraid my knowledge of SQL is fairly limited. I have recently had a similar problem to yours, where a complex query was involved, but I solved it programmatically.

If you want to delete this question, I don't have any problems with that ;-)

kennethxu

>>Also can take a look on my other question. Damn! i need solve it asap
rogaut1 answered your question accurately, according to those jpg images you have posted.

If you have other problem, please give more detail.

Ryan Chong

ASKER

You're right kennethxu, rogaut1 did give me great idea i miss out there, definitely will award pts to him, wil finalise and close the opening questions after the application is deployed. cheers

jarasa

Hi Jimmack.

>>For my information, does that SQL line work with out "As" keywords?

For your information. On Oracle Does not work the "AS" KeyWord it has to be without it.

Best regards
Javier

jimmack

Thanx Javier ;-)

Ryan Chong

ASKER

Hi guys, time to close this question.

Suggestion: Delete this question

ASKER CERTIFIED SOLUTION

Computer101

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial