Tricky Dynamic 'Where' but not using Dynamic SQL

Please can someone advise me? Many thanks.

I need to replicate this logic into an Oracle 8i SQL string without using functions or procedures or dynamic SQL.

I have two fields to be used for the selection, both are usually Null but a few records have different values. These are the special cases that the logic is supposed to filter in a slightly more refined way.

Typically:

Field1 # Field2
=======#=======
HEDGE  # I
HEDGE  # E
ACTIV  # I
ACTIV  # E
Null   # Null

The start of the SQL is standard:

SELECT * FROM TABLE WHERE
GROUP BY ACCOUNT_ID;   This is sufficient for all records where Field1 and Field2 are both Null.

However now I also need to append this extra selection criteria when Field1 and Field2 are not Null, thus

If Field1 = ‘HEDG' and Field2 = ‘I' then

Grouped selection is also based on Field1 = ‘HEDG'

ElseIf Field1 = ‘HEDG' and Field2 = ‘E' then

Grouped selection is also based on Field1 <> ‘HEDG' and Field1 Is Null

ElseIf Field1 = ‘ACTIV' and Field2 = ‘I' then

Grouped selection is also based on Field1 Is Null

ElseIf Fields1 = ‘ACTIV' and Field2 = ‘E' then

Grouped selection is also based on Field1 Is Not Null

EndIf

I have two ways of doing this, both of which give the wrong results:

1/

   (Decode(Field2, ‘I', Decode(Field1, ‘HEDG', Field1)) = ‘HEDG')

OR (Decode(Field2, ‘E', Decode(Field1, ‘HEDG', Field1)) <> ‘HEDG'

    OR Decode(Field2, ‘E', Decode(Field1, ‘HEDG', Field1)) Is Null)

OR (Decode(Field2, ‘I', Decode(Field1, ‘ACTIV', Field1)) Is Null)

OR (Decode(Field2, ‘E', Decode(Field1, ‘ACTIV', Field1)) Is Not Null)

2/

   ((NOT(Field2 = ‘I' AND Field1 = ‘HEDG')) OR Field1 = ‘HEDG')

OR ((NOT(Field2 = ‘E' AND Field1 = ‘HEDG')) OR Field1 <> ‘HEDG' OR Field1 Is Null)

OR ((NOT(Field2 = ‘I' AND Field1 = ‘HEDG')) OR Field1 Is Null)

OR ((NOT(Field2 = ‘E' AND Field1 = ‘ACTIV')) OR Field1 Is Not Null)

If anyone has any ideas as to how to emulate dynamic SQL using WHERE and logical operations to add extra selection criteria based upon the values in certain fields I'd be very grateful.

Many thanks – CJB. Received on Wed May 26 2004 - 13:01:02 CDT