views:

148

answers:

4

The following code works without problems:

select donem, mekankodu, count(yayin_kodu) yc, 
       SUM(CASE WHEN iade =0 THEN 1 ELSE 0 END) yys
from 
(    select donem,
            bayi_adi,
            bayi_kodu,
            x.mekankodu,
            mekan_adi,
            mekan_tipi,
            yayin_kodu,
            yayin_adi,
            sum(x.b2b_dagitim + x.b2b_transfer) sevk,
            sum(x.b2b_iade) iade,
            sum(x.b2b_dagitim + x.b2b_transfer - x.b2b_iade) satis
    from mps_view2 x
    where x.donem = '200910'
    and x.yayin_kodu in (93,95,98,99,103,174,307,309,311,489,491,495,533,534,538,605,641,642,650,2539,
                         2697,4560,5049,5772,5950,5980,7318,7440,8086,8524,11161,12707,12708,12709,14376,
                         15107,15309,15633)
    and x.mekankodu in (31851,38569,7123,7403,7481)
    group by donem, bayi_adi, bayi_kodu, mekankodu, mekan_adi, mekan_tipi, yayin_kodu, yayin_adi
    order by donem, bayi_kodu, mekan_adi, yayin_adi
)
group by donem, mekankodu

When I want to include yc/yss in my result table, I arrange the SELECT part as follows:

select donem, mekankodu, count(yayin_kodu) yc, 
       SUM(CASE WHEN iade =0 THEN 1 ELSE 0 END) yys,
       yc/yys yo

but it gives

ORA-00904 Error: YYS: Invalid identifier.

What should I do?

A: 

Did you forget "as"?

select donem, mekankodu, count(yayin_kodu) yc, 
   SUM(CASE WHEN iade =0 THEN 1 ELSE 0 END) AS yys 
Manos Dilaverakis
AS is optional here. When I add AS to all SELECT statements, it still gives the same error. There must be something else. Additionally, the first code I wrote is without AS, and it works.
Mehper C. Palavuzlar
+2  A: 

You can't access a neighborly-created column in SQL, you can however access the data directory:

select donem, mekankodu, count(yayin_kodu) yc, 
       SUM(CASE WHEN iade =0 THEN 1 ELSE 0 END) yys,
       count(yayin_kodu)/SUM(CASE WHEN iade =0 THEN 1 ELSE 0 END) "yc/yys"
from 
(    select donem,
            bayi_adi,
            bayi_kodu,
            x.mekankodu,
            mekan_adi,
            mekan_tipi,
            yayin_kodu,
            yayin_adi,
            sum(x.b2b_dagitim + x.b2b_transfer) sevk,
            sum(x.b2b_iade) iade,
            sum(x.b2b_dagitim + x.b2b_transfer - x.b2b_iade) satis
    from mps_view2 x
    where x.donem = '200910'
    and x.yayin_kodu in (93,95,98,99,103,174,307,309,311,489,491,495,533,534,538,605,641,642,650,2539,
                         2697,4560,5049,5772,5950,5980,7318,7440,8086,8524,11161,12707,12708,12709,14376,
                         15107,15309,15633)
    and x.mekankodu in (31851,38569,7123,7403,7481)
    group by donem, bayi_adi, bayi_kodu, mekankodu, mekan_adi, mekan_tipi, yayin_kodu, yayin_adi
    order by donem, bayi_kodu, mekan_adi, yayin_adi
)
group by donem, mekankodu

Note: This isn't inefficient like it looks...SQL is smart enough to do the calculation once and use the result twice in most cases. More often than not, trust the optimizer.

Nick Craver
Thanks a lot. Now it works.
Mehper C. Palavuzlar
+5  A: 

Use a with statement:

with subquery_name as
(
    select
       donem,
       mekankodu,
       count(yayin_kodu) as yc, 
       SUM(CASE WHEN iade =0 THEN 1 ELSE 0 END) as yys
    from
        ...
)
select
    donem,
    mekankodu,
    yc,
    yys,
    yc/yys as yo
from
    subquery_name

This is a very useful feature of PL/SQL. You're trying to access a generated column within the same query, which is not possible. A with statement will allow you to calculate a new column based on another calculated column as you are doing here.

Aaron
Thanks for the answer. I will try your solution.
Mehper C. Palavuzlar
Does Oracle suffer from problems with integer math? As it appears to me that both these values would be integers, in SQL Server I would express the division (yc/(yys*1.0), to avoid rounding out of the decimals.
HLGEM
Oracle doesn't differentiate between integers and other numbers.
Gary
+3  A: 

You cannot refer to other columns in the same select. Use a sub-select:

select donem, mekankodu, yc, yys, yc/yys yo
from
(
    select donem, mekankodu, count(yayin_kodu) yc,
        SUM(CASE WHEN iade =0 THEN 1 ELSE 0 END) yys,
        yc/yys yo
    from ...
) Sub
Rezun