Link to home
Create AccountLog in
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

Select case issue

Hello all

I have an issue with a SQL where i have this error: ORA-00920: invalid relational operator


SELECT 
    ID,
    STATE,
    TIME_ZONE_ID,
    COUNTRY,
        CASE
        WHEN STATE = 'AB' and  TIME_ZONE_ID <> 'MST' THEN 'State should be MST'
        WHEN STATE = 'BC' and  TIME_ZONE_ID <> 'PST' THEN 'State should be PST'
        WHEN STATE = 'MB' and  TIME_ZONE_ID <> 'CST' THEN 'State should be CST'
        WHEN STATE = 'NB' and  TIME_ZONE_ID <> 'ATL' THEN 'State should be ATL'
        WHEN STATE = 'NL' and  TIME_ZONE_ID <> 'NFL' THEN 'State should be NFL'
        WHEN STATE = 'NT' and  TIME_ZONE_ID <> 'CST' THEN 'State should be CST'
        WHEN STATE = 'ON' and  TIME_ZONE_ID <> 'EST' THEN 'State should be EST'
        WHEN STATE = 'QC' and  TIME_ZONE_ID <> 'EST' THEN 'State should be EST'
        WHEN STATE = 'SK' and  TIME_ZONE_ID <> 'CST' THEN 'State should be CST'
        WHEN STATE = 'YT' and  TIME_ZONE_ID <> 'PST' THEN 'State should be PST'
        ELSE ''
        END  STATE_VALIDATION,
        
        CASE
        WHEN STATE = 'AB' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'BC' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'BC' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'MB' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'NB' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'NL' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'NT' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'ON' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'QC' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'SK' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'YT' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        ELSE ''
        END  COUNTRY_VALIDATION

FROM 
    LOCATION
WHERE 
    STATE_VALIDATION NOT NULL;

Open in new window


I only want to have the result where i have a mismatch from both cases.

How can i fix this?

Thanks again.
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

try this : ( IS is required before NOT NULL in the where clause )

SELECT
    ID,
    STATE,
    TIME_ZONE_ID,
    COUNTRY,
        CASE
        WHEN STATE = 'AB' and  TIME_ZONE_ID <> 'MST' THEN 'State should be MST'
        WHEN STATE = 'BC' and  TIME_ZONE_ID <> 'PST' THEN 'State should be PST'
        WHEN STATE = 'MB' and  TIME_ZONE_ID <> 'CST' THEN 'State should be CST'
        WHEN STATE = 'NB' and  TIME_ZONE_ID <> 'ATL' THEN 'State should be ATL'
        WHEN STATE = 'NL' and  TIME_ZONE_ID <> 'NFL' THEN 'State should be NFL'
        WHEN STATE = 'NT' and  TIME_ZONE_ID <> 'CST' THEN 'State should be CST'
        WHEN STATE = 'ON' and  TIME_ZONE_ID <> 'EST' THEN 'State should be EST'
        WHEN STATE = 'QC' and  TIME_ZONE_ID <> 'EST' THEN 'State should be EST'
        WHEN STATE = 'SK' and  TIME_ZONE_ID <> 'CST' THEN 'State should be CST'
        WHEN STATE = 'YT' and  TIME_ZONE_ID <> 'PST' THEN 'State should be PST'
        ELSE ''
        END  STATE_VALIDATION,
       
        CASE
        WHEN STATE = 'AB' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'BC' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'BC' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'MB' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'NB' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'NL' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'NT' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'ON' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'QC' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'SK' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'YT' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        ELSE ''
        END  COUNTRY_VALIDATION

FROM
    LOCATION
WHERE
    STATE_VALIDATION IS NOT NULL;
Avatar of Wilder1626

ASKER

Hi

Like this, i have an error: ORA-00904: "STATE_VALIDATION": invalid identifier
try this...

SELECT
    ID,
    STATE,
    TIME_ZONE_ID,
    COUNTRY,
        CASE
        WHEN STATE = 'AB' and  TIME_ZONE_ID <> 'MST' THEN 'State should be MST'
        WHEN STATE = 'BC' and  TIME_ZONE_ID <> 'PST' THEN 'State should be PST'
        WHEN STATE = 'MB' and  TIME_ZONE_ID <> 'CST' THEN 'State should be CST'
        WHEN STATE = 'NB' and  TIME_ZONE_ID <> 'ATL' THEN 'State should be ATL'
        WHEN STATE = 'NL' and  TIME_ZONE_ID <> 'NFL' THEN 'State should be NFL'
        WHEN STATE = 'NT' and  TIME_ZONE_ID <> 'CST' THEN 'State should be CST'
        WHEN STATE = 'ON' and  TIME_ZONE_ID <> 'EST' THEN 'State should be EST'
        WHEN STATE = 'QC' and  TIME_ZONE_ID <> 'EST' THEN 'State should be EST'
        WHEN STATE = 'SK' and  TIME_ZONE_ID <> 'CST' THEN 'State should be CST'
        WHEN STATE = 'YT' and  TIME_ZONE_ID <> 'PST' THEN 'State should be PST'
        ELSE ''
        END  STATE_VALIDATION,
        CASE
        WHEN STATE = 'AB' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'BC' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'BC' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'MB' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'NB' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'NL' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'NT' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'ON' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'QC' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'SK' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        WHEN STATE = 'YT' and  COUNTRY <> 'CANADA' THEN 'Country should be CANADA'
        ELSE ''
        END  COUNTRY_VALIDATION
FROM
    LOCATION
WHERE
    STATE_VALIDATION IS NOT NULL;
Sorry but i have the same error: ORA-00904: "STATE_VALIDATION": invalid identifier
i removed the blank lines as well in between the column names in my above query.
i still have the same error: ORA-00904: "STATE_VALIDATION": invalid identifier
ASKER CERTIFIED SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Seems to me that the CASE result string should be delimited with double quotes rather than single ones.
no dvz, the case exression is being aliased with that column name and it is not visible in the where clause and hence the error. so just got that column name outside of the inline view query and should be working for him now.
This is perfect

Thank you so much for your help
good and thanks.