Re: Return select statement with sql case statement
On Wednesday, July 4, 2018, Ron 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.
Re: Return select statement with sql case statement
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.
Re: Return select statement with sql case statement
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.kla...@aklaver.com
Re: Return select statement with sql case statement
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.
Re: Return select statement with sql case statement
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
Re: 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.
Return select statement with sql case statement
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.