Forum Discussion

Damone's avatar
Damone
New Contributor
4 years ago

Null Values Not Written to Table

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.

expression_language

4 Replies

    • Damone's avatar
      Damone
      New Contributor
      4 years ago

      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.