Write an SQL expression using SELECT and USER()

[cdm1221]

Hi all!

I'm using a very large Oracle DB, and want to create a report using multiple tables from this DB. However, whenever I try to create an SQL expression using SELECT, FROM, WHERE, etc. it gives me the following error:

"ODBC error: [MERANT][ODBC Oracle 8 Driver][Oracle 8]ORA-00911: invalid character"

and the syntax error occurs right after the word "select".

Even simpler, if I try to add the basic SQL Expression: {fn USER()}. The result/user will not display if more than one table is added to the report.

Essentially, I want to grab a specific value from a record of one table that is selected based on a record from another table. Then use that value to add/subtract from other values in different tables.

Any suggestions??

I'm in the process of looking into UDFs; however, even if I get a working UDF, how can I call it from a SQL expression if my SQL expression doesn't recognize SELECT statements?

 

[lyanch]

SQL expressions are unfortunately not what one would think of as a SQL expression. Think of them more as simple functions, convert, add, subtract, cast etc. I don't think there is a way that you can do this in 8.5 or earlier without writing a stored procedure. You can, however, probably do this in ADD COMMAND in Version 9.

Lisa

 

[cdm1221]

So, is there any point in writing the UDF? How can I use it?

 

[lyanch]

I have never worked with UDF's within SQL expressions so I can't answer that question. My gut instinct tells me it wouldn't work.

Lisa

 

[cdm1221]

Thanks lyanch. Are there other ways of working with a UDF? Or could you elaborate on stored procedures?

 

[rhinok]

You can use Select|From|Where in a SQL Expression, you just need to encapsulate it in parentheses. Most people don't know this, heck, I didn't know it and I wrote an FAQ on SQL Expressions about a year ago (wich reminds me, I need to update it)! This basically gives you the ability to create a correlated subquery:


(
SELECT
table.field
FROM
dbo.table
WHERE
table.field = 'X'
)

 

[cdm1221]

Thanks rhinok, but parentheses or no parentheses I still get the same error. I'm using CR 8.5. Does this effect anything? Someone tried to tell me that the formatting or functions are different if using an Oracle db. Is that right?

BTW, here's my simple expression:
(select
_PERSONNEL_."CODE" from _PERSONNEL_ where _PERSONNEL_."CODE"={fn USER()})

 

[cdm1221]

Why does the USER() function not work when two tables from the same database are used (linked or not) to display fields?

 

[synapsevampire]

Is this USER a function, or are you simply trying to return the user name?

You should be able to just use USER (not {fn USER}) in a SQL Expression if the intent is to return the user name.

Since User is a reserved word in Oracle, which may be giving the ODBC driver trouble, don't use it unless you intend to return the User name.

Here's the whitepaper:

http://support.businessobjects.com/library/kbase/articles/c2010985.asp

-k

 

[cdm1221]

That worked but doesn't solve my problem.

All I want to do is this:

(select a.x from x where a.y =
(select b.m from b where b.n = USER))

I have multiple tables all from the same database being used in the report. I was told i could use a SQL expression. It doesn't work. I'm confused because USER returns the username if only fields from one table are being displayed in the report. However, if I try to display multiple fields from different tables, it neither displays the fields from any table or the username.

 

[synapsevampire]

If you don't get any rows from the tables, you won't get the USER.

If you're just displaying the USER once, just toss in a subreport with a simple select that includes the USER in a SQL Expression. Now it will always be returned.

I don't have Oracle set up right now, otherwise I'd test the USER in a SQL Expression, but the above is the solution, why bother trying to return USER for every row, it's always going to be the same value anyway.

-k

 

[synapsevampire]

If you don't get any rows from the tables, you won't get the USER.

If you're just displaying the USER once, just toss in a subreport with a simple select that includes the USER in a SQL Expression. Now it will always be returned.

I don't have Oracle set up right now, otherwise I'd test the USER in a SQL Expression, but the above is the solution, why bother trying to return USER for every row, it's always going to be the same value anyway.

(I'd try something like "select user from dual" in a SQL Expression, or something like that, haven't played with Oracle for a few months)

-k

 

[cdm1221]

In the example I posted, I am returning a row (the row in table A where the field A.X equals a value from table B). Am I not thinking syntactically correct? The value from table B is dependent on the username (USER). It doesn't matter if I can see, but the fact that it is not being displayed when it once was leads me to believe that it is now null and thereby not returning the value I want.

 

[synapsevampire]

Sorry, I see your dilema now.

The best solution is to create a View which has the subquery, and then use your record selection formula against the View.

From within Crystal this is problematic.

Another solution would be to use an ADO connection and paste SQL in.

I've read of people using a SQL Expression to handle subqueries, but I always have access to the database and won't consider embedding SQL into the report itself for maintenance and reusability reasons.

-k

 

[cdm1221]

Well explain something else for me please...
If I have two unlinked tables, shouldn't I be able to do a record selection on one without it affecting the other. So still get an entire column of table A and one row of table B.

I plan to use one value from table B to adjust the values in the column of table A.

I'm trying to prevent prompting for parameters and use values in other tables of the database.

 

[cdm1221]

I'm very unfamiliar with Views. Can you elaborate?

 

[lyanch]

Synapse is partial to views, I like stored procedures, it really depends on which works best in your situation. I had a whitepaper detailing the syntax required for Oracle stored procedures from Crystal but I can't seem to find it on their site today. You may have better luck.

Lisa

 

[synapsevampire]

Lisa: I'm not partial to Views. I use SP's often, but only as required, and the SP's only reference Views to provide a simplified maintenance layer against database changes.

I think that you've confused my suggesting that developers not have access to tables directly to being partial to Views, I'm just in favor of them over tables, especially in dynamic environments.

cdm1221: A view is basically a Select statement, though they allow for more advanced functions and subqueries.

They're created and stored on the database, check with your dba.

-k