Oracle Data Validation 1

[TheLazyPig]

Hi!

I have a table (sampletbl) that contains all values for dropdown parameter(p_dtlval).

Required Validations:
1) If the dropdown is blank, all dtl_val except PAMI OFW will only appear
2) If chose PAMI OFW, all dtl_val plus PAMI OFW will appear
3) If not blank nor PAMI OFW is chosen, that specific dtl_val will only appear

Here is my code below...

SELECT * FROM sampletbl WHERE dtl_val NOT IN ('PAMI OFW')
UNION ALL
SELECT * FROM sampletbl WHERE dtl_val = DECODE([b]p_dtlval[/b],NULL,NULL,[b]p_dtlval[/b]);

The only validation that works in my query above is 1 & 2. When it comes to choosing a specific dtl_val I get all of the values. Please help!


Thank you!

 

[carp]

I think this should get you within striking distance of what you want:

   SELECT * FROM sampletbl
    WHERE dtl_val = CASE 
                       WHEN p_dtlval IS NULL THEN dtl_val
                       WHEN p_dtlval = 'PAMI OFW' THEN dtl_val
                       WHEN p_dtlval IS NOT NULL AND l_val != 'PAMI OFW' THEN p_dtlval
                    END
       AND dtl_val != (DECODE(p_dtlval,NULL,'PAMI OFW','dummy'));

 

[TheLazyPig]

Thank you this works well!

I want to add something... there's a word PAMI in the database, the user said it was the same as PAMI OFW that's why they want me to filter it too. Account name like PAMI OFW or PAMI.

 

[carp]

If I understand the requirement change correctly, this should take care of it. It makes PAMI act just like PAMI OFW, but leaves room for other "PAMI" values to act differently if desired.

SELECT * FROM sampletbl
    WHERE dtl_val = CASE 
                       WHEN p_dtlval IS NULL THEN dtl_val
                       WHEN p_dtlval IN ('PAMI','PAMI OFW') THEN dtl_val
                       WHEN p_dtlval IS NOT NULL AND l_val NOT IN ('PAMI','PAMI OFW') THEN p_dtlval
                    END
       AND dtl_val != (DECODE(p_dtlval,NULL,'PAMI OFW','dummy','PAMI','dummy'));

 

[TheLazyPig]

The requirement did change it has now 2 dropdowns.
1) To include PAMI OFW/PAMI
2) all dtl_val (optional)

Validations:
1) If the 1&2-dropdown is empty, all dtl_val except PAMI OFW will only appear
2) If chose PAMI OFW from 1-dropdown & 2-dropdown is empty, all dtl_val plus PAMI OFW will appear
3) If 2-dropdown is not empty, that specific dtl_val will only appear

I still get PAMI from the code. So I change PAMI to PAMI OFW by using DECODE in the main select then add your code to where clause in a subquery. But to get only PAMI OFW (2nd dropdown) values doesn't work with the current code.