开发者

How to eliminate repeating case or decode statement multiple times in SELECT?

开发者 https://www.devze.com 2023-03-28 02:21 出处:网络
For example: SELECT a, IF (c = DECODE(b, \"foo1\", \"bar1\", \"foo2\", \"bar2\") THEN c ELSE 0), IF (d != DECODE(b, \"foo1\", \"ba开发者_运维知识库r1\", \"foo2\", \"bar2\") THEN d ELSE 2),

For example:

SELECT a,
  IF (c = DECODE(b, "foo1", "bar1", "foo2", "bar2") THEN c ELSE 0),
  IF (d != DECODE(b, "foo1", "ba开发者_运维知识库r1", "foo2", "bar2") THEN d ELSE 2),
  IF (e = DECODE(b, "foo1", "bar1", "foo2", "bar2") THEN e ELSE 5)
FROM x

Seems like there should be a less repetitive way to do this...


use

SELECT 
a,
IF (c = b THEN c ELSE 0),
IF (d != b THEN d ELSE 2),
IF (e = b THEN e ELSE 5)
FROM
(SELECT a, DECODE(b, "foo1", "bar1", "foo2", "bar2") b, c, d, e from x);


SELECT a,
  DECODE( c, b_decoded, c, 0 ),
  DECODE( d, b_decoded, 2, d ),
  DECODE( e, b_decoded, e, 5 )
FROM (SELECT a, c, d, e, DECODE(b, "foo1", "bar1", "foo2", "bar2") b_decoded FROM x)
0

精彩评论

暂无评论...
验证码 换一张
取 消