Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing record selection to SQL 1

Status
Not open for further replies.

Johnsf

MIS
Apr 5, 2004
45
NZ
Hi,

Why is Crystal not passing the below record selection formula to SQL under "Show SQL Query"? I want all the OR's to be processed on the server...

(
{TPTY_A_NEW.APTYNAM} <> {TPTY_A_OLD.APTYNAM} or
{TPTY_A_NEW.APTYABB} <> {TPTY_A_OLD.APTYABB} or
{TPTY_A_NEW.APTYDSC} <> {TPTY_A_OLD.APTYDSC} or
{TPTY_A_NEW.APTYSTA} <> {TPTY_A_OLD.APTYSTA} or
{TPTY_A_NEW.AACCINIEMPCDE} <> {TPTY_A_OLD.AACCINIEMPCDE} or
{TPTY_A_NEW.AACCMGREMPCDE} <> {TPTY_A_OLD.AACCMGREMPCDE} or
{TPTY_A_NEW.ACLTIND} <> {TPTY_A_OLD.ACLTIND} or
{TPTY_A_NEW.AOURAGTIND} <> {TPTY_A_OLD.AOURAGTIND} or
{TPTY_A_NEW.ASTKBRKIND} <> {TPTY_A_OLD.ASTKBRKIND} or
{TPTY_A_NEW.AAGTIND} <> {TPTY_A_OLD.AAGTIND} or
{TPTY_A_NEW.AISSIND} <> {TPTY_A_OLD.AISSIND}
)

Thanks,

John
 
Alot of it depends on the driver that your using.
Database/Version and Crystal-Version would help others respond better.

If thats the only code in the record selection formulae, then it should pass it. When it comes to OR's, CR will pass the whole lot or nothing.
See if you can get it to pass just one OR statement. If It cannot it may be the db driver.
If it does... then see where it stops passing by adding part of the selection formulae at a time.

Try a different driver, ODBC/Native/OLEDB etc.

Fred
 
Can you write a view on the server to gather the data. A SQL view or procedure is a lot more powerfull than Crysatl.

Make sure you give select rights to the view, or exec rights to the procedure!
 
Ummm Ive got Crystal Enterprise 8.5 and the driver is SQLORA32.DLL 9.02.00.00 by Oracle Corporation.
 
Your selection formula is essentially joins, which crystal 8.5 only sends from the linking window. It also won't allow you to have *both* an = and a != join between the same tables at the same time from the SQL window.. (it makes them all = or all != as you may have discovered).

Basically,

a) write a view
b) write a SP
c) upgrade (I found the upgrade from 8.5 to 9 very painful..)

Lisa
 
I think what you mean to say is that you're using a Crystal Report 8.5 in a CE 8.5 environment, and you are using the Oracle supplied ODBC driver for Oracle 9.

Switch to the Crystal native connectivity for the best performance, but if you're inclined to use ODBC, use the ODBC for Oracle supplied by crystal.

As for passing this to the database, your syntax seems fine, and if this is the only thing in the record selection then I would think that it would pass using native connectivity or the proper ODBC.

Using the Oracle supplied ODBC in Crystal can also return decimal places incorrectly, drops rows, etc.

-k
 
Oh ok thanks... Ill write a View. Is the driver Im using a native driver? If not how do I install one?

Thanks,

John
 
Id like to switch to Crystal native connectivity... but I dont know how?

Thanks,

John
 
This is the entire record selection formula Im using now:

{TPTY_A_OLD.AOPRTTYP} = "repold" and
{TPTY_A_NEW.AOPRTTYP} = "repnew" and
{TPTY_A_OLD.AAUDDTETME} > DateTime (2004, 06, 01, 00, 00, 00) and
{TPTY_A_NEW.AAUDDTETME} > DateTime (2004, 06, 01, 00, 00, 00) and
(
{TPTY_A_NEW.APTYNAM} <> {TPTY_A_OLD.APTYNAM} or
{TPTY_A_NEW.APTYABB} <> {TPTY_A_OLD.APTYABB} or
{TPTY_A_NEW.APTYDSC} <> {TPTY_A_OLD.APTYDSC} or
{TPTY_A_NEW.APTYSTA} <> {TPTY_A_OLD.APTYSTA} or
{TPTY_A_NEW.AACCINIEMPCDE} <> {TPTY_A_OLD.AACCINIEMPCDE} or
{TPTY_A_NEW.AACCMGREMPCDE} <> {TPTY_A_OLD.AACCMGREMPCDE} or
{TPTY_A_NEW.ACLTIND} <> {TPTY_A_OLD.ACLTIND} or
{TPTY_A_NEW.AOURAGTIND} <> {TPTY_A_OLD.AOURAGTIND} or
{TPTY_A_NEW.ASTKBRKIND} <> {TPTY_A_OLD.ASTKBRKIND} or
{TPTY_A_NEW.AAGTIND} <> {TPTY_A_OLD.AAGTIND} or
{TPTY_A_NEW.AISSIND} <> {TPTY_A_OLD.AISSIND}
)
 
