CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Rita
Rita

Reputation: 1237

What is wrong with this query in Oracle

I have below query. But it is not bringing the results how i want.

In case if UPPER(PCK.Keyid) = 'TLMAPICONFIGMGR.USEDB' AND UPPER(PCK.DEFAULTKEYIDVALUE) = 'FALSE' then, it should bring only this record. If PCK.DEFAULTKEYIDVALUE is not False, it should bring all other records back.

Though it is false in the database, it is bringing all records including this one.

How to fix this where Clause?

SELECT
    COALESCE(Cast(PCBS.keyId as VarChar2(255)), cast(PC.Keyid as varchar2(255)), cast(PCK.KeyId as varchar2(255))) as Key,
    COALESCE(Cast(PCBS.KeyIdValue as VarChar2(255)), cast(PC.KeyIdValue as varchar2(255)), cast(PCK.DefaultKeyIdValue as varchar2(255))) as value
FROM
    PodServer_Tab PS
    JOIN PodConfigKey_tab PCK
        ON PCK.KeyId = PCK.KEYID 
    LEFT OUTER JOIN PodConfig_Tab PC 
        ON PS.PodId = PC.PodId
    LEFT JOIN PodConfigByServer_Tab PCBS 
        ON PS.PODID = PCBS.PODID 
        AND PS.ServerName = PCBS.ServerName
        AND PCBS.KeyId = PC.KeyId 
WHERE PS.PodId =  p_podId 
    AND PS.Servername = p_serverName
    AND (
        (UPPER(PCK.Keyid) = 'TLMAPICONFIGMGR.USEDB' AND UPPER(PCK.DEFAULTKEYIDVALUE) = 'FALSE') 
        OR
        (UPPER(PCK.Keyid) != 'TLMAPICONFIGMGR.USEDB')
        );

Upvotes: 0

Views: 66

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

The filter is applied against each row.

AND (
    (UPPER(PCK.Keyid) = 'TLMAPICONFIGMGR.USEDB' AND UPPER(PCK.DEFAULTKEYIDVALUE) = 'FALSE') 
    OR
    (UPPER(PCK.Keyid) != 'TLMAPICONFIGMGR.USEDB')
    );

For every row, UPPER(PCK.Keyid) either does or does not match 'TLMAPICONFIGMGR.USEDB'. Every row that does not match will always be returned, because there is nothing saying it shouldn't be.

The only time anything will be excluded is if UPPER(PCK.DEFAULTKEYIDVALUE) does not equal 'FALSE', in which case only that row will be excluded. It has no effect on rows which don't match the Keyid, only on those that do.

Whatever that default is set to, all other Keyid values will always match.

If you want to only see a single row or everything except that row, then you need to look wider than one row at a time. You could add an exists clause, something like:

AND (
    (UPPER(PCK.Keyid) = 'TLMAPICONFIGMGR.USEDB' AND UPPER(PCK.DEFAULTKEYIDVALUE) = 'FALSE') 
    OR
    (UPPER(PCK.Keyid) != 'TLMAPICONFIGMGR.USEDB'
      AND NOT EXISTS (
        SELECT 1 FROM PodConfigKey_tab PCK2
        WHERE PCK2.KeyId = PCK.KEYID
        AND UPPER(PCK2.Keyid) = 'TLMAPICONFIGMGR.USEDB'
        AND UPPER(PCK2.DEFAULTKEYIDVALUE) = 'FALSE'
      )
    )
    );

You could use a subquery with analytic functions but it you're looking up a single value there may not be much benefit and the correlated self-join won't be too expensive.

I don't think this is relevant to the issue you're describing, but at the moment you have what appears to be a broken join condition too:

JOIN PodConfigKey_tab PCK
    ON PCK.KeyId = PCK.KEYID

should presumably be:

JOIN PodConfigKey_tab PCK
    ON PS.KeyId = PCK.KEYID

otherwise it is a cross-join rather than an inner join; in which case it would be clearer to use:

CROSS JOIN PodConfigKey_tab PCK

(with no ON clause, since a cross join doesn't allow one) to make it clear that is the intention.

Upvotes: 2

davegreen100
davegreen100

Reputation: 2115

 OR
        (UPPER(PCK.Keyid) != 'TLMAPICONFIGMGR.USEDB')

well the above is saying show me everything where keyid != 'TLMAPICONFIGMGR.USEDB', so you will get everything (assuming the servername matches)

Upvotes: 1

Related Questions