Stop SQL Expression from being verified?

[RustyAfro]

Is there way to force Crystal to NOT verify the SQL in a SQL Expression. I have found that when a SQL Expression references two or more disparate tables it takes a VERY long time to verify. I know the SQL is right so I don't want Crystal to verify it.

Thanks for any help,

Joel

 

[RustyAfro]

LBASS answered this before:thread766-1423980 suggesting to wrap the expression with Parenthesis.

Whether I do or not CR still tries verify the expression. I have to end task after an hour or two. I even turned off "Compile SQL Expressions" with no luck.

(Rank() Over (Partition by "BKB437_REV_CD_COV"."MED_CTRL_DIMSN_KEY" Order By "CPT300_SVC_DIMSN"."CPT_CD"))

 

[IanWaterman]

Have you tried going to
File->Report

Make sure following are unchecked
Verify Stored Procedures on first refresh
Verify on first refresh
Display alerts on first refresh

Might solve your problem

There are similar settings in
File->Options
Changing here would mean all new reports have same checks turned off automatically

Ian

 

[RustyAfro]

I found a solution to the problem. It was as I suspected. CR was definitely not passing the SQL Expression to the server as part of SELECT statement of the main query.

I had to change the SQL Expression as follows to make it parse inside the SELECT of the main. The solution seemed to be

1. Not using aliases inside the subquery.
2. Wrapping double quotes "" around the table.fieldnames of the fields referenced in the main query.

The reason I think the double quotes are necessary is because the main query does have those tables aliased without the "BKB" part. This website:

http://www.cogniza.com/blog/?p=26

Helped with understanding when double quotes are necessary.

It should be noted though that wrapping the c.fieldname in double quotes (where c is the alias as in the original query above) did not work. I had to remove all aliases from the subquery to get it to parse.

(SELECT        
       bkb.uwvw_cpt300_rowid.Rank
  FROM bkb.uwvw_cpt300_rowid
 WHERE      
       bkb.uwvw_cpt300_rowid.med_ctrl_dimsn_key = "BKB437_REV_CD_COV"."MED_CTRL_DIMSN_KEY"
   and bkb.uwvw_cpt300_rowid.cpt_svc_dimsn_key = "BKB437_REV_CD_COV"."CPT_SVC_DIMSN_KEY"
   and bkb.uwvw_cpt300_rowid.rank = 1
)

 

[RustyAfro]

Oops, this was the answer to this thread767-1430337

Still cannot get this to work.

I did find this site though:

http://www.cogniza.com/blog/?p=26

It talks about the Invalid Column error. Some people responded with the same problem as I. However, nothing seemed to work to get CR to pass the SQL Expression with the main query.