Using "And/Or" in Selection Criteria

[ChipF]

Hello, I can't get this to work, I want it show apps like G2, G3 and/or apps like G4 with a loan amt over 250K

{DLY_APPLICATION.OPR_LOCATION_NR} in ["010623", "010633"] and
{HE_ACTIVITY_FACT.ACTIVITY_DT} = currentdate-1 and
{HE_ACTIVITY_FACT.ACTIVITY_CD} = "AP" and
{DLY_APPLICATION.RISK_RATING_CD} like ["G2*", "G3*"] or
({DLY_APPLICATION.RISK_RATING_CD} like ["G4*"] and
{DLY_APPLICATION.LOAN_AM > 250000})

 

[tektipdjango]

I'm not sure the syntax around the Like is good, but I haven't CR at the moment.
Also there maybe a problem with the date comparison
{HE_ACTIVITY_FACT.ACTIVITY_DT} = currentdate-1 as maybe there's a pb around the time part of the date.
You are doing a comparisonlike 03/04/2004 11:23:23 with 03/03/2004 07:03:22, clearly there not equal

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[bdreed35]

Do the first 3 lines need to apply to bot apps like G2, G3 and/or apps like G4 with a loan amt over 250K? If so, I think you need some more parens in there like this:

{DLY_APPLICATION.OPR_LOCATION_NR} in ["010623", "010633"] and
{HE_ACTIVITY_FACT.ACTIVITY_DT} = currentdate-1 and
{HE_ACTIVITY_FACT.ACTIVITY_CD} = "AP" and
({DLY_APPLICATION.RISK_RATING_CD} like ["G2*", "G3*"] or 
({DLY_APPLICATION.RISK_RATING_CD} like ["G4*"] and 
{DLY_APPLICATION.LOAN_AM > 250000}))

~Brian

 

[ChipF]

I want the report to show all APd apps with G2, G3 and all APd apps with a G4 over 250K

 

[Turkbear]

Hi, As has been noted, its all in the parens..

{DLY_APPLICATION.OPR_LOCATION_NR} in  ["010623", "010633"]  and
{HE_ACTIVITY_FACT.ACTIVITY_DT} = currentdate-1
and
{HE_ACTIVITY_FACT.ACTIVITY_CD} = "AP"
// All Of the above must be true and
// at least 1 of the 2 below must be true.
// Will always return G2 and G3 and will
// conditionally return G4 IF LOAN_AMT > 250000
and
(
   {DLY_APPLICATION.RISK_RATING_CD} like ["G2*", "G3*"]
   or 
   ({DLY_APPLICATION.RISK_RATING_CD} like ["G4*"] and 
     {DLY_APPLICATION.LOAN_AM > 250000})
)

If No Rating_CD start with G2 or G3 and no G4 is > 250000, NO Records will be returned..Is that the behavior you wanted?

I am not sure about you LIKE syntax, but, as far as the And/Or part this should work.

 

[synapsevampire]

I tend to use parens throughout to clearly delineate, plus it helps the Crystal parse it and pass SQL:
(
{DLY_APPLICATION.OPR_LOCATION_NR} in ["010623", "010633"]
)
and
(
{HE_ACTIVITY_FACT.ACTIVITY_DT} = currentdate-1
)
and
(
{HE_ACTIVITY_FACT.ACTIVITY_CD} = "AP"
)
and
(
{DLY_APPLICATION.RISK_RATING_CD} like ["G2*", "G3*"]
or
(
{DLY_APPLICATION.RISK_RATING_CD} like ["G4*"] and
{DLY_APPLICATION.LOAN_AM > 250000}
)
)

-k