I am trying to create a common data field based on multiple columns below. When I try to use nvl i am getting blank values in the common field and its not getting picked up..The input and output tables are below:
Input table
| model-manufacture | model -CAM | MODEL -schedule |
|---|---|---|
| CX190 | ||
| NM890 | NM890 | |
| JK1231 | ||
| LO121 | LO121 | |
| VB1231 | ||
| OPE12 | ope12 | |
| jk7812 |
Output table
| model-common | model-manufacture | model -CAM | MODEL -schedule |
|---|---|---|---|
| CX190 | CX190 | ||
| NM890 | NM890 | NM890 | |
| JK1231 | JK1231 | ||
| LO121 | LO121 | LO121 | |
| VB1231 | VB1231 | ||
| ope12 | OPE12 | ope12 | |
| jk7812 | jk7812 |
Query Used
select nvl(model-manufacture,model-CAM,MODEL-schedule) as model-common from input
Advertisement
Answer
On wa is to replace empty strings with NULL with nullif
But it would be best, to save NULL
select nvl(nullif(TRIM(model-manufacture),'') ,nullif(TRIM(model-CAM),''),nullif(TRIM(MODEL-schedule),'')) as model-common from input