Re: argument of CASE/WHEN must not return a set

On Thu, Jan 9, 2014 at 1:26 AM, George Weaver <gweaver(at)shaw(dot)ca> wrote:

> ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
> development(# , ',')

I guess this part of your statement will return 1,2, which is a set

Can you try below:
SELECT CASE
WHEN LENGTH(ARRAY_TO_STRING(
REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
, ',')
)
= LENGTH('12-70510')

THEN cast(ARRAY_TO_STRING(
REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
, ',') as varchar(100))

ELSE ''
END AS "12-70510";

But anyways, I think the best way to do it is the way you have already
figured (check the plan for both statements once you have sorted out the
error)

Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).