CODEWITHSUNDEEP
sqloracle-databasestored-proceduresconditional-statements
Rudziankoŭ
Rudziankoŭ

Reputation: 11251

SQL: conditioning in aggregation function

I have following sql:

  SELECT LISTAGG((TO_CHAR(ch.count), '|') WITHIN GROUP (ORDER BY ch.Count)
    FROM ChG cg
    JOIN Ch ch on ch.GroupID = cg.GroupID
    WHERE cg.PartyID = cp.PartyID

I would like to add condition, pseudocode: if(ch.TYPECODE = 1) then ch.count = 'A' + ch.count. How it's better to achieve in stored procedure?

Upvotes: 0

Views: 72

Answers (1)

user5683823
user5683823

Reputation: 

listagg(case when ch.typecode = 1 then 'A' end || to_char(ch.count), '|') .....

Before aggregation, each row is inspected for the condition ch.typecode = 1. If it is true, an 'A' is pre-pended (concatenated in front of) to_char(ch.count). I am just guessing that's what you need.

If you need this 'A' to be pre-pended to ch.count for the ORDER BY condition also, then you can do the same thing there. You will need to wrap within to_char as well. (If you don't, Big Brother Oracle will do it for you anyway, but try to avoid implicit conversions whenever possible.)

Upvotes: 2

Related Questions