Обсуждение: 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,
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
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.
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,
where occupation_type_id = 1
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 productwhere occupation_type_id = 1
group by product_id, occupation_type_id
else
select product_id,
classic_price,
select product_id,
classic_price,
period_price
from product1
where occupation_type_id = 1
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.