Using Left Outer Join and USER to select fields

[cdm1221]

I'm trying to avoid using a stored procedure, but I'm attempting the following SQL query in CR 8.5:

SELECT
A.X
FROM
A, B
WHERE
A.Y = B.Y (+) AND
B.Z=user

However, everytime I enter this into Database-->Show SQL Query, it changes it and returns:

SELECT
A.X
FROM
A
WHERE
AND B.Z=user

Of course, returning a syntax error. Can CR 8.5 and/or SQL not compute the two together, in which case a stored procedure would be pointless as well?

I'm trying to avoid prompting the user for their username and use the DB User ID which is the same.

 

[MColeman]

I might be wrong on this one; I have found that Crystal will tailor your SQL based on what fields are used in the report.

For example, if I changed the SQL query to get another field, and then didn't use the field in the report, Crystal would remove it from the query since it wasn't used.

 

[synapsevampire]

Try creating a SQL Expression for the USER (for whatever database you're using, it's dependent) and adding it to the Record Selection Formula, Crystal should build the SQL for you.

-k