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

[Sameer Kumar]

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

[image: email patch]

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

<<image002.jpg>>