BorlandTalk.com

N · Guest

I have been unable to find a single example on how to utilize the USER() function under SQL Builder, I have an easier way to run my query:
SELECT ORADBA.PS_PROJECT_INFO.PROJECT_ID,ORADBA.PS_PROJECT_INFO.PROJECT_NAME,ORADBA.PS_PROJECT_INFO.PROJECT_DESC
FROM ORADBA.PS_PROJECT_INFO
WHERE ORADBA.PS_PROJECT_INFO.PROJECT_ID = (SELECT ORADBA.PS_PROJECT_EMPLOYEES.PROJECT_ID
FROM ORADBA.PS_PROJECT_EMPLOYEES
WHERE ORADBA.PS_PROJECT_EMPLOYEES.EMPLOYEE_ID = (SELECT ORADBA.PS_EMPLOYEE_LOOKUP.EMPLOYEE_ID FROM ORADBA.PS_EMPLOYEE_LOOKUP WHERE ORADBA.PS_EMPLoYEE_LOOKUP.USERNAME = (SELECT USER from dual)));

But for some reason it will not accept the this query ( i already ran it as a script via command line and it works)... So now I am trying to use the USER() function... Is there any documentation about these functions and how to use them. What I found in the help section was useless?
Thank you for your help in advance..
N

John Moore (TeamB) · Guest

N wrote:

John Moore (TeamB) · Guest

Oh, and as an additional note..

If the driver supports it.. there is a DataSource property "user"..
Consult any JDBC 2.0 book on Datasource properties..

John..

--
=============================================
TeamB are volunteer helpers. Please DO NOT REPLY VIA EMAIL!
Post all questions and replies to this newsgroup ONLY
For papers on DataExpress, Applets, JSP, and Web Development go to:
http://www.microps.com/mps/paperFAQ.html
====================================================

N · Guest

"John Moore (TeamB)" <jbm (AT) microps (DOT) com> wrote:

Lori M Olson [TeamB] · Guest

N wrote:

Nick · Guest

Just an update...
I tried using different JDBC drivers and had no luck in using subqueries with QueryDataSet in JBuilder 2006. I had to rewrite the Subqueries as Joins instead and this seems to work. I am still not sure if it truelly is a JDBC driver issue or a JBuilder issue. I was unable to find any information regarding my particular problem (running subqueries, 3 levels, wouldn't even work for 2 levels). To any one else with similar issues, good luck, and write Joins instead.

Nick

"N" <nmperez (AT) water (DOT) ca.gov> wrote:

John Moore (TeamB) · Guest

Nick wrote:

Nick · Guest

I appreciate everyones help on this topic. Will do on the JDBC book, I am crash course learning JBuilder, so I appreciate the explanations.

thanks again,
Nick

Arthur Ore · Guest

Hi,
I've just experimented with this. I got JB 2006 Ent, the same as Nick. I
used classes12.jar against a 10G Oracle database.

The following SQL (which should work on virtually any Oracle database) runs
happily in PL/SQL :-
select * from dual where dummy in (select dummy from dual)

The same statement also works fine in JB 2006's Database Pilot.

If you create a new datamodule and use the SQL builder to create the
following SQL statement it works.
SELECT * FROM DUAL

Once you've created your QueryDataSet, go into design mode and select your
QueryDataSet from the structure pane.
From the properties window, click on the 3 ellipses next to the Query
property.
Now type in the original SQL with the nested subquery :-
select * from dual where dummy in (select dummy from dual)

Click the test query button and you should get "SUCCESS".

Now click the SQL Builder button and use the same SQL in the builder, this
time you get an error parsing SQL. I think this probably suggests a problem
with the builder rather than JDBC.

I think the moral is that as John says he avoids using the SQL Builder to
construct the SQL. You could test it in the Database Pilot and paste it in
from there.

Hope this helps.
Arth

"Nick" <nmperez (AT) water (DOT) ca.gov> wrote in message
news:45dc7fd2$1 (AT) newsgroups (DOT) borland.com...