OraFAQ Forum: SQL & PL/SQL » NUMERIC DENOMINATION

Home » SQL & PL/SQL » SQL & PL/SQL » NUMERIC DENOMINATION (SQL NAV 6)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NUMERIC DENOMINATION [message #640282]

Sun, 26 July 2015 10:05

rukku2015
Messages: 1
Registered: July 2015
Location: INDIA

Junior Member

its a query to output the denomination values for amount
the query is throwing an error as "Invalid Identifier"
but the same query is running in access

wohours = 2688

select wohours/1000 rs_1000,
rs_1000/500 rs_500,
rs_500/100 rs_100,
rs_100/50 rs_50,
rs_50/20 rs_20,
rs_10/10 rs_10,
rs_10/5 rs_5,
rs_10/5 rs_2,
rs_10/5 rs_1,
from ts

invalid identifier error

output should be like

rs_1000 rs_500 rs_100 rs_50 rs_20 rs_10 rs_5 rs_2 rs_1
-------------------------------------------------------------------
2 1 1 1 2 1 1 2 1

any help on this query
thank you

[Updated on: Sun, 26 July 2015 10:08]

Report message to a moderator

Re: NUMERIC DENOMINATION [message #640283 is a reply to message #640282]

Sun, 26 July 2015 10:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

SQL> select sal, sal/1000 rs_sal1000, sal/1000/10 rs_sal10000, rs_sal_1000/10 from emp where empno=7839;
select sal, sal/1000 rs_sal1000, sal/1000/10 rs_sal10000, rs_sal_1000/10 from emp where empno=7839
                                                          *
ERROR at line 1:
ORA-00904: "RS_SAL_1000": invalid identifier

[Updated on: Sun, 26 July 2015 10:39]

Report message to a moderator

Re: NUMERIC DENOMINATION [message #640536 is a reply to message #640282]

Thu, 30 July 2015 08:12

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

You can't reference aliases in select list/where clause on same level. You either need:

select  wohours / 1000 rs_1000,
        wohours / 1000 / 500 rs_500,
        wohours / 1000 / 500 / 100 rs_100,
        wohours / 1000 / 500 / 100 / 50 rs_50,
        wohours / 1000 / 500 / 100 / 50 / 20 rs_20,
        wohours / 1000 / 500 / 100 / 50 / 20 / 10 rs_10,
        wohours / 1000 / 500 / 100 / 50 / 20 / 10 / 5 rs_5,
        wohours / 1000 / 500 / 100 / 50 / 20 / 10 / 5 rs_2,
        wohours / 1000 / 500 / 100 / 50 / 20 / 10 / 5 rs_1,
  from  ts
/

Or:

with t1 as (
            select  wohours/1000 rs_1000
              from  ts
           ),
     t2 as (
            select  rs_1000,
                    rs_1000 / 500 rs_500
              from  t1
           ),
     t3 as (
            select  rs_1000,
                    rs_500,
                    rs_500 / 100 rs_100
              from  t2
           ),
     t4 as (
            select  rs_1000,
                    rs_500,
                    rs_100,
                    rs_100 / 50 rs_50
              from  t3
           ),
     t5 as (
            select  rs_1000,
                    rs_500,
                    rs_100,
                    rs_50,
                    rs_50 / 20 rs_20
              from  t4
           ),
     t6 as (
            select  rs_1000,
                    rs_500,
                    rs_100,
                    rs_50,
                    rs_20,
                    rs_20 / 10 rs_10
              from  t5
           )
select  rs_1000,
        rs_500,
        rs_100,
        rs_50,
        rs_20,
        rs_10
        rs_10 / 5 rs_5,
        rs_10 / 5 rs_2,
        rs_10 / 5 rs_1
  from  t6
/

SY.

Report message to a moderator

Previous Topic:	Need a query to get the ouput in an expected format
Next Topic:	Include if else in sql statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 29 02:15:36 CDT 2026