nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Missing Junctures

Antoine KAIBER

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 :

and this :

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 :

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.

Find more posts tagged with

Reporting

Accepted answers

All comments

YGUTTIKONDA

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

YGUTTIKONDA

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

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

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

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

can you provide a sample of carriage return data ??

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

One location_gid = One address such as below :

(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

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

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