1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need help on sql query

Discussion in 'SQL PL/SQL' started by RG Hegde, Sep 8, 2015.

  1. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello

    data in the table
    ***************
    COL1 COL2
    -------------------
    GBP EUR
    GBP USD

    Expected output
    ******************
    COL1 COL2
    ------------------
    GBP EUR
    GBP USD
    EUR USD
    USD EUR

    CREATE TABLE xxtest(col1 varchar2(50)
    ,col2 VARCHAR2(50))

    insert into xxtest values ('GBP','EUR')
    insert into xxtest values ('GBP','USD')

    Can anyone please help me on this
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Check this...

    select col1,col2 from xxtest
    union
    select a.col2,b.col2 from xxtest a,xxtest b
    where a.col2!=b.col2
     
    RG Hegde likes this.
  3. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Cool..
    It works .. My requirement is to calculate cross rate .

    Col1 col2 Rate
    -------------------
    GBP EUR 1.43123
    GBP USD 1.55532

    Output
    Col1 col2 Rate

    GBP EUR 1.43123
    GBP USD 1.55532
    EUR USD calculated rate
    USD EUR calculated rate

    Any Idea...
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I could probably write a SQL statement that would return the data request for the example rows shown. However, it would break down when used in a wider case. This is because to perform the calculation you request requires that the solution use values from more than one row to generate the data. In SQL, this is most easily done using LAG or LEAD. However, both of these are very dependent on the row order. So for example if you added in a row for the Indian Rupee (GBP -> INR) and wanted rows calculated to show EUR -> INR and USD -> INR -- the logic would very likely break down.

    The most flexible solution would be to create a PL/SQL block to generate the data output you require. However, creating that block requires knowing what the end goal is.
     
    RG Hegde likes this.
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Try this...

    select col1,col2,rate from xxtest
    union
    select a.col2,b.col2,a.rate/b.rate from xxtest a,xxtest b
    where a.col2!=b.col2
    ;
     
  6. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    "a.rate/b.rate" should be "b.rate/a.rate". Other than that -- this produces reasonable results so long as COL1 ALWAYS contains GBP.