parenthesis help

[fmrock]

Is there any trick to getting the parenthesis in your select expert match in the SQL that crystal generates.

I am using this in my select expert, but the SQL that crystal comes up the parens are way off and not using my left join.

(({%Fee_Effective} < datadate) AND ((isnull({%Fee_Expiration})) OR ({%Fee_Expiration} > datadate))) OR ((isnull({MEDMNGCARE_FEE.MNGPRCODE})) OR ({MEDMNGCARE_FEE.MNGPRCODE}=""))

 

[lbass]

Is {MEDMNGCARE_FEE.MNGPRCODE} referenced in the SQL expressions? Once it is referenced you can no longer test it for null (i.e., any null values must be tested first).

Also, try entering your record selection formula in report->selection formula->record. If you enter it there, it is more likely to appear as you wish in the SQL query. In the select expert, there is an attempt to optimize the query, and clauses can end up rearranged.

-LB

 

[fmrock]

{MEDMNGCARE_FEE.MNGPRCODE} is just a field in the database and I am left joining to that table.

I also use different statements based on a parameter.

(
If UCASE({?CPT Status})= "FUTURE" Then
    ({%Fee_Effective} > datadate) AND ((isnull({%Fee_Expiration})) OR ({%Fee_Expiration} > datadate))
Else If UCASE({?CPT Status})= "ACTIVE" Then

(({%Fee_Effective} < datadate) AND ((isnull({%Fee_Expiration})) OR ({%Fee_Expiration} > datadate))) OR ((isnull({MEDMNGCARE_FEE.MNGPRCODE})) OR ({MEDMNGCARE_FEE.MNGPRCODE}=""))

Else If UCASE({?CPT Status})= "ALL" Then
    true
Else If UCASE({?CPT Status})= "NONE" Then
    true
) and

The SQL crystal creates always has this in the where clause so that it forces it to bring back rows even when it should not.

 

[lbass]

Please show the content of each SQL expression.

Please identify whether the first query you show above is your actual returned SQL query (show SQl query) and whether the second is your actual record selection formula.

-LB

 

[fmrock]

Fee_Effective

to_date("MEDMNGCARE_FEE"."MNGEFFECTIVE",'j')

Fee_Expiration

to_date("MEDMNGCARE_FEE"."MNGEXPIRATION",'j')

The where clause Crystal is writing is not allowing the left join to work.

 WHERE  
 
 to_date("MEDINSCONTRACT"."CEFFECTIVE",'j')<TO_DATE ('13-03-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
  AND (to_date("MEDINSCONTRACT"."CTERMINATION",'j') IS  NULL  OR to_date("MEDINSCONTRACT"."CTERMINATION",'j')>=TO_DATE ('14-03-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) 
   AND (to_date("MEDMNGCARE_FEE"."MNGEFFECTIVE",'j')<TO_DATE ('13-03-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND (to_date("MEDMNGCARE_FEE"."MNGEXPIRATION",'j') IS  NULL  OR to_date("MEDMNGCARE_FEE"."MNGEXPIRATION",'j')>=TO_DATE ('14-03-2012 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) OR "MEDMNGCARE_FEE"."MNGPRCODE" IS  NULL  OR "MEDMNGCARE_FEE"."MNGPRCODE"='')

 

[lbass]

You cannot add selection criteria on any fields in left-joined table and still maintain the left join. You either have to address the selection criteria by using conditional formulas in the body of your report, or you can use a command as your datasource for the report, and add the selection criteria in the FROM clause instead of in the WHERE clause. If you want to try that, please post your entire SQL Query.

-LB