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

argument of CASE/WHEN must not return a set

From
"George Weaver"
Date:
Good morning,
 
I've have solved my problem in another way, but I am curious as to why I am getting the following error.
 
The following returns a boolean value a expected:
 
development=# SELECT LENGTH(ARRAY_TO_STRING(  REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
development(#                        , ',')
development(#                        )  =
development-#        LENGTH('12-70510')
development-#
development-#     AS "12-70510";
 12-70510
----------------
 f
(1 row)
 
But if I put the comparison into a CASE or WHERE clause I get this error:
 
development=# SELECT CASE
development-#             WHEN LENGTH(ARRAY_TO_STRING(  REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
development(#                                         , ',')
development(#                         )
development-#                  = LENGTH('12-70510')
development-#
development-#             THEN ARRAY_TO_STRING(  REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
development(#                                  , ',')
development-#
development-#             ELSE ''
development-#         END AS "12-70510";
ERROR:  argument of CASE/WHEN must not return a set
LINE 2:             WHEN LENGTH(ARRAY_TO_STRING(  REGEXP_MATCHES('12...
 
 
What am I missing?
 
Thanks,
George

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

From
Sameer Kumar
Date:

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

email patch

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

Attachment

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

From
Sameer Kumar
Date:

On Thu, Jan 9, 2014 at 6:05 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:

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)



Sorry about this response. The issue seems to be different.

 

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

email patch

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


Attachment

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

From
David Johnston
Date:
Sameer Kumar wrote
> On Thu, Jan 9, 2014 at 1:26 AM, George Weaver <

> gweaver@

> > 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 (7K)
> <http://postgresql.1045698.n5.nabble.com/attachment/5786031/0/image002.jpg>

The condition (WHEN) in a case cannot be a set.  You have to make the
expression always resolve to a single row/value.

I'd suggest creating a regexp_matches_single(...) function that calls
regexp_matches(...) in a sub-select so that no matches results in null.  You
then need to decide how you want to handle multiple matches. This function
will return a single text[] and so can be used in places where you want your
match to only and always return a single result (i.e. non-global behavior).

Note a recent patch was applied yesterday to resolve an ancient undiscovered
bug related to this kind of query as well. Using the above function/behavior
will let you avoid that bug as well.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/argument-of-CASE-WHEN-must-not-return-a-set-tp5785874p5786085.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

From
"George Weaver"
Date:
----- Original Message -----
From: David Johnston

<SNIP>

>The condition (WHEN) in a case cannot be a set.  You have to make the
>expression always resolve to a single row/value.

>I'd suggest creating a regexp_matches_single(...) function that calls
>regexp_matches(...) in a sub-select so that no matches results in null.
>You
>then need to decide how you want to handle multiple matches. This function
>will return a single text[] and so can be used in places where you want
>your
>match to only and always return a single result (i.e. non-global behavior).

Thanks David,

I found that if the whole expression is made a sub-select it works:

development=# SELECT CASE
development-#             WHEN (SELECT LENGTH(ARRAY_TO_STRING(
REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
development(#                                         , ',')
development(#                         ) = LENGTH('12-70510')
development(#                  )
development-#            THEN ARRAY_TO_STRING(
REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
development(#                                  , ',')
development-#             ELSE ''
development-#         END AS "12-70510"
development-# ;
 12-70510
----------

(1 row)

Cheers,
George

>Note a recent patch was applied yesterday to resolve an ancient
>undiscovered
>bug related to this kind of query as well. Using the above
>function/behavior
>will let you avoid that bug as well.

>David J.

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

From
Sameer Kumar
Date:

On Fri, Jan 10, 2014 at 12:02 AM, George Weaver <gweaver@shaw.ca> wrote:
Thanks David,

I found that if the whole expression is made a sub-select it works:

I too eventually got there. :-)

Check the plan for two queries that you have.


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

email patch

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

Attachment