Hello,
I'm a newbie in SQL and I did another topic on tuesday to concatenate on rows and I got my answer () but, as I don't really understand the formula that was given to me and don't know all the impacts that it could have on my final report, I would like to do it another way.
So this is my SQL :
select DISTINCT (Select ADDRESS_LINE from LOCATION_ADDRESS
where location_gid ='PEI.00096247_00_GG1' AND LINE_SEQUENCE = 1) STP_ADD_LINE,
(Select ADDRESS_LINE from LOCATION_ADDRESS
where location_gid ='PEI.00096247_00_GG1' AND LINE_SEQUENCE = 2) STP_ADD_LINE2
FROM LOCATION_ADDRESS
which gives me this result :
And I would eventually add :
"Select ADDRESS_LINE from LOCATION_ADDRESS
where location_gid ='PEI.00096247_00_GG1' AND LINE_SEQUENCE = 3" and so on if the address is very long or there are several carriage returns in the address.
And as a result I would like to have one field with:
"49 BOULEVARD ESTIENNE D'ORVES ZAC DU POLYGONE":
I tried the concatenate formula but as i'm a newbie I can't manage to make it work. Fyi this is my Data Set (short version of it) in which I will add the formula :
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