SQL using CASE in SELECT with GROUP BY. Need a CASE value, but get the string value

Question

so basically there is 1 question and 1 problem:

1. question - when I have 100 columns in a table (and no key or uindex is given) and I want to join this table or sub-select it, do I really need to write each column name?

2. problem - the example below shows question 1. and my actual problem with the SQL statement

Example:

A.FIELD1, (SELECT CASE WHEN B.FIELD2 = 1 THEN B.FIELD3 ELSE null FROM TABLE B WHERE A.* = B.*) AS CASEFIELD1 (SELECT CASE WHEN B.FIELD2 = 2 THEN B.FIELD4 ELSE null FROM TABLE B WHERE A.* = B.*) AS CASEFIELD2 FROM TABLE A GROUP BY A.FIELD1 

The story is this: if I do not put CASE in my own select statement, then I have to put the actual rowname in GROUP BY, and GROUP BY does not group the NULL value from CASE, but the actual value from the string. And because of this, I will either have to join, or select all the columns, since there is no key and no uindex, or somehow find another solution.

DBServer is DB2.

So now, to describe it only with words and without SQL: I have “order items” that can be divided into “ZD” and “EK” (1 = ZD, 2 = EK) and can be grouped by a “distributor”. Even if the "order items" can have one of two different "departments" (ZD, EK), the fields / lines for "ZD" and "EK" are always filled. I need a grouping to consider a "department", and only if the changed "department" (ZD or EK) changes, then I want to create a new group.

 SELECT (CASE WHEN TABLE.DEPARTEMENT = 1 THEN TABLE.ZD ELSE null END) AS ZD, (CASE WHEN TABLE.DEPARTEMENT = 2 THEN TABLE.EK ELSE null END) AS EK, TABLE.DISTRIBUTOR, sum(TABLE.SOMETHING) AS SOMETHING, FROM TABLE GROUP BY ZD EK TABLE.DISTRIBUTOR TABLE.DEPARTEMENT 

This worked in SELECT and ZD, EK in GROUP BY. The only problem was that even if the EK was not assigned DEPARTEMENT, it still opened a new group if it changed because it used the real value of EK and not NULL from CASE, as I already explained up.

Answer 1

And here ladies and gentlemen are the solution to the problem:

 SELECT (CASE WHEN TABLE.DEPARTEMENT = 1 THEN TABLE.ZD ELSE null END) AS ZD, (CASE WHEN TABLE.DEPARTEMENT = 2 THEN TABLE.EK ELSE null END) AS EK, TABLE.DISTRIBUTOR, sum(TABLE.SOMETHING) AS SOMETHING, FROM TABLE GROUP BY (CASE WHEN TABLE.DEPARTEMENT = 1 THEN TABLE.ZD ELSE null END), (CASE WHEN TABLE.DEPARTEMENT = 2 THEN TABLE.EK ELSE null END), TABLE.DISTRIBUTOR, TABLE.DEPARTEMENT 

@ t-clausen.dk: Thank you!

@others: ...

Answer 2

Actually there is a wildcard equality test. I'm not sure why you would group by field1, which in your example would seem impossible. I tried to tweak it to your question:

 SELECT FIELD1, CASE WHEN FIELD2 = 1 THEN FIELD3 END AS CASEFIELD1, CASE WHEN FIELD2 = 2 THEN FIELD4 END AS CASEFIELD2 FROM ( SELECT * FROM A INTERSECT SELECT * FROM B ) C UNION -- results in a distinct SELECT A.FIELD1, null, null FROM ( SELECT * FROM A EXCEPT SELECT * FROM B ) C 

This will crash for data types that are not comparable

Answer 3

No, there is no template equality test. You will need to list each field that you want to check individually. If you do not want to test each individual field, you can use a hack such as combining all the fields, for example.

 WHERE (a.foo + a.bar + a.baz) = (b.foo + b.bar + b.az) 

but in any case you list all the fields.

Answer 4

I could decide something like this

 WITH q as (SELECT Department , (CASE WHEN DEPARTEMENT = 1 THEN ZD WHEN DEPARTEMENT = 2 THEN EK ELSE null END) AS GRP , DISTRIBUTOR , SOMETHING FROM mytable ) SELECT Department , Grp , Distributor , sum(SOMETHING) AS SumTHING FROM q GROUP BY DEPARTEMENT , GRP , DISTRIBUTOR 

Answer 5

If you need to find all rows in table A that match TableB, what about INTERSECT or INTERSECT DISTINCT?

 select * from A INTERSECT DISTINCT select * from B 

However, if you only need lines from A, where the entire line corresponds to the values ​​in the line from B, then why does your sample code take some values ​​from A and others from B? If the row matches all columns, this will seem pointless. (Perhaps your question can be explained in more detail?)