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!

Manipulating Select SQL

Discussion in 'SQL PL/SQL' started by rudolf_r, Mar 31, 2014.

  1. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Hi Sergey,

    I'm getting an error when running the script. The error is on line 49 which includes this section of the SQL:
    Code (SQL):
    SELECT
          pr.*,
         
          CASE WHEN rn = 1 THEN
               listagg(dv_fn_transferos,',') WITHIN GROUP (ORDER BY rn),
               END  dv_fn_transferos,
          CASE WHEN rn = 1 THEN
               listagg(dv_sn_transferos,',') WITHIN GROUP (ORDER BY rn),
               END  dv_sn_transferos,      
          CASE WHEN rn = 1 THEN
               listagg(dv_fn_transferee,',') WITHIN GROUP (ORDER BY rn),
               END  dv_fn_transferee,      
          CASE WHEN rn = 1 THEN
               listagg(dv_sn_transferee,',') WITHIN GROUP (ORDER BY rn),
               END  dv_sn_transferee      
    FROM preres  pr;
    Regards
    Rudolf
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    768
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    So it turned out that, you copied request when I edited it and it there was not a final result.
    Here once again ready query:

    Code (SQL):


    WITH
    your_data AS
    (
    SELECT DISTINCT t.TITLE_DEED,
    t.SURNAME_OF_TRANSFEROR,
    t.FIRST_NAMES_OF_TRANSFEROR,
    t.DATE_OF_BIRTH AS DOB_TRANSFEROR,
    t.MARITAL_DESCRIPTION AS M_STATUS_TRANSFEROR,
    t.ID_NO AS ID_TRANSFEROR,  
    e.SURNAME_OF_TRANSFEREE,
    e.FIRST_NAMES_OF_TRANSFEREE,
    e.DATE_OF_BIRTH AS DOB_TRANSFEREE,
    e.MARITAL_DESCRIPTION AS M_STATUS_TRANSFEREE,
    e.ID_NO AS ID_TRANSFEREE,
    t.REG_DIV_ID AS REG_DIV,
    t.MUNICIPALITY AS TOWN,
    t.SUBURBS_EXTENTIONS AS EXTENTION,
    t.PARCEL_NO AS ERF_NO,
    e.AREA_SIZE_PARCEL AS AREA,
    t.REGISTRATION_DATE AS REG_DATE,
    t.PURCHASE_PRICE AS AMOUNT
    FROM V_TRANSFEROR_VIEW t, V_TRANSFEREE_VIEW e
    WHERE t.DOCUMENTS_ID = e.DOCUMENTS_ID
    AND t.PARCEL_ID = e.PARCEL_ID
    AND UPPER(T.SUBURBS_EXTENTIONS) LIKE ('OLYMPIA')
    AND UPPER(T.MUNICIPALITY) LIKE ('&TOWN_NAME')
    AND UPPER(T.REG_DIV_ID) LIKE ('&REGION')
    AND TRUNC(t.REGISTRATION_DATE) >= TO_DATE('&FROM_DATE')
    AND TRUNC(t.REGISTRATION_DATE) <= TO_DATE('&TO_DATE')
    AND UPPER(E.PARCEL_TYPE) = ('E')
    )
    ,
    preres AS
    (
    SELECT
        y.*,
        NULLIF(y.FIRST_NAMES_OF_TRANSFEROR,lead(y.FIRST_NAMES_OF_TRANSFEROR) OVER (partition BY y.TITLE_DEED,y.SURNAME_OF_TRANSFEROR ORDER BY FIRST_NAMES_OF_TRANSFEROR )) dv_fn_transferos,
        NULLIF(y.SURNAME_OF_TRANSFEROR,lead(y.SURNAME_OF_TRANSFEROR) OVER (partition BY y.TITLE_DEED ORDER BY FIRST_NAMES_OF_TRANSFEROR )) dv_sn_transferos,    
        NULLIF(y.FIRST_NAMES_OF_TRANSFEREE,lead(y.FIRST_NAMES_OF_TRANSFEREE) OVER (partition BY y.TITLE_DEED,y.SURNAME_OF_TRANSFEREE ORDER BY FIRST_NAMES_OF_TRANSFEREE )) dv_fn_transferee,
        NULLIF(y.SURNAME_OF_TRANSFEREE,lead(y.SURNAME_OF_TRANSFEREE) OVER (partition BY y.TITLE_DEED ORDER BY FIRST_NAMES_OF_TRANSFEREE )) dv_sn_transferee,
        ROW_NUMBER() OVER (partition BY y.TITLE_DEED ORDER BY efr_no ) rn
    FROM your_data y
    )
     
     
    SELECT
          pr.*,
     
     CASE WHEN rn = 1 THEN
    listagg(dv_fn_transferos,',') WITHIN GROUP (ORDER BY rn) OVER (partition BY TITLE_DEED)
    END dv_fn_transferos,
    CASE WHEN rn = 1 THEN
    listagg(dv_sn_transferos,',') WITHIN GROUP (ORDER BY rn) OVER (partition BY TITLE_DEED)
    END dv_sn_transferos,
     
    CASE WHEN rn = 1 THEN
    listagg(dv_fn_transferee,',') WITHIN GROUP (ORDER BY rn) OVER (partition BY TITLE_DEED)
    END dv_fn_transferee,
    CASE WHEN rn = 1 THEN
    listagg(dv_sn_transferee,',') WITHIN GROUP (ORDER BY rn) OVER (partition BY TITLE_DEED)
    END dv_sn_transferee
     
    FROM preres  pr;


     
     
    zargon likes this.