public inbox for [email protected]
help / color / mirror / Atom feedQuery with conditional statement
5+ messages / 5 participants
[nested] [flat]
* Query with conditional statement
@ 2023-09-13 17:52 JORGE MALDONADO <[email protected]>
2023-09-13 17:57 ` RE: Query with conditional statement Tchouante, Merlin <[email protected]>
2023-09-13 18:07 ` Re: Query with conditional statement David G. Johnston <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: JORGE MALDONADO @ 2023-09-13 17:52 UTC (permalink / raw)
To: pgsql-sql
Hi,
Can a conditional CASE statement be part of the SELECT portion of a query?
For example:
SELECT
fld1, fld2, fld3,
CASE
WHEN condition1 THEN fldx1 AS a1, fldx2 AS a2, fldx3 AS a3
WHEN condition2 THEN fldy1 AS b1, fldy2 AS b2, fldy3 AS b3
ELSE .....
END,
fld6, fld7
FROM ......
WHERE ......
I ran a test and see the following:
* Each WHEN only accepts 1 result and not 3 as shown in the example
* The AS for the alias is not supported
I need to return more than 1 field on each WHEN and also assign an ALIAS.
I very much appreciate your feedback.
Regards,
Jorge Maldonado
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Query with conditional statement
2023-09-13 17:52 Query with conditional statement JORGE MALDONADO <[email protected]>
@ 2023-09-13 17:57 ` Tchouante, Merlin <[email protected]>
2023-09-13 20:39 ` Re: Query with conditional statement Shane Borden <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: Tchouante, Merlin @ 2023-09-13 17:57 UTC (permalink / raw)
To: JORGE MALDONADO <[email protected]>; pgsql-sql
Yes, it can.
Thanks,
-- Merlin
Merlin D. Tchouante, Sr. IT Enterprise Application Developer
Center for Information Technology Services (CITS)
601 West Lombard Street
Baltimore, Maryland 21201-1512
[email protected]<mailto:[email protected]>
410-706-4489 * 410-706-1500 fax
Please send Blackboard questions to the CITS support email address: [email protected]<mailto:[email protected]>
Please send Mediasite questions to the CITS support email address: [email protected]<mailto:[email protected]>
[cid:[email protected]]
From: JORGE MALDONADO <[email protected]>
Sent: Wednesday, September 13, 2023 1:52 PM
To: [email protected]
Subject: Query with conditional statement
You don't often get email from [email protected]<mailto:[email protected]>. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification;
CAUTION: This message originated from a non-UMB email system. Hover over any links before clicking and use caution opening attachments.
Hi,
Can a conditional CASE statement be part of the SELECT portion of a query? For example:
SELECT
fld1, fld2, fld3,
CASE
WHEN condition1 THEN fldx1 AS a1, fldx2 AS a2, fldx3 AS a3
WHEN condition2 THEN fldy1 AS b1, fldy2 AS b2, fldy3 AS b3
ELSE .....
END,
fld6, fld7
FROM ......
WHERE ......
I ran a test and see the following:
* Each WHEN only accepts 1 result and not 3 as shown in the example
* The AS for the alias is not supported
I need to return more than 1 field on each WHEN and also assign an ALIAS.
I very much appreciate your feedback.
Regards,
Jorge Maldonado
Attachments:
[image/png] image001.png (9.8K, 3-image001.png)
download | view image
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query with conditional statement
2023-09-13 17:52 Query with conditional statement JORGE MALDONADO <[email protected]>
2023-09-13 17:57 ` RE: Query with conditional statement Tchouante, Merlin <[email protected]>
@ 2023-09-13 20:39 ` Shane Borden <[email protected]>
2023-09-14 08:11 ` Re: Query with conditional statement Mehmet Sabri KUNT <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Shane Borden @ 2023-09-13 20:39 UTC (permalink / raw)
To: Tchouante, Merlin <[email protected]>; +Cc: JORGE MALDONADO <[email protected]>; pgsql-sql
--Apple-Mail-8819A634-8043-40FC-82F1-B41CF20DB9B4
Content-Type: text/html;
charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">You will need to do a CASE statement for ea=
ch column. I=E2=80=99m not aware of being able to return multiple colu=
mns from one case. <br><br><div dir=3D"ltr">Shane Borden<div>sborden76@=
gmail.com<br><div>Sent from my iPhone</div></div></div><div dir=3D"ltr"><br>=
<blockquote type=3D"cite">On Sep 13, 2023, at 4:23 PM, Tchouante, Merlin <=
;[email protected]> wrote:<br><br></blockquote></div><blockquote typ=
e=3D"cite"><div dir=3D"ltr">=EF=BB=BF
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii">=
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style>@font-face { font-family: "Cambria Math"; }
@font-face { font-family: Calibri; }
@font-face { }
@font-face { font-family: "Segoe UI"; }
p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0in; font-size: 11pt; fon=
t-family: Calibri, sans-serif; }
a:link, span.MsoHyperlink { color: blue; text-decoration: underline; }
span.EmailStyle19 { font-family: Calibri, sans-serif; color: rgb(31, 56, 100=
); font-weight: normal; font-style: normal; }
.MsoChpDefault { font-size: 10pt; }
@page WordSection1 { size: 8.5in 11in; margin: 1in; }
div.WordSection1 { page: WordSection1; }</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
<div class=3D"WordSection1">
<p class=3D"MsoNormal"><span style=3D"color:#1F3864">Yes, it can.<o:p></o:p>=
</span></p>
<p class=3D"MsoNormal"><span style=3D"color:#1F3864"><o:p> </o:p></span=
></p>
<div>
<p class=3D"MsoNormal" style=3D"background:white"><b><i><span style=3D"font-=
size:10.0pt;font-family:inherit;color:teal;border:none windowtext 1.0pt;padd=
ing:0in">Thanks,</span></i></b><span style=3D"color:#242424"><o:p></o:p></sp=
an></p>
<p class=3D"MsoNormal" style=3D"background:white"><b><i><span style=3D"font-=
size:10.0pt;font-family:inherit;color:black;border:none windowtext 1.0pt;pad=
ding:0in"> -- Merlin</span></i></b><span style=3D"color:#242424"><o:p>=
</o:p></span></p>
<p class=3D"MsoNormal" style=3D"background:white"><span style=3D"color:#1F38=
64;border:none windowtext 1.0pt;padding:0in"> </span><span style=3D"col=
or:#242424"><o:p></o:p></span></p>
<p class=3D"MsoNormal" style=3D"background:white"><span style=3D"color:#1F38=
64;border:none windowtext 1.0pt;padding:0in"> </span><span style=3D"col=
or:#242424"><o:p></o:p></span></p>
<p class=3D"MsoNormal" style=3D"background:white"><b><i><span style=3D"font-=
size:9.0pt;font-family:inherit;color:black;border:none windowtext 1.0pt;padd=
ing:0in">Merlin D. Tchouante,</span></i></b><span style=3D"font-size:9.0pt;f=
ont-family:inherit;color:black;border:none windowtext 1.0pt;padding:0in">&nb=
sp;</span><span style=3D"font-size:9.0pt;font-family:inherit;color:maroon;bo=
rder:none windowtext 1.0pt;padding:0in">Sr.
IT Enterprise Application Developer</span><span style=3D"font-size:9.0pt;fo=
nt-family:inherit;color:navy;border:none windowtext 1.0pt;padding:0in"><br>
</span><b><i><span style=3D"font-size:9.0pt;font-family:"Arial",sa=
ns-serif;color:red;border:none windowtext 1.0pt;padding:0in">Center for Info=
rmation Technology Services (CITS)</span></i></b><span style=3D"font-size:9.=
0pt;font-family:inherit;color:navy;border:none windowtext 1.0pt;padding:0in"=
><br>
</span><i><span style=3D"font-size:9.0pt;font-family:"Arial",sans-=
serif;color:navy;border:none windowtext 1.0pt;padding:0in">601 West Lombard S=
treet</span></i><span style=3D"font-size:9.0pt;font-family:inherit;color:nav=
y;border:none windowtext 1.0pt;padding:0in"><br>
</span><i><span style=3D"font-size:9.0pt;font-family:"Arial",sans-=
serif;color:navy;border:none windowtext 1.0pt;padding:0in">Baltimore, Maryla=
nd 21201-1512</span></i><span style=3D"font-size:9.0pt;font-family:inherit;c=
olor:navy;border:none windowtext 1.0pt;padding:0in"><br>
</span><span style=3D"color:#1F3864"><a href=3D"mailto:[email protected]=
u" title=3D"mailto:[email protected]"><i><span style=3D"font-family:&qu=
ot;Arial",sans-serif">[email protected]</span></i></a></span><span=
style=3D"font-size:9.0pt;font-family:inherit;color:black;border:none window=
text 1.0pt;padding:0in"> <br>
</span><span style=3D"color:#1F3864">410-706-4489 * 410-706-1500 fax</span><=
span style=3D"color:#242424"><o:p></o:p></span></p>
<p class=3D"MsoNormal" style=3D"background:white"><span style=3D"font-size:9=
.0pt;font-family:"Arial",sans-serif;color:teal;border:none windowt=
ext 1.0pt;padding:0in"> </span><span style=3D"color:#242424"><o:p></o:p=
></span></p>
<p class=3D"MsoNormal" style=3D"background:white"><i><span style=3D"font-siz=
e:9.0pt;font-family:inherit;color:#1F3864;border:none windowtext 1.0pt;paddi=
ng:0in">Please send Blackboard questions to the CITS support email address:<=
/span></i><span style=3D"font-size:9.0pt;font-family:inherit;color:#1F3864;b=
order:none windowtext 1.0pt;padding:0in"> <a href=3D"mailto:dl-ci=
[email protected]">[email protected]</a></span><span st=
yle=3D"color:#242424"><o:p></o:p></span></p>
<p class=3D"MsoNormal" style=3D"background:white"><i><span style=3D"font-siz=
e:9.0pt;font-family:inherit;color:#365F91;border:none windowtext 1.0pt;paddi=
ng:0in">Please send Mediasite questions to the CITS support email address:</=
span></i><span style=3D"font-size:9.0pt;font-family:inherit;color:#365F91;bo=
rder:none windowtext 1.0pt;padding:0in"> </span><span style=3D"fo=
nt-size:9.0pt;font-family:inherit;color:#1F3864;border:none windowtext 1.0pt=
;padding:0in"><a href=3D"mailto:[email protected]">DL-CI=
[email protected]</a></span><span style=3D"color:#1F3864"><o:=
p></o:p></span></p>
<p class=3D"MsoNormal" style=3D"background:white"><span style=3D"color:#1F38=
64;border:none windowtext 1.0pt;padding:0in"> </span><span style=3D"col=
or:#242424"><o:p></o:p></span></p>
<p class=3D"MsoNormal" style=3D"background:white"><span style=3D"color:#1F38=
64"><div><image001.png></div></span><span style=3D"color:#242424"><o:p=
></o:p></span></p>
</div>
<p class=3D"MsoNormal"><span style=3D"color:#1F3864"><o:p> </o:p></span=
></p>
<div>
<div style=3D"border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0=
in 0in">
<p class=3D"MsoNormal"><b>From:</b> JORGE MALDONADO <[email protected]=
om> <br>
<b>Sent:</b> Wednesday, September 13, 2023 1:52 PM<br>
<b>To:</b> [email protected]<br>
<b>Subject:</b> Query with conditional statement<o:p></o:p></p>
</div>
</div>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<table class=3D"MsoNormalTable" border=3D"0" cellspacing=3D"0" cellpadding=3D=
"0" align=3D"left" width=3D"100%" style=3D"width:100.0%">
<tbody>
<tr>
<td style=3D"background:#A6A6A6;padding:5.25pt 1.5pt 5.25pt 1.5pt"></td>
<td width=3D"100%" style=3D"width:100.0%;background:#EAEAEA;padding:5.25pt 3=
.75pt 5.25pt 11.25pt">
<div>
<p class=3D"MsoNormal" style=3D"mso-element:frame;mso-element-frame-hspace:2=
.25pt;mso-element-wrap:around;mso-element-anchor-vertical:paragraph;mso-elem=
ent-anchor-horizontal:column;mso-height-rule:exactly">
<span style=3D"font-size:9.0pt;font-family:"Segoe UI",sans-serif;c=
olor:#212121">You don't often get email from
<a href=3D"mailto:[email protected]">[email protected]</a>. <a hre=
f=3D"https://aka.ms/LearnAboutSenderIdentification";
Learn why this is important</a><o:p></o:p></span></p>
</div>
</td>
<td width=3D"75" style=3D"width:56.25pt;background:#EAEAEA;padding:5.25pt 3.=
75pt 5.25pt 3.75pt;align:left">
</td>
</tr>
</tbody>
</table>
<div>
<p class=3D"MsoNormal" align=3D"center" style=3D"mso-margin-top-alt:auto;mso=
-margin-bottom-alt:auto;text-align:center;line-height:12.0pt;background:#FFE=
B9C">
<b><span style=3D"font-size:10.0pt;color:red">CAUTION: </span></b><span styl=
e=3D"font-size:10.0pt;color:black">This message originated from a non-UMB em=
ail system. Hover over any links before clicking and use caution opening att=
achments.</span><o:p></o:p></p>
<div>
<div>
<p class=3D"MsoNormal">Hi, <o:p></o:p></p>
<div>
<p class=3D"MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class=3D"MsoNormal">Can a conditional CASE statement be part of the SELEC=
T portion of a query? For example:<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class=3D"MsoNormal">SELECT<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal">fld1, fld2, fld3,<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal">CASE <o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal"> WHEN condition1 THEN fldx1 AS a1, fldx2=
AS a2, fldx3 AS a3<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal"> WHEN condition2 THEN fldy1 AS b1, f=
ldy2 AS b2, fldy3 AS b3<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal"> ELSE .....<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal">END,<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal">fld6, fld7<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal">FROM ......<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal">WHERE ......<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class=3D"MsoNormal">I ran a test and see the following:<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal">* Each WHEN only accepts 1 result and not 3 as shown i=
n the example<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal">* The AS for the alias is not supported<o:p></o:=
p></p>
</div>
<div>
<p class=3D"MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class=3D"MsoNormal">I need to return more than 1 field on each WHEN and a=
lso assign an ALIAS.<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal">I very much appreciate your feedback.<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class=3D"MsoNormal">Regards,<o:p></o:p></p>
</div>
<div>
<p class=3D"MsoNormal">Jorge Maldonado<o:p></o:p></p>
</div>
</div>
</div>
</div>
</div>
</div></blockquote></body></html>=
--Apple-Mail-8819A634-8043-40FC-82F1-B41CF20DB9B4--
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query with conditional statement
2023-09-13 17:52 Query with conditional statement JORGE MALDONADO <[email protected]>
2023-09-13 17:57 ` RE: Query with conditional statement Tchouante, Merlin <[email protected]>
2023-09-13 20:39 ` Re: Query with conditional statement Shane Borden <[email protected]>
@ 2023-09-14 08:11 ` Mehmet Sabri KUNT <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Mehmet Sabri KUNT @ 2023-09-14 08:11 UTC (permalink / raw)
To: JORGE MALDONADO <[email protected]>; +Cc: pgsql-sql
Hi Jorge,
You can not use the case statement like in your code.
If you want to get a different field's value by your condition you can use
the case statement like the following examples.
select id,CASE WHEN condition1 THEN fldx1 END AS a1,CASE WHEN
condition1 THEN fldx2 END AS a2,CASE WHEN condition1 THEN fldx3 END AS
a3,CASE WHEN condition2 THEN fldy1 END AS b1,CASE WHEN condition2 THEN
fldy2 END AS b2,CASE WHEN condition2 THEN fldy3 END AS b3FROM
your_table
If you use like above, you have lots of null values.
If you don't have to use column names like a1, a2,a3, b1,b2, or b3 like in
your code, I suggest using like the following
selectid,CASE WHEN condition1 THEN fldx1 WHEN
condition2 THEN fldy1 ELSE aaa1END AS a1,CASE WHEN
condition1 THEN fldx2 WHEN condition2 THEN fldy2 ELSE
aaa2END AS a2,CASE WHEN condition1 THEN fldx3 WHEN
condition2 THEN fldy3 ELSE aaa3END AS a3FROM your_table
Shane Borden <[email protected]>, 14 Eyl 2023 Per, 01:43 tarihinde şunu
yazdı:
> You will need to do a CASE statement for each column. I’m not aware of
> being able to return multiple columns from one case.
>
> Shane Borden
> [email protected]
> Sent from my iPhone
>
> On Sep 13, 2023, at 4:23 PM, Tchouante, Merlin <[email protected]>
> wrote:
>
>
>
> Yes, it can.
>
>
>
> *Thanks,*
>
> * -- Merlin*
>
>
>
>
>
> *Merlin D. Tchouante,* Sr. IT Enterprise Application Developer
> *Center for Information Technology Services (CITS)*
> *601 West Lombard Street*
> *Baltimore, Maryland 21201-1512*
> *[email protected]* <[email protected]>
> 410-706-4489 * 410-706-1500 fax
>
>
>
> *Please send Blackboard questions to the CITS support email address:*
> [email protected] <[email protected]>
>
> *Please send Mediasite questions to the CITS support email address:*
> [email protected]
>
>
>
> <image001.png>
>
>
>
> *From:* JORGE MALDONADO <[email protected]>
> *Sent:* Wednesday, September 13, 2023 1:52 PM
> *To:* [email protected]
> *Subject:* Query with conditional statement
>
>
>
> You don't often get email from [email protected]. Learn why this is
> important <https://aka.ms/LearnAboutSenderIdentification;
>
> *CAUTION: *This message originated from a non-UMB email system. Hover
> over any links before clicking and use caution opening attachments.
>
> Hi,
>
>
>
> Can a conditional CASE statement be part of the SELECT portion of a query?
> For example:
>
>
>
> SELECT
>
> fld1, fld2, fld3,
>
> CASE
>
> WHEN condition1 THEN fldx1 AS a1, fldx2 AS a2, fldx3 AS a3
>
> WHEN condition2 THEN fldy1 AS b1, fldy2 AS b2, fldy3 AS b3
>
> ELSE .....
>
> END,
>
> fld6, fld7
>
> FROM ......
>
> WHERE ......
>
>
>
> I ran a test and see the following:
>
> * Each WHEN only accepts 1 result and not 3 as shown in the example
>
> * The AS for the alias is not supported
>
>
>
> I need to return more than 1 field on each WHEN and also assign an ALIAS.
>
> I very much appreciate your feedback.
>
>
>
> Regards,
>
> Jorge Maldonado
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query with conditional statement
2023-09-13 17:52 Query with conditional statement JORGE MALDONADO <[email protected]>
@ 2023-09-13 18:07 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2023-09-13 18:07 UTC (permalink / raw)
To: JORGE MALDONADO <[email protected]>; +Cc: pgsql-sql
On Wed, Sep 13, 2023 at 10:53 AM JORGE MALDONADO <[email protected]>
wrote:
> I need to return more than 1 field on each WHEN and also assign an ALIAS.
>
>
You can only produce a single value of a specific data type from the output
of a case expression. To do what you desire, create a custom composite
type and return that. You get to name the fields when you define the
composite type.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-09-14 08:11 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-13 17:52 Query with conditional statement JORGE MALDONADO <[email protected]>
2023-09-13 17:57 ` Tchouante, Merlin <[email protected]>
2023-09-13 20:39 ` Shane Borden <[email protected]>
2023-09-14 08:11 ` Mehmet Sabri KUNT <[email protected]>
2023-09-13 18:07 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox