Javier:
Your 1st way:
Yup. Great minds. Works for both calendar and fiscal years. With my
ancient table approach I had values pre-set: [ 1 | 2011 | Q1 | 2011-Q1
].
This time, I thought I'd do an on-the-fly, in-line IFF process here, got
surprised, then just didn't want to let go. Sent quandry to support.
Your 2nd way:
Rube Goldberg's got nothing on you :
Smart Aleck.
d;)
Bruce A. Chitiea | SafeSectors, Inc.
112 Harvard Ave #272 | Claremont CA 91711-4716 | USA
[email protected] | +011 (909) 238-9012 c | +011 (909) 912-8678 f
------ Original Message ------
From: [email protected]
To: [email protected]
Sent: 6/16/2021 8:42:29 PM
Subject: RE: [RBASE-L] - IFF Function Quandry
A couple of ways to do it.
First, create a quick lookup table MonthQtr with columns
ColMonth ColQtr
1 1
2 1
3 1
4 2
5 2
6 2
7 3
8 3
9 3
10 4
11 4
12 4
And do your update easily.
UPDATE fts_input_tt SET trxQuarter = ColQtr in MonthQtr WHERE Colmonth
= trxMonth
Pretty straight forward and you can always add another column with the
actual month names for added flexibility.
Now, you know there had to be an engineer’s solution:
UPDATE fts_input_tt SET trxQuarter = ( 'Q' +
CTXT(INT(AINT((((INT(trxMonth) - 0.1) /3.0) + 1)))))
I am sure you can figure out the logic. 😊
Javier,
Javier Valencia, PE
913-915-3137
From:[email protected] <[email protected]> On Behalf Of
Bruce Chitiea
Sent: Wednesday, June 16, 2021 3:15 PM
To:[email protected]
Subject: [RBASE-L] - IFF Function Quandry
RBGX5E Current (Best viewed in monospaced font)
The trxMonth and trxQuarter columns are each defined as TEXT (2).
This statement:
UPDATE fts_input_tt +
SET trxQuarter = +
(IFF('trxMonth IN (1,2,3)','Q1','xx'))
... correctly updates trxQuarter with value Q1 where the value of
trxMonth is in (1,2,3) and with 'xx' for all other values of trxMonth.
This statement:
UPDATE fts_input_tt +
SET trxQuarter = +
(IFF('trxMonth IN (1,2,3)','Q1', +
(IFF('trxMonth IN (4,5,6)','Q2','XX'))))
... returns the error:
Parentheses required around the list of items for the IN clause. (2304)
... and aborts the UPDATE.
???
Thanks for your assistance
Bruce A. Chitiea | SafeSectors, Inc.
112 Harvard Ave #272 | Claremont CA 91711-4716 | USA
[email protected] | +011 (909) 238-9012 c | +011 (909) 912-8678 f
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/em64238e39-bec5-4762-8c3e-79ad6040a5f5%40pathfinder
<https://groups.google.com/d/msgid/rbase-l/em64238e39-bec5-4762-8c3e-79ad6040a5f5%40pathfinder?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAuAAAAAAAAAADLHb%2BWh2tFm4LP9j1qlTcBAMO2jhD3dRHOtM0AqgC7tuYAAAAAAA4AABAAAAC8u6/%2B0OtMSYbwZgFKxm4QAQAAAAA%3D%40vtgonline.com
<https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAuAAAAAAAAAADLHb%2BWh2tFm4LP9j1qlTcBAMO2jhD3dRHOtM0AqgC7tuYAAAAAAA4AABAAAAC8u6/%2B0OtMSYbwZgFKxm4QAQAAAAA%3D%40vtgonline.com?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/em531e645a-d6fd-4712-94d6-ea795bb2bf85%40pathfinder.
