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!

Convert select statement to group by

Discussion in 'SQL PL/SQL' started by borje karlstedt, Nov 14, 2017.

  1. borje karlstedt

    borje karlstedt Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    spÄnga
    i.
    i creat a view as(see below)
    the problem is that i get dublicat rows and with UNION they are deselected,
    i dont want to use UNION ALL

    so my question is, how can i rewrite the two "select .. " to a Group by expression, Group by: YEAR, Month
    and get the same result as UNION ALL

    CREAT VIEW v_test_rerytering (.......) AS
    select
    F.ALDER
    age,
    cast(P.AR as VARCHAR2(4)) year,
    ccast((P.MANAD)as VARCHAR2(2)) month,
    cast(K.KON_KOD as VARCHAR2(50)) SEX,
    cast('Ext' as VARCHAR2(50)) R_KOD,
    F.NR_REK
    REK
    from test.Table_anstallda F join test.PERIOD P on P.PERIOD =
    F.PERIOD

    union

    select
    F.ALDER
    age,
    cast(P.AR as VARCHAR2(4)) year,
    ccast((P.MANAD)as VARCHAR2(2)) month,
    cast(K.KON_KOD as VARCHAR2(50)) SEX,
    cast('Internal' as VARCHAR2(50)) R_KOD,
    F.NR_dism
    REK
    from test.Table_anstallda F join test.PERIOD P on P.PERIOD_SN =
    F.PERIOD
    ;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    739
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi,Borje.
    You need to bring the script to create the tables and the script for the table to be filled with data.
    What is PK for tables table_anstallda and PERIOD ?