Usage of DECODE or CASE in queries

[ScottXJ]

Hi,

I'm trying to determine the best way in which to replace an old query. The tables and fields that were used in the old query no longer exist due to database re-design. It has been suggested to me by a more senior developer, that I use DECODE or CASE. The old query is as follows:

SELECT targ_id, sel_id, sel_name, 'EML' AS "SELECTOR_TYPE", date_added, date_modified, date_deleted
FROM tbl_EMAIL
UNION
SELECT sel_id, sel_name, 'NET' AS "SELECTOR_TYPE",
date_added, date_modified, date_deleted
FROM tbl_NETWK
UNION
SELECT sel_id, sel_name, 'NUM' AS "SELECTOR_TYPE",
date_added, date_modified, date_deleted
FROM tbl_NUMBER

As I mentioned, the database has been re-designed making several of the tables obselete. As well, the date_added, date_deleted and date_modified fields have been replaced with a LOG table which records the action and the date of the associated action. The SELECTOR_TYPE has been replaced with a GENERAL_TYPES table that stores an GENERAL_TYPE_ID and GENERAL_TYPE (description) of the ID. My attempt at re-writing the query is as follows:

SELECT SEL.SEL_ID, SEL.SEL_NAME,SEL.GENERAL_TYPE_ID,
GENERAL_TYPES.GENERAL_TYPE,SEL_LOG.SEL_ID, SEL_LOG.DATES,
SEL_LOG.ACTION

FROM SEL, GENERAL_TYPES, SEL_LOG

WHERE SEL.GENERAL_TYPE_ID = GENERAL_TYPES.GENERAL_TYPE_ID
AND SEL.SEL_ID = SEL_LOG.SEL_ID
AND SEL.GENERAL_TYPE_ID IN (1,2,3)

I used the IN function with the numbers 1 thru 3 because they correspond with the values EMAIL, NETWORK and NUMBER in the GENERAL_TYPES table. My query does seem to work fine, in the sense that it brings back the expected data. The senior developer suggested to me that the IN function is not a good idea as the values related to the numbers I have used may change. This is where the subject of using DECODE or CASE came up. I'm kind of familiar with how to do use DECODE or CASE, I'm just not sure how to apply it to my situation. The values in the GENERAL_TYPES table is as follows:

GENERAL_TYPE_ID GENERAL_TYPE
1 EMAIL
2 NETWORK
3 NUMBER

Any suggestions about how to proceed would be appreciated.

Sorry for the long post!

Scott.

 

[SantaMufasa]

Scott,

I'm not sure what your senior developer is proposing. Is s/he suggesting that there may be a good business reason for changing the GENERAL_TYPE_IDs that correspond to their respective descriptions? (Why would anyone do that?) Is s/he suggesting that the GENERAL_TYPE contents will always stay the same? What does s/he want you to do with a DECODE/CASE construct?

We'll be happy to suggest code if we know what your senior developer is proposing to change about your query.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[ScottXJ]

Mufasa,

Thanks for the quick reply. I will try and answer your questions the best I can. The senior developer mentioned to me that she did not want any hardcoded values in my queries as the GENERAL_TYPE_IDs and the corresponding GENERAL_TYPES may change due to client requests (e.g. GENERAL_TYPE_ID of 1 may reference GENERAL_TYPE of CELLULAR in the future).

With respect to the DECODE/CASE construct, I have to admit that I'm not so clear as to how the construct(s) will assist. I was thinking that perhaps I could do something similar to this example:

SELECT SEL.SEL_ID, SEL.SEL_NAME,
(CASE SEL.GENERAL_TYPE_ID
WHEN 1 THEN 'EMAIL'
WHEN 2 THEN 'NETWORK'
WHEN 3 THEN 'NUMBER'
END)GENERAL_TYPES.GENERAL_TYPE, SEL_LOG.SEL_ID,
SEL_LOG.DATES, SEL_LOG.ACTION

FROM SEL, GENERAL_TYPES, SEL_LOG

WHERE SEL.GENERAL_TYPE_ID = GENERAL_TYPES.GENERAL_TYPE_ID
AND SEL.SEL_ID = SEL_LOG.SEL_ID

I'm not sure if this example makes sense, but I will try to find out some more reasoning as to what exactly the senior dev wants to see.

Thanks,

Scott

 

[SantaMufasa]

Scott,

The puzzling thing is that if 1='EMAIL' now, but 1='CELLULAR' in the future, then how does the above code, whether using CASE or DECODE, help your organization?

Implementing such a change in meaning, it seems to me, throws your data integrity into upheaval. Let's pretend that on January 1, 2007, the meaning of "1" changes from 'EMAIL' to 'CELLULAR'. Does that mean that the 10,000 rows for which 1='EMAIL' must now change to some other value to retain "meaning integrity"?

And regardless of such a change's effect upon data integrity, why would you want to invite automatic and mandatory maintenance to all of your SQL code in which you have hardcoded the descriptive meanings:

...WHEN 1 THEN 'EMAIL'
WHEN 2 THEN 'NETWORK'
WHEN 3 THEN 'NUMBER'

.

IMHO, just because a "senior developer" is proposing such a scheme and justification, doesn't mean that it is an appropriate scheme or an appropriate justification.

Without further information and rationale to justify this particular implementation of CASE/DECODE, I would not advocate its/their use.

In any case, your code is nearly usable -- to get the code to work, you would need, at the very least, a comma to follow your CASE statement:

SELECT SEL.SEL_ID, SEL.SEL_NAME,
(CASE SEL.GENERAL_TYPE_ID
WHEN 1 THEN 'EMAIL'
WHEN 2 THEN 'NETWORK'
WHEN 3 THEN 'NUMBER'
END)[b],[/b] <-- comma needed here
GENERAL_TYPES.GENERAL_TYPE, SEL_LOG.SEL_ID,
SEL_LOG.DATES, SEL_LOG.ACTION
FROM SEL, GENERAL_TYPES, SEL_LOG
WHERE SEL.GENERAL_TYPE_ID = GENERAL_TYPES.GENERAL_TYPE_ID
AND SEL.SEL_ID = SEL_LOG.SEL_ID;

Let us know your reactions/thoughts.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Turkbear]

Hi,
If both those values may change then that table is useless for any lookup or Decoding or Case use..In fact I cannot see any use for it at all.....








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Turkbear, I guess "Great thinks mind alike," huh?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Turkbear]

Hi,
And apparently type at the same time as well...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Thargy]

scott,

I feel obliged to contribute my three hap'orth too.

Your "senior" developer is committing a cardinal sin, i.e. treating metadata as data, and v.v.

Over a decade ago, I learned the hard way to never ever do this. The magic numbers by which referential integrity is maintained, has always been, and should always be absolutely nothing to do with data. Please learn from my mistake, and don't do it.

By insisting on coding agains PK values, your senior (who really ought to know better) is guaranteeing that a change in metadata will break code.

After labouring for over a calendar month to find a "bug" I swore never to use metadata (and/or pk values) as data again. From that day to this, I never have.

As others far more knowledgable than I have already said in previous posts, this is not the way to proceed.

(Descends from soapbox, draws deep breath...)

Sorry to be such a purist, but I've already been bitten on the behind by this one.

Regards

Tharg

Grinding away at things Oracular