Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Missing Junctures

Received Response
147
Views
12
Comments
Antoine KAIBER
Antoine KAIBER Rank 6 - Analytics Lead
in Oracle Analytics Cloud and Server

Summary

Missing Junctures

Content

Hello,

 

I'm working on a report in which I encounter a problem when an address has a carriage return because it duplicates Data.

 

For example for a same ship_stop of a same shipment I have

this :

image

 

and this :

 

image

 

And instead I want to have the same thing but with

STOP_ADD_LINE1 (49 BOULEVARD ESTIENNE D'ORVES)

STOP_ADD_LINE2 (ZAC DU POLYGONE)

 

And also inside of a Ship_stop I have a stop_address data set which is doubled :

 

image

 

When I would like to do the same as explained above.

 

I can't send Data Model and Data sets on this forum but by mail if you need.

 

N.B: I tried a clause such as

,(SELECT address_line from location_address where line_sequence = 1 and location_gid = 'la.location_gid') STP_ADD_LINE1

,(SELECT address_line from location_address where line_sequence = 2 and location_gid = 'la.location_gid') STP_ADD_LINE2

,(SELECT address_line from location_address where line_sequence = 3 and location_gid = 'la.location_gid') STP_ADD_LINE3

 

But it didn't work as (I think) it doesn't know la.location_gid corresponds to, so there is no data on the XML (I would want the field to return data from the location_gid of the ship_stop of the shipment_gid.

Tagged:
«1

Answers

  • YGUTTIKONDA
    YGUTTIKONDA Rank 6 - Analytics Lead

    Yes understood. this should somehow be incorporated in the SQL so that the XML will be generated accordingly

  • YGUTTIKONDA
    YGUTTIKONDA Rank 6 - Analytics Lead

    see the following if they can be of any help .. I'll update if I get any more info///

    https://it.toolbox.com/question/replace-cr-lf-characters-in-long-descriptions-via-sql-script-062510

    In Depth Apps - Oracle eBusiness Suite - Gareth Roberts: BI Publisher: How do I  replace linefeeds, newline or carriage …

    https://it.toolbox.com/question/xmlp-template-how-to-avoid-cr-lf-for-if-condition-040815

    https://www.brighthub.com/internet/web-development/articles/82183.aspx

  • Antoine KAIBER
    Antoine KAIBER Rank 6 - Analytics Lead

    Yes indeed but as I wrote in my OP this "didn't work as (I think) it doesn't know to what la.location_gid corresponds to, so there is no data on the XML corresponding to the address when I put this clause (I would want the field to return data from the location_gid of the ship_stop of the shipment_gid."

    So I thought, since all the Data I want to gather is already in the XML but not in the format that I want (since both my Data sets are doubled) it must be a problem of juncture

  • Antoine KAIBER
    Antoine KAIBER Rank 6 - Analytics Lead

    Ok thanks but the thing is I don't want to avoid CR. For example if I have this :

    49 BOULEVARD ESTIENNE D'ORVES

    ZAC DU POLYGONE

    I want to display it as it is in my report. This means I would most likely have several fields :

    49 BOULEVARD ESTIENNE D'ORVES -> STP_ADD_LINE1 (where the clause would be something like (SELECT address_line from location_address where line_sequence = 1 and location_gid = 'la.location_gid'))

    ZAC DU POLYGONE -> STP_ADD_LINE2 (where the clause would be something like (SELECT address_line from location_address where line_sequence = 2 and location_gid = 'la.location_gid'))

  • Antoine KAIBER
    Antoine KAIBER Rank 6 - Analytics Lead

    The address in my original post which doubles Data Sets is due to carriage return:

    STP_ADD_LINE (49 BOULEVARD ESTIENNE D'ORVES)

    STP_ADD_LINE (ZAC DU POLYGONE)

    That means that in Location_gid PEI.00096247_00_GG1, the address is written as follows :

    49 BOULEVARD ESTIENNE D'ORVES

    ZAC DU POLYGONE

    where :

    • 49 BOULEVARD ESTIENNE D'ORVES corresponds to the LINE_SEQUENCE 1 of the address
    • ZAC DU POLYGONE corresponds to the LINE_SEQUENCE 2 of the address

  • YGUTTIKONDA
    YGUTTIKONDA Rank 6 - Analytics Lead

    can you provide a sample of carriage return data  ??

  • YGUTTIKONDA
    YGUTTIKONDA Rank 6 - Analytics Lead

    you can ignore the link, as I was under the impression this is a single query ..

    Looking at your Ship_stop query

    One "Location" can have Multiple "LOCATION_ADDRESS" correct ??

    can you try a simple query with a specific location and try to get the LOCATION_ADDRESS

  • Antoine KAIBER
    Antoine KAIBER Rank 6 - Analytics Lead

    One location_gid = One address such as below :

    image

    (There is a juncture on location_gid = stop_add_loc)

    But in some cases I have carriage return in the address so it duplicates my stop_address and my ship_stop as you can see in my original Post (FYI it's a problem because when the address is doubled, the quantities of my items linked to a shipment stop will be doubled...). So what I want is :

    • when there is no carriage return, leave it as it is above
    • when there is a carriage return add
      • STP_ADD_LINE2 (REST_OF_ADDRESS) in SHIP_STOP
      • STOP_ADD_LINE2 (REST_OF_ADDRESS) in STOP_ADDRESS
  • Antoine KAIBER
    Antoine KAIBER Rank 6 - Analytics Lead

    But if your link is only to join multiple Data Sets I have already done it. As i've said the stop_address Data Set is within the Ship_stop Data set, they are linked by location_gid.

  • Antoine KAIBER
    Antoine KAIBER Rank 6 - Analytics Lead

    Hello,

    I'm not sure I understand everything but I my queries are in different data sets :

    Ship_stop :

    select distinct

    itm.SHIPMENT_GID

    , itm.STOP_NUM

    , itm.STOP_ACTIVITY

    , itm.LOCATION_GID

    , itm.S_SHIP_UNIT_LINE_NO

    , substr(LTRIM(itm.LOCATION_GID,itm.DOMAIN_NAME||'.'),13,19) STOP_GATE

    , substr(LTRIM(itm.LOCATION_GID,itm.DOMAIN_NAME||'.'),1,11) STOP_ACCOUNT

    , coalesce (c3.CORPORATION_NAME,' ') STP_NAME

    , coalesce (c3.CORPORATION_GID,' ') STP_GID

    , l3.LOCATION_NAME STP_LOC_NAME

    , la.ADDRESS_LINE STP_ADD_LINE

    , l3.CITY STP_CITY

    , l3.POSTAL_CODE STP_CP

    , l3.COUNTRY_CODE3_GID STP_COUNTRY

    , ctr.COUNTRY_NAME STP_COUNTRY_NAME

    from SHIP_STOP_ITEM_BOV itm,LOCATION l3

    left join LOCATION_CORPORATION lc3 ON l3.LOCATION_GID= lc3.LOCATION_GID

    left join CORPORATION c3 ON lc3.CORPORATION_GID= c3.CORPORATION_GID

    left join LOCATION_ADDRESS la ON la.LOCATION_GID= l3.LOCATION_GID

    ,COUNTRY_CODE ctr

    where l3.LOCATION_GID=itm.LOCATION_GID

    and l3.COUNTRY_CODE3_GID=ctr.COUNTRY_CODE3_GID

    N.B : Instead of  "la.ADDRESS_LINE STP_ADD_LINE" I tried to put :

    ,(SELECT address_line from location_address where line_sequence = 1 and location_gid = 'la.location_gid') STP_ADD_LINE1

    ,(SELECT address_line from location_address where line_sequence = 2 and location_gid = 'la.location_gid') STP_ADD_LINE2

    ,(SELECT address_line from location_address where line_sequence = 3 and location_gid = 'la.location_gid') STP_ADD_LINE3

    but the result comes out empty, it appears the problem comes from and location_gid = 'la.location_gid'

    Stop_Address:

    select l.LOCATION_GID  STOP_ADD_LOC

    , coalesce(la1.LINE_SEQUENCE,0)      STOP_ADD_SEQ

    , coalesce(la1.ADDRESS_LINE,' ')       STOP_ADD_LINE

    ,coalesce(cnt.FIRST_NAME,' ') STOP_CNT_FIRSTNAME

    ,coalesce(cnt.LAST_NAME,' ') STOP_CNT_LASTNAME

    ,coalesce(cnt.EMAIL_ADDRESS,' ') STOP_CNT_EMAIL

    ,coalesce(cnt.PHONE1,' ') STOP_CNT_PHONE

    ,coalesce(cnt.LANGUAGE_SPOKEN,' ') STOP_CNT_LANG

    from LOCATION l left join LOCATION_ADDRESS la1 on la1.LOCATION_GID=l.LOCATION_GID left join CONTACT cnt

    ON cnt.LOCATION_GID=l.LOCATION_GID

    AND cnt.IS_PRIMARY_CONTACT='Y'

    As for the link you sent me I get this error message :

    "Access to this place or content is restricted. If you think this is a mistake, please contact your administrator or the person who directed you here."