Oracle Analytics Cloud and Server

Products Banner

Missing Junctures

Received Response
138
Views
12
Comments

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:

Answers

  • YGUTTIKONDA
    YGUTTIKONDA ✭✭✭✭✭

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

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    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 ✭✭✭✭✭

    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 ✭✭✭✭✭

    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 ✭✭✭✭✭

    can you provide a sample of carriage return data  ??

  • YGUTTIKONDA
    YGUTTIKONDA ✭✭✭✭✭

    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 ✭✭✭✭✭

    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 ✭✭✭✭✭

    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 ✭✭✭✭✭

    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."

  • YGUTTIKONDA
    YGUTTIKONDA ✭✭✭✭✭

    Elkbir,

    From what i read and understood, the easiest way is to implement this in your Data Model (SQL Query) it is tough to interpret what la.location_gid without looking at your query .. it would be easier if you could just simplify your question.

    One other way to approach this is to have separate queries in your data model like Master / Detail or Parent / Child.

    0--YG

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    FYI, I added the line_sequence field in my Data_Set Ship_Stop and did a juncture between my Data sets Ship_Stop and Stop_Address on the filed '"line_sequence" and I solved the problem of having two "stop_address" blocks in a "Ship_Stop" block. So that's one less problem.

    But I still have the biggest problem which is that my Data set "ship_stop" is doubled when I have several line_sequence in the address of the location_gid.

    I need to merge these blocks by having only one which would contain

    STP_ADD_LINE1 and STP_ADD_LINE2 which woukld correspond to the line of the address but I don't know how to do that. Juncture in the Ship_stop Data set ? Add a clause in the ship_stop DS saying something like  :

    ,(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 ?

    Antoine