Return select statement with sql case statement

On 07/04/2018 07:48 AM, hmidi slim wrote:

Hi,
I need to use conditional expression in my query, So I want to make a
query like this:
select numberOfPremiumDays
            case  when numberOfPremiumDays = date_part('day',
('2018-11-05'::timestamp) - ('2018-11-01'::timestamp)) then
                select product_id,
                price
                from product
                where occupation_type_id = 1
                group by product_id, occupation_type_id

However, in the documentation I found that the return of case was a value,
not like in my case I want to return a select statement.
How can I use a conditional expression in a sql query?
Best Regards.

The CASE clause is used to return one of many choices.  Based on this
example, you need to do this:

select numberOfPremiumDays,
       product_id,
       price
       from product
where occupation_type_id = 1
  and  numberOfPremiumDays = date_part('day', ('2018-11-05'::timestamp) -
('2018-11-01'::timestamp))
group by product_id, occupation_type_id

--
Angular momentum makes the world go 'round.

Actually, I need the use of case because based on the numberOfPremiumDays
there are different type of treatment:
select numberOfPremiumDays
case when numberOfPremiumDays = date_part('day',
('2018-11-05'::timestamp) - ('2018-11-01'::timestamp)) then
select product_id,
premium_price,
period_price
from product
where occupation_type_id = 1
group by product_id, occupation_type_id
else
select product_id,
classic_price,
period_price
from product1
where occupation_type_id = 1
group by product_id, occupation_type_id

Hello,

sorry your description is not clear ...
why do you use a GROUP BY on product without aggregation function min, max,
sum ?

where is defined numberOfPremiumDays ?

may be using UNION can solve your problem:

select
numberOfPremiumDays,
product_id,
premium_price,
period_price
from product, PremiumDays
where occupation_type_id = 1
and numberOfPremiumDays = date_part('day',
('2018-11-05'::timestamp) - ('2018-11-01'::timestamp))
UNION ALL
select
numberOfPremiumDays,
product_id,
classic_price,
period_price
from product1, PremiumDays
where occupation_type_id = 1
and numberOfPremiumDays != date_part('day',
('2018-11-05'::timestamp) - ('2018-11-01'::timestamp))

Regards
PAscal

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 07/04/2018 10:32 AM, hmidi slim wrote:

Actually, I need the use of case because based on the numberOfPremiumDays
there are different type of treatment:
select numberOfPremiumDays
            case  when numberOfPremiumDays = date_part('day',
('2018-11-05'::timestamp) - ('2018-11-01'::timestamp)) then
                select product_id,
                premium_price,
                period_price
                from product
                where occupation_type_id = 1
                group by product_id, occupation_type_id
           else
                select product_id,
                classic_price,
                period_price
                from product1
                where occupation_type_id = 1
                group by product_id, occupation_type_id

Then try:
select product_id,
case when numberOfPremiumDays = date_part('day', ('2018-11-05'::timestamp) -
('2018-11-01'::timestamp)) then
           premium_price
       else
           period_price
       end as the_price
from product
where occupation_type_id = 1
order by product_id, occupation_type_id

--
Angular momentum makes the world go 'round.

On 07/04/2018 03:03 PM, Ron wrote:

On 07/04/2018 10:32 AM, hmidi slim wrote:

Actually, I need the use of case because based on the
numberOfPremiumDays there are different type of treatment:
select numberOfPremiumDays
            case  when numberOfPremiumDays = date_part('day',
('2018-11-05'::timestamp) - ('2018-11-01'::timestamp)) then
                select product_id,
                premium_price,
                period_price
                from product
                where occupation_type_id = 1
                group by product_id, occupation_type_id
           else
                select product_id,
                classic_price,
                period_price
                from product1
                where occupation_type_id = 1
                group by product_id, occupation_type_id

Then try:
select product_id,
case when numberOfPremiumDays = date_part('day',
('2018-11-05'::timestamp) - ('2018-11-01'::timestamp)) then
           premium_price
       else
           period_price
       end as the_price
from product
where occupation_type_id = 1
order by product_id, occupation_type_id

The issue with the above is that table changes from product to product1
in the OP's desired behavior so the price switch alone will not work:(

--
Adrian Klaver
adrian.klaver@aklaver.com

On 07/04/2018 05:08 PM, Adrian Klaver wrote:

On 07/04/2018 03:03 PM, Ron wrote:

On 07/04/2018 10:32 AM, hmidi slim wrote:

Actually, I need the use of case because based on the
numberOfPremiumDays there are different type of treatment:
select numberOfPremiumDays
            case  when numberOfPremiumDays = date_part('day',
('2018-11-05'::timestamp) - ('2018-11-01'::timestamp)) then
                select product_id,
                premium_price,
                period_price
                from product
                where occupation_type_id = 1
                group by product_id, occupation_type_id
           else
                select product_id,
                classic_price,
                period_price
                from product1
                where occupation_type_id = 1
                group by product_id, occupation_type_id

Then try:
select product_id,
case when numberOfPremiumDays = date_part('day',
('2018-11-05'::timestamp) - ('2018-11-01'::timestamp)) then
            premium_price
        else
            period_price
        end as the_price
from product
where occupation_type_id = 1
order by product_id, occupation_type_id

The issue with the above is that table changes from product to product1 in
the OP's desired behavior so the price switch alone will not work:(

Ah, didn't notice that.  Then... dynamic sql constructed by the programming
language executing the query?

--
Angular momentum makes the world go 'round.

On Wednesday, July 4, 2018, Ron <ronljohnsonjr@gmail.com> wrote:

Ah, didn't notice that. Then... dynamic sql constructed by the
programming language executing the query?

That, the UNION idea, or pull the common stuff into the from clause and
write two left joins then coalesce whichever one provided the row. In
short, the OP cannot do what they thought they needed to do but hasn't
really provided any info for others to make alternative suggestions.

David J.