[Oracle] CASE Syntax in a SELECT

[Chris Neff]

Hi there
I'm stuck in Oracle by trying to overtake the MS SQLs nice syntax

[B]SELECT
     portfolio,
     in =
     CASE
          WHEN trn = 'in' THEN SUM(trn_amount)
     END,
     out =
     CASE
          WHEN trn = 'out' THEN SUM(trn_amount)
     END
FROM
     ...[/B]

This query gave a result like:
12345 340'000.00 23'060.00
Depending on the "trn"-value, it summed the amount of the transaction either in column 2 or 3.
When I try now to accomplish this in Oracle-Syntax, I'm charmed by the idea, to use CASE/END aswell.
But when I use it the following way...

[B]SELECT
     portfolio,
     CASE
          WHEN trn = 'in' THEN SUM(trn_amount)
     END in,
     CASE
          WHEN trn = 'out' THEN SUM(trn_amount)
     END out
FROM
     ...[/B]

I have the Oracle error ORA-00907: missing right parenthesis
I know that I could accomplish it like...

[B]SELECT
     a.portfolio,
     b.in,
     c.out
FROM
     (SELECT portfolio, sum(in) FROM ... WHERE trn = 'in') a,
     (SELECT portfolio, sum(out) FROM ... WHERE trn = 'aus') b
WHERE
     a.portfolio = b.portfolio[/B]

... but don't like doing it this way.
You're idea? Am I too MSSQL fixed? Shall I use the second, working way?
Thanxx in advance!
Pumpel

[Chris Neff]

I've found it...

[B]SELECT
     portfolio,
     ( CASE
          WHEN trn = 'in' THEN SUM(trn_amount)
     END ) in,
     ( CASE
          WHEN trn = 'out' THEN SUM(trn_amount)
     END ) out
FROM
     ...[/B]

I forgot to use the () around the CASE/END Syntax. Now I've, depending on the both valid contents of trn ('in' and 'out') two rows with each data. I'll merge them et voil� - done.
Greetings
Pumpelche

[Pauline McNamara]

Glad you found a solution, and welcome to JavaRanch "Pumpelche".

Your display name doesn't quite fit the famous JavaRanch naming policy. Could you please take a minute to change your display name to show 2 names, preferably your real names. (It's all about maintaining our professional image - don't let the one-eyed moose fool you!).

Thanks and hope you'll be visiting the ranch often,
Pauline

[Chris Neff]

Pauline
Forgive me - here it is..., my real name.
Greetings
Chris

[Michael Matola]

For what it's worth, here's how I would write that in Oracle:

  select portfolio ,
    sum ( case when trn = 'in' then trn_amount end ) "in" ,
    sum ( case when trn = 'out' then trn_amount end ) "out"
   from pf
  group by portfolio

Note that I put the table alias "in" in quotations because "in" is a reserved word in SQL. Otherwise, I believe Oracle's trying to parse the query with in as an operator.
Oddly, I can't reproduce your original parenthesis error. Nor can I get your proposed solution to work. I've used CASE...WHEN inside grouping functions many times, but I'd never seen your approach of using the grouping function as the THEN value.
Anyhow, the CASE...WHEN...THEN...END statement rules.