I cannot calculate a division in my SQL code

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?

Answers


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.


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

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.


Did you forget "as"?

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

Need Your Help

Identify Cells in CollectionView

ios swift uicollectionview uicollectionviewcell

I want to hide the label in a tapped cell and instead show an Image. Also I want to store this state and show the image only if a certain other cell is already displaying an image. I have a set num...

Ivy/gant include BlazeDS jars that aren`t in a public repo and have no version

blazeds ivy gant

Ive been trying to figure out the best way to include the BlazeDS jars in my Gant/Ivy build.