SQLite Forum

Case when
Login

Case when

(1) By anonymous on 2022-01-20 18:12:42 [link] [source]

Hello! I am trying to create a CASE WHEN for a data file. The idea is to have the CASE WHEN look at a column of data in the file (column is called "accounting period", and based on the accounting period, it will create a new file name that includes the quarter and year that the data file represents. Currently I have the following:

Select B case when ('accounting period'= '1','2','3', 'Q1'

when 'accounting period'= '4','5','6', 'Q2' when 'accounting period'= '7','8','9', 'Q3' else 'Q4') |'-'| right('accounting period',1,4) || '.csv'

B=converted file name

(2) By anonymous on 2022-01-20 18:29:25 in reply to 1 [link] [source]

I should add: The "Account period" column format is MM/DD/YYYY. So I need to include a substr/split_part to pull only the month from the Accounting Period column, but not too sure how to accomplish that.

(5) By Michael A. Cleverly (cleverly) on 2022-01-20 19:37:12 in reply to 2 [link] [source]

CASE WHEN substr("Account period", 1, 2) IN ('01','02','03') THEN 'Q1' 
     WHEN substr("Account period", 1, 2) IN ('04','05','06') THEN 'Q2'
     WHEN substr("Account period", 1, 2) IN ('07','08','09') THEN 'Q3'
     WHEN substr("Account period", 1, 2) IN ('10','11','12') THEN 'Q4'
     ELSE 'BUG!' END AS Quarter

or

printf('Q%d', ((substr("Account period", 1, 2) - 1) / 3) + 1) AS Quarter

(6) By anonymous on 2022-01-20 19:59:42 in reply to 5 [link] [source]

Thanks Michael!

Here is my new SQL, using yours and another user's responses:

CASE WHEN substr("accounting period", 1, instr("accounting period", '/') - 1) IN ('01','02','03') THEN 'Q1' WHEN substr("accounting period", 1, instr("accounting period", '/') - 1) IN ('04','05','06') THEN 'Q2' WHEN substr("accounting period", 1, instr("accounting period", '/') - 1) IN ('07','08','09') THEN 'Q3' WHEN substr("accounting period", 1, instr("accounting period", '/') - 1) IN ('10','11','12') THEN 'Q4' ELSE 'BUG!' END AS Quarter

Here is the result:

Quarter BUG! BUG! BUG! BUG! BUG! BUG! BUG! BUG! BUG!

(7) By Michael A. Cleverly (cleverly) on 2022-01-20 20:58:29 in reply to 6 [link] [source]

The "BUG! BUG! BUG!" tells us that the

substr("accounting period", 1, instr("accounting period", '/') - 1)

is returning something else that is NOT IN ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12') ...

Are you SURE your "accounting period" field is really formatted as MM/DD/YYYY? No other leading whitespace or other text?

What does:

SELECT substr("accounting period", 1, instr("accounting period", '/') - 1) FROM WhateverYourTableIsCalled

return?

(9) By David Raymond (dvdraymond) on 2022-01-21 13:03:58 in reply to 7 [source]

My bet is that their dates are like 1/2/2022, and not like 01/02/2022, but as has been pointed out, they aren't showing us their data.

(11) By anonymous on 2022-01-24 14:46:36 in reply to 9 [link] [source]

Hi again,

I confirmed the data format is MM/DD/YYYY. Below is my updated SQL:

SELECT CASE WHEN substr("accounting period", 1, 2) IN ('01','02','03') THEN 'Q1' WHEN substr("accounting period", 1, 2) IN ('04','05','06') THEN 'Q2' WHEN substr("accounting period", 1, 2)IN ('07','08','09') THEN 'Q3' WHEN substr("accounting period", 1, 2) IN ('10','11','12') THEN 'Q4' ELSE 'BUG!' END from B AS Quarter

**The ultimate goal = to rename the file something like "Q3-2021.csv" based on the data that is in the file. So there is definitely more to it than this, I need to pull the year from the "accounting period" column as well as update the SQL to rename the file like my example.

The SQL above is still giving me 11 results = "BUG!"

(12) By David Raymond (dvdraymond) on 2022-01-24 16:20:44 in reply to 11 [link] [source]

We can't help you with that until you show us some actual data that you're running it on. Telling us the result doesn't mean anything without the input. Show us some records, and what you're getting for results.

select
"accounting period",
substr("accounting period", 1, 2) as month,
<case statement here> as Quarter
from some_table;

and show us some results.

(13) By anonymous on 2022-01-24 19:57:57 in reply to 12 [link] [source]

here is the SQL that was inputted:

select substr("accounting period", 1, 2) as month from B

1.) B = table name

2.) "accounting period" = the column name found in the file. (dates found in the file are in *M/DD/YYYY format.

The results of the query above are:

Loading table: B 907 records found

month ac ac ac ac ac ac ac ac ac

...So it appears it is not recognizing the column name and is performing the substring off the word "accounting period"

(14) By Michael A. Cleverly (cleverly) on 2022-01-24 20:04:15 in reply to 13 [link] [source]

You would get that behavior if you used single quotes ('accounting period') around accounting period rather than double quotes ("accounting period").

(15) By Stephan Beal (stephan) on 2022-01-24 20:08:57 in reply to 13 [link] [source]

month ac ac ac ac ac ac ac ac ac

The implication of that would seem to be that the column is not really called "accounting period" in the database. You have not yet shown a schema demonstrating that that's the name sqlite is seeing.

(16) By Keith Medcalf (kmedcalf) on 2022-01-24 20:12:53 in reply to 13 [link] [source]

This would mean that there is no column accounting period in the table B.

You can try putting identifier quotes around it, rather than using quotation marks. Unless you have DQS=0 then handling of quotes is slippery.

Try running:

select `accounting period` from B;

and see what error message you receive.

(17) By anonymous on 2022-01-25 01:29:34 in reply to 16 [link] [source]

The data found in the "Accounting period" column all reflects "09/30/2021".

The SQL used is below:

SELECT CASE WHEN substr(accounting period, 1, 2) IN ('01','02','03') THEN 'Q1' WHEN substr(accounting period, 1, 2) IN ('04','05','06') THEN 'Q2' WHEN substr(accounting period, 1, 2)IN ('07','08','09') THEN 'Q3' WHEN substr(accounting period, 1, 2) IN ('10','11','12') THEN 'Q4' ELSE 'BUG!' END from B AS Quarter

The result was: Q3

So this is working correctly now. But now I am trying to rename the entire file to "Q3-2021.csv". The SQL I used for this is below:

SELECT CASE WHEN substr(accounting period, 1, 2) IN ('01','02','03') THEN 'Q1' WHEN substr(accounting period, 1, 2) IN ('04','05','06') THEN 'Q2' WHEN substr(accounting period, 1, 2)IN ('07','08','09') THEN 'Q3' WHEN substr(accounting period, 1, 2) IN ('10','11','12') THEN 'Q4' ELSE 'BUG!' END from B "-" select substr(accounting period,7,4) from B ".csv" AS Filename
The result was: error near " ": syntax error

(18) By Gunter Hick (gunter_hick) on 2022-01-25 06:36:29 in reply to 17 [link] [source]

The quotes are getting mangled by the forum and the markup style you have selected in your post.

I suspect the main cause of your problems is insisting on using column names with embedded whitespace, forcing you into quote purgatory.

Single quotes delimit text constants.

Double quotes may do so too, in the case that there is no matching column in scope. This is SQLite trying to be helpful, because using double quotes around text constants is a very common error.

Try showing the schema of your table...

(19) By John Dennis (jdennis) on 2022-01-25 07:30:50 in reply to 17 [link] [source]

This will never work: substr(accounting period, 1, 2)... with that embedded space. It would need to be substr("accounting period",1,2)...

With your embedded spaces, generating the filename would need to be: select substr("accounting period",7,4)||'.csv' AS Filename from B;

(20.1) Originally by anonymous with edits by Stephan Beal (stephan) on 2022-01-25 14:13:20 from 20.0 in reply to 19 [link] [source]

Hey everyone,

First, I just looked at my last message and it looks like the SQL did not paste nicely, so I apologize for the confusion!!

Second, I think I got it to work! 

Here is my SQL (I double-checked it this time):

select CASE WHEN substr(`accounting period`, 1, 2) IN ('01','02','03') THEN 'Q1' WHEN substr(`accounting period`, 1, 2) IN ('04','05','06') THEN 'Q2' WHEN substr(`accounting period`, 1, 2)IN ('07','08','09') THEN 'Q3' WHEN substr(`accounting period`, 1, 2) IN ('10','11','12') THEN 'Q4' ELSE 'BUG!' END||'-'|| substr(`accounting period`,7,4)||'.csv' AS Filename from B


This gave me the following result (which is exactly what I was looking for):

Q3-2021.csv




Thank you all so much for your time and effort! Clearly I am still trying to learn SQL and all the nuances that come with it but this was extremely helpful for my work and beneficial for my growth! So thank you again!

(21) By anonymous on 2022-01-25 14:51:28 in reply to 20.1 [link] [source]

You can make that a (little) bit simpler:

select 'Q' ((cast(substr(accounting period, 1, 2) as integer) + 2) / 3) '-' substr(accounting period, 7, 4) '.csv' AS Filename from B

BTW sanitizing your input should have filtered out invalid dates, hence no need to handling the 'BUG' case.

(22) By Harald Hanche-Olsen (hanche) on 2022-01-25 15:41:53 in reply to 21 [link] [source]

That got seriously mangled by the markup (markdown?) process, as the vertical bars turned your whole code into a table! Try it with a narrow window for some hilarious result (that is how I discovered it).

Here it is again, properly markdown-quoted:

select 'Q' || ((cast(substr(`accounting period`, 1, 2) as integer) + 2) / 3) || '-' || substr(`accounting period`, 7, 4) || '.csv' AS Filename from B

or yet again, with some newlines added for legibility:

select 'Q'
 || ((cast(substr(`accounting period`, 1, 2) as integer) + 2) / 3) 
 || '-' || substr(`accounting period`, 7, 4) 
 || '.csv' AS Filename from B

(8) By Harald Hanche-Olsen (hanche) on 2022-01-20 21:53:32 in reply to 6 [link] [source]

Pro tip: Try to create a way for others to repeat your BUG!

It should start with an empty database, followed by one (or more) CREATE TABLE statements, followed by INSERT INTO … VALUES (…), … to populate the table, followed by a SELECT statement to exhibit the BUGgy behaviour.

Vary this recipe according to need, but present it all as a bunch of lines that people can copy and paste into a file and feed directly to sqlite3. Then all can reproduce the BUG! for themselves, and all will be happy. Unicorns and rainbows all around.

(3) By David Raymond (dvdraymond) on 2022-01-20 19:00:41 in reply to 1 [link] [source]

Not much there is valid SQL.

The format for a CASE statement is shown in the Expression page

Single quotes make string literals. You need double quotes to quote an identifier like a column name.

String concatenation is 2 pipes, not 1

Core Functions

There is no right() function, but you can use a negative second value to substr()

For the month lookup you'll use substr() and instr(), something like substr("accounting period", 1, instr("accounting period", '/') - 1)

(4) By anonymous on 2022-01-20 19:30:46 in reply to 3 [link] [source]

Thank you David! Below are my updates: SELECT || CASE WHEN (substr("accounting period", 1, instr("accounting period", '/') - 1)= '1','2','3', 'Q1' WHEN (substr("accounting period", 1, instr("accounting period", '/') - 1)= '4','5','6', 'Q2' WHEN (substr("accounting period", 1, instr("accounting period", '/') - 1)= '7','8','9', 'Q3' ELSE 'Q4') ||'-'|| substr("accounting period",1,4) || '.csv' from B

Still getting an error message, I'm guessing the SQL is incorrect in at least a couple spots

(10) By David Raymond (dvdraymond) on 2022-01-21 13:24:11 in reply to 4 [link] [source]

The || thing is a binary operator. It's just like multiplication *, division /  etc. in that it needs something on both sides of it to mean anything.

There's nothing on the left hand side of the operator here
SELECT || CASE...

You have mismatched parenthesis. You have 2 more open parenthesis than you have close parenthesis.

You're using CASE... but don't have THEN or END in there anywhere. Again, look at how to use CASE: https://www.sqlite.org/lang_expr.html#the_case_expression

= can't be used to test against multiple things. It is, again, a binary operator. It takes what's immediately on the left, immediately on the right, compares them and returns the result. To compare 1 thing on the left against multiple things on the right use IN:  https://www.sqlite.org/lang_expr.html#the_in_and_not_in_operators
(which I guess is technically still a binary operator comparing 1 thing on the left with 1 list on the right...)

substr(...) IN ('1', '2', '3')

substr(...) = '1','2','3','Q1'
is like saying
(substr(...) = '1'), '2', '3', 'Q1'
or
true, '2', '3', 'Q1'
It doesn't mean anything