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

On Thu, Jan 9, 2014 at 1:26 AM, George Weaver <gweaver@shaw.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

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