CODEWITHSUNDEEP
sqloracle-database
Mike P
Mike P

Reputation: 51

Selecting Diverse Values in SQL

I am in the process of designing a workflow to export data from an Oracle database to another system. Unfortunately, I've hit a tricky problem. The destination system uses an XML-based import and any data input must be in a very particular format. Certain fields are not nillable and must have data in them even if it is just the word "none". The reporting tool I'm using (eVisions Argos) can handle this by using If statements to conditionally print out the word none if a field is null.

Here's the problem. Some of the data in a few columns in the originating database is inconsistent. Most of the fields in question aren't null, but contain two spaces (not sure why - I'm not the DBA). The exception is a handful of fields which actually are null and some which have data in them.

Is there a way to structure my query so as to select the data in a field if it is there, but otherwise put a null value in? I tried this (the full query is excluded as it is a bit lengthy):

SELECT

...

 CASE perm_address2
     WHEN '  ' THEN null
     WHEN null THEN null
     ELSE (select perm_address2 from local_lib_extract_view where perm_address2 is not null)
 END perm_address2

...

FROM local_lib_extract_view

but got the error "ORA-01427:

single-row subquery returns more than one row". Does SQL have the capabilities to handle this scenario?

Upvotes: 1

Views: 342

Answers (1)

D-Shih
D-Shih

Reputation: 46239

The error caused your subquery to return multiple rows in ELSE.

You don't need to use a subquery. just get perm_address2 column in ELSE, you can remove second WHEN null THEN null, when perm_address2 is NULL that will be NULL so that make no sense.

SELECT

...

 CASE perm_address2
     WHEN '  ' THEN null
     ELSE perm_address2
 END perm_address2

...

FROM local_lib_extract_view

Upvotes: 1

Related Questions