I found out how to use the native drivers and tried it with them but still no luck passing all the ORs to SQL :-|
 
I am using the native drivers and tested on 8.5 to make sure I remembered correctly. Basically, you can't do it from crystal, it won't send it. You have to do a view etc..

Lisa
 
Please post all relevent information, only showing part of it initially wasted everyone's time.

And why not post the Database->Show SQL Query here as well to provide thorough information?

Is anything passing?

Rather than describing problems, try to provide what you've done and what the result is.

Try removing everything but the OR clauses, does that work?

Crystal has a nasty habit of omitting everything after a failure point in the record selection formula.

Since you're comparing tables, not user values or hardcoded values, I'm not sure how CR will handle this.

BTW, the whole criteria is very odd to me, it appears that you're attempting to use 2 tables to discover which has rows that are not in the other, which would usually be accomplished using some date field...

-k
-k
 
Yep the basic criteria is passing as below:

SELECT
"TPTY_A_OLD"."AAUDDTETME", "TPTY_A_OLD"."AOPRTTYP", "TPTY_A_OLD"."APTYABB", "TPTY_A_OLD"."APTYNAM", "TPTY_A_OLD"."APTYDSC", "TPTY_A_OLD"."APTYSTA", "TPTY_A_OLD"."AACCINIEMPCDE", "TPTY_A_OLD"."AACCMGREMPCDE", "TPTY_A_OLD"."AOURAGTIND", "TPTY_A_OLD"."AISSIND", "TPTY_A_OLD"."ASTKBRKIND", "TPTY_A_OLD"."AAGTIND", "TPTY_A_OLD"."ACLTIND", "TPTY_A_NEW"."AAUDDTETME", "TPTY_A_NEW"."AAUDUSRCDE", "TPTY_A_NEW"."AOPRTTYP", "TPTY_A_NEW"."APTYID", "TPTY_A_NEW"."APTYABB", "TPTY_A_NEW"."APTYNAM", "TPTY_A_NEW"."APTYDSC", "TPTY_A_NEW"."APTYSTA", "TPTY_A_NEW"."AACCINIEMPCDE", "TPTY_A_NEW"."AACCMGREMPCDE", "TPTY_A_NEW"."AOURAGTIND", "TPTY_A_NEW"."AISSIND", "TPTY_A_NEW"."ASTKBRKIND", "TPTY_A_NEW"."AAGTIND", "TPTY_A_NEW"."ACLTIND"
FROM
"NOVA"."TPTY_A" "TPTY_A_OLD",
"NOVA"."TPTY_A" "TPTY_A_NEW"
WHERE
"TPTY_A_OLD"."ATRNID2" = "TPTY_A_NEW"."ATRNID2" AND
"TPTY_A_OLD"."AOPRTTYP" = 'repold' AND
"TPTY_A_NEW"."AOPRTTYP" = 'repnew' AND
"TPTY_A_OLD"."AAUDDTETME" > TO_DATE ('01-06-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
"TPTY_A_NEW"."AAUDDTETME" > TO_DATE ('01-06-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

Im accessing an Audit table that tracks updates to another table as "repold" and "repnew" for each record that is updated. I need to compare the old record to the new record and display the fields that have been changed.

Now the above SQL works fine to do that along with Crystal where I can compare but getting those ORs executed on the server will make the report run a lot faster.
 
Looks like Crystal is not passing the OR statement when the comparison is with a field. IF you compare to a constant (or maybe a expresion) it will pass it.

A work around is to use SQL expressions in 8.5. I beleive in 9/10 they have been replaced with the 'add command' function.

Anyway, with sql expression, get the db server to return a 1 or 0 if its true, then use the sql expression in the 'where clause'. That way the code will be passed to the server. Its a bit longer to write, but it may be worth your while.

e.g.
{sqlAPTYNAM}
case when {TPTY_A_NEW.APTYNAM} <> {TPTY_A_OLD.APTYNAM} then 1 else 0 end

repeat for every field you want to compare.

Just check the syntax for the case when else end as I've tested it on sql Server 2k.

In the select expert...
{TPTY_A_OLD.AOPRTTYP} = "repold" and
{TPTY_A_NEW.AOPRTTYP} = "repnew" and
{TPTY_A_OLD.AAUDDTETME} > DateTime (2004, 06, 01, 00, 00, 00) and
{TPTY_A_NEW.AAUDDTETME} > DateTime (2004, 06, 01, 00, 00, 00) and
({sqlAPTYNAM}=1 or
{sqlAPTYABB}=1 or
etc
)

Fred
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top