โ06-09-2022 12:32 PM
My Oracle input is address information similar to:
STREET: 123 MAIN STREET
CITY: ANYTOWN
STATE: ANYSTATE
ZIP: 12345
COUNTRY: USA
STREET: null
CITY: OTHERTOWN
STATE: OTHERSTATE
ZIP: 54321
COUNTRY: CAN
STREET: null
CITY: OTHERTOWN
STATE: null
ZIP: null
COUNTRY: USA
My desired output would be as follows:
123 MAIN STREET, ANYTOWN, ANYSTATE 12345 USA
null, OTHERTOWN, OTHERSTATE, 54321 CAN
null, OTHERTOWN, null null USA
My problem occurs when โSTREETโ (the first value) is null. In that case, my entire record gets written as null.
123 MAIN STREET, ANYTOWN, ANYSTATE 12345 USA
null
null
I have used variations of this expression, all without success.
$STREET.concat(", ", $CITY, ", " , $STATE , " โ,$ZIP,โ ",$COUNTRY)
$STREET==null?โโ:$STREET.concat(", ", $CITY, ", โ, $STATEโ, โ, :$ZIPโ, ", $COUNTRY)
$STREET1==null?โโ:$STREET1.concat(โ, โ, $CITY==null?โ-โ:$CITYโ).concat(โ, โ, $STATE==null?โ-โ:$STATEโ) โฆ
Any help figuring this out is appreciated.
โ06-14-2022 04:43 AM
Hi @Damone
Did you try using string concatenation using โ+โ ?
$STREET+โ,โ+$CITY+โ,โ+$STATE+โ,โ+$ZIP+โ,โ+$COUNTRY
testCom_2022_06_14.slp (3.7 KB)
Regards,
Ajay
โ06-14-2022 07:01 AM
Hello,
Thank you for your response. Unfortunately, that isnโt working either. Oddly enough, it actually writes โnullโ out onto the table. Iโve been tweaking your expression, and I get close but keep hitting a wall. I think Iโll skip trying to use an expression and load the table using sql.
โ06-14-2022 07:11 AM
Hi @Damone
you need null value or empty or blank value for those null?
Regards,
Ajay
โ06-14-2022 07:28 AM
Good morning Ajay,
Iโd prefer to keep them as null values.