CODEWITHSUNDEEP
sqloracle-database
fuko
fuko

Reputation: 37

filter parameters is not working properly

I'm able to fetch the data only for the first filter 'SEL' but when we enter 'BUY' or 'All' then data is not coming.

If i run the condition one by one for each filter then it is working. But in the AND OR loop it is not working as expected.

I'm passing same value to all the 3 parameters. so one filter should work at a time and fetch the results as per the passed value

AND
      (
          ('&psRate_type') = 'SEL' AND  PCP.charge_calculation_method = 'R' AND RPH.RP_RECORD_ID = PCP.CHARGE_RATEPROF_RECORD_ID
      OR

           ('&psRate_type') = 'BUY' AND  PCP.cost_calculation_method = 'R' AND RPH.RP_RECORD_ID = PCP.COST_RATEPROF_RECORD_ID
      OR

           ('&psRate_type') = 'All' AND 
                                         (RPH.RATE_TYPE = 'SEL' AND PCP.charge_calculation_method = 'R' AND RPH.RP_RECORD_ID = PCP.COST_RATEPROF_RECORD_ID)  
                                         OR
                                         ((RPH.RATE_TYPE = 'BUY') AND PCP.cost_calculation_method = 'R' AND RPH.RP_RECORD_ID = PCP.COST_RATEPROF_RECORD_ID)
      )

Upvotes: 0

Views: 236

Answers (3)

Kobi
Kobi

Reputation: 2524

When you use OR and AND, you should look at priorities of evaluations.

So, use parenthesis to be sure to evaluate the whole conditions everytime.

AND
      (    ('&psRate_type' = 'SEL' AND  PCP.charge_calculation_method = 'R' AND RPH.RP_RECORD_ID = PCP.CHARGE_RATEPROF_RECORD_ID)
        OR ('&psRate_type' = 'BUY' AND  PCP.cost_calculation_method   = 'R' AND RPH.RP_RECORD_ID = PCP.COST_RATEPROF_RECORD_ID  )
        OR ('&psRate_type' = 'All' AND  (   (RPH.RATE_TYPE = 'SEL' AND PCP.charge_calculation_method = 'R' AND RPH.RP_RECORD_ID = PCP.COST_RATEPROF_RECORD_ID)
                                         OR (RPH.RATE_TYPE = 'BUY' AND PCP.cost_calculation_method   = 'R' AND RPH.RP_RECORD_ID = PCP.COST_RATEPROF_RECORD_ID)))
      )

Upvotes: 0

sorineatza
sorineatza

Reputation: 106

If you put first OR with BUY, second OR with SEL etc. it is working?

Upvotes: 1

Halko Karr-Sajtarevic
Halko Karr-Sajtarevic

Reputation: 2268

You have messed with the ANDs and ORs. As I can see, you wanted to separate your 3 filters. Try this instead:

AND
  (
      (
        ('&psRate_type') = 'SEL' AND  PCP.charge_calculation_method = 'R' AND RPH.RP_RECORD_ID = PCP.CHARGE_RATEPROF_RECORD_ID
      )
  OR
      (
        ('&psRate_type') = 'BUY' AND  PCP.cost_calculation_method = 'R' AND RPH.RP_RECORD_ID = PCP.COST_RATEPROF_RECORD_ID
      )
  OR
      (
        ('&psRate_type') = 'All' AND 
                                     (RPH.RATE_TYPE = 'SEL' AND PCP.charge_calculation_method = 'R' AND RPH.RP_RECORD_ID = PCP.COST_RATEPROF_RECORD_ID)  
                                     OR
                                     (RPH.RATE_TYPE = 'BUY' AND PCP.cost_calculation_method = 'R' AND RPH.RP_RECORD_ID = PCP.COST_RATEPROF_RECORD_ID)
      )  
  )

Upvotes: 1

Related Questions