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.

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

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

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

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

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.