Problem with CASE WHEN statement

Database
Enthusiast

Problem with CASE WHEN statement

I have created a query with three CASE WHEN statements.  One segment counts total transactions from 2011 thourgh current date, second segment counts the total transactions of the last ninety days, and the last segment counts recency by a range of days.  The problem is with the last segment.   When I run the query I receive data for ranges of 1-14 days, 15-30 days, and 30-90 days.  However, no data is populated for 90-180, 180-365, and 366+.  I have provided the code below.  If anyone can help with this problem I would greatly appreciate it.

Posting text removed based on formal request from Owner of the associated Intelectual Property.

13 REPLIES
Senior Apprentice

Re: Problem with CASE WHEN statement

What is [Posting text removed based on formal request from Owner of the associated Intelectual Property.]?

If it's created based on the query of your previous post (http://forums.teradata.com/forum/database/create-table-failed-3076-problem-between-the-and-the-as) using extract(day from B.last_pmt_txn_dt) it will never be greater than 31.

Addtionally there's  no need to use BETWEEN in your CASE, the first condition to be true will be returned:

Posting text removed based on formal request from Owner of the associated Intelectual Property.

Enthusiast

Re: Problem with CASE WHEN statement

Posting text removed based on formal request from Owner of the associated Intelectual Property.

Enthusiast

Re: Problem with CASE WHEN statement

My two cent opinion...

If you need the amount of days based on the last transaction date then why don't you subtract the last transaction date from current date... you will get the number of days till the current date...

Enthusiast

Re: Problem with CASE WHEN statement

Thanks Qaisar for your two cents.  It made me realize that I was making this more complicated than needed to be.

Enthusiast

Re: Problem with CASE WHEN statement

Hello Dieter,

I adopted your code above.  However, I get the following error when I trie to execute:  Select Failed: [3707] Syntax error, expected something like a name or a Unicode delimited identifieror a UDFCALLNAME keyword between ")' and the group keyword....

I have read various blogs on the internet concerning this issue and several of the fixes I have tried didn't work.  Did I make a mistake in the code? (please see below)

Posting text removed based on formal request from Owner of the associated Intelectual Property.

Enthusiast

Re: Problem with CASE WHEN statement

Hi,

When you got a syntax error, you do not have to reas blogs, instead read you code :-)

You have misplaced the parenthesis at the following part:

Source Code removed based on formal request from Owner of the associated Intelectual Property.

You either have to remove the part 

)

Group By 1

Or place it at correct place. But I think you can remove this as it has no connection with the select statement.

Khurram
Enthusiast

Re: Problem with CASE WHEN statement

Hello Khurram,

You were right, I overlooked the syntax and assumed something worse.  However, I am receiving a new syntax error [3706] expected something between ',' and '.'.

I think the issue is in the same section, but I am not sure about what syntax is missing.

Source Code removed based on formal request from Owner of the associated Intelectual Property.

Enthusiast

Re: Problem with CASE WHEN statement

In the last statement you have comma and a dot together between the columns is_pmt_official_y_n & total_ach_txn_cnt...

Enthusiast

Re: Problem with CASE WHEN statement

Wow, I really need to wake up or get my eyes checked again....lol!  Thanks fo rthe help!