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!

Value of Alias Column displayed twice

Discussion in 'SQL PL/SQL' started by rudolf_r, Feb 18, 2014.

  1. rudolf_r

    rudolf_r Active Member

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

    I have the following SQL report, pulling data from Oracle 11g Database. The report was done by a previous dba. Kindly advice me on how I can format the report to display only once the vlaue of either "TRANSFEREE" or "TRANSFEROR". The SQL statement is attached aas file, SQL-Report1.txt. Please see the attached sample report file, Report1.PNG


    Thank you.
    Rudolf
     

    Attached Files:

  2. jagadekara

    jagadekara Forum Guru

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

    It is not alias name problem.

    In query they took two columns. If you don't want one just remove it.

    Modified query is like below.

    SELECT DISTINCT /*+ FIRST_ROWS(10000)*/
    D.DOC_REF_NO AS TITLE_DEED,
    TRIM(L.FIRST_NAME||' '||L.SURNAME||' '||
    L.COMPANY_NAME)||' '||
    CASE
    WHEN L.DATE_OF_BIRTH IS NOT NULL THEN
    ' (DOB: '||L.DATE_OF_BIRTH||') '
    END||
    CASE
    WHEN L.ID_NO IS NOT NULL THEN
    ' (ID: '||L.ID_NO||') '
    END||
    CASE
    WHEN L.CD_MARITAL_TYPE IS NOT NULL THEN
    ' (Marital: '||MS.DESCRIPTION||') '
    END||
    CASE
    WHEN L.COMPANY_REG_NO IS NOT NULL THEN
    ' (Company. Reg. No: '||L.COMPANY_REG_NO||') '
    END
    AS TRANSFEROR,
    E.PARCEL_NO AS ERF_NO,
    LA.LOCAL_AUTH_NAME AS TOWN_NAME,
    TRIM(E.EXTENT||' '||E.EXTENT_UNIT) AS ERF_SIZE,
    P.REGISTRATION_DATE AS REG_DATE,
    T.TSV_NAME AS RD,
    P.PURCHASE_PRICE AS AMOUNT
    /*ROWNUM */
    FROM PARCEL E
    INNER JOIN PROP_OWNERS P
    ON E.PARCEL_ID = P.PARCEL_ID
    INNER JOIN LEGAL_ENTITIES L
    ON P.LEGAL_ENTITY_NO = L.LEGAL_ENTITY_NO
    INNER JOIN DOCUMENTS D
    ON P.DOCUMENTS_ID = D.DOCUMENTS_ID
    LEFT JOIN TRANSACTION_ROLE TR
    ON P.TRANSACTION_ROLE_ID = TR.TRANSACTION_ROLE_ID
    LEFT JOIN TSV T
    ON E.TSV_ID = T.TSV_ID
    LEFT JOIN MUNICIPALITIES MU
    ON T.MUNIC_ID = MU.MUNIC_ID
    LEFT JOIN LOCAL_AUTHORITIES LA
    ON MU.LOCAL_AUTH_ID = LA.LOCAL_AUTH_ID
    LEFT JOIN CD_MARITAL_STATUS_TYPES MS
    ON L.CD_MARITAL_TYPE = MS.CD_MARITAL_STATUS_TYPE
    WHERE ROWNUM <=100000

    AND P.TRANSACTION_ROLE_ID IN (5,6)
    AND UPPER(T.TSV_NAME) LIKE 'WINDHOEK%'
    AND TRUNC(P.REGISTRATION_DATE) >= TO_DATE('1/1/2012','mm/dd/yyyy')
    AND TRUNC(P.REGISTRATION_DATE) <= TO_DATE('12/31/2012','mm/dd/yyyy') AND UPPER(E.PARCEL_TYPE) = 'E'
    ORDER BY D.DOC_REF_NO
     
  3. rudolf_r

    rudolf_r Active Member

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

    Thanks for your reply. Maybe I have not been very clear in my explaination of the problem.
    The values can't be the same. The join on "LEFT JOIN TRANSACTION_ROLE TR
    ON P.TRANSACTION_ROLE_ID = TR.TRANSACTION_ROLE_ID" is crucial here. The column for "TRANSFEROR" = '5" and for "TRANSFEREE" = '6' as in table "TRANSACTION_ROLE_ID".

    Thanks.
    Rudolf
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    We need to see your data (or sample data similar to what you're querying) before we can troubleshoot this issue. You have provided the query, now provide the create table statements and sample data.
     
  5. jagadekara

    jagadekara Forum Guru

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

    Still I didn't get your questn.
     
  6. rudolf_r

    rudolf_r Active Member

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

    Maybe this will explain more. Please look at the attach file Report2.png. This is how the final result should look like. In Report1.png which is the result of my SQL the data for column for "TRANSFEROR" and "TRANSFEREE" are repeated in both columns, because of me repeating the ALIAS in the SQL.

    Thanks.
    Rudolf
     

    Attached Files:

  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, it doesn't. It doesn't supply create table statements, it doesn't supply sample data. We can't tell from that report WHAT may be wrong with the source data.
     
  8. rudolf_r

    rudolf_r Active Member

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

    My apologies for only replying now. I've looked at other options on how to do this. I have created a view from the SQL that i have provided previously, please see the attached V_VIEW_TRANSFER_REPORT.xls

    I need to have the LEGAL_ENTITY_NAME Column displayed as "TRANSFEROR" for when TRANSACTION_ROLE_ID = 6 else LEGAL_ENTITY_NAME Column displayed as "TRANSFEREE" when TRANSACTIO_ROLE_ID = 5. Hope this makes a little sense now.

    Kindly let me know how i can achieve this.

    Thanks and best regards
    Rudolf
     

    Attached Files: