INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL case Statement help

SQL case Statement help

(OP)
I have a table that has a container type (in this case) depicted by a series of 20 numbers, i.e. 00000415401049837981. I need to count the records where the container type = '00000415401049837981' or any other 20 digit number. I want the statement to count the number of pallets on a load, if the container is a pallet, it will have a 20 digit number if it is not a pallet the field will be blank. The statement that I am using is:
CASE WHEN [P_Outbound].[CHCART00 - Carton Header].[Pallet #]>0 [P_Outbound].[CHCART00 - Carton Header].[Pallet #] ELSE null END

What am I doing incorrectly. It will not return anything. I did not ask it to count because the program I am using has an aggregate function that I can use after i narrow down the proper function.

RE: SQL case Statement help

Hi,

CODE

SELECT....
, Sum(CASE WHEN [container type] = '00000415401049837981' THEN 1 ELSE 0 END)
,..... 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: SQL case Statement help

To get counts for any container:

CODE -->

Select [container type], Count(*) as ContainerCount FROM yourtable GROUP BY [container type] 

To test for exactly 20 digits: WHERE RTRIM(LTRIM([container type])) NOT LIKE '%[^0-9]%' AND LEN(RTRIM(LTRIM([container type])))=20

First condition means the (trimmed) type should not contain any non digit anywhere (but spaces before or after) and the second condition makes sure its 20 digits.

Bye, Olaf.

RE: SQL case Statement help

It's unclear what this should mean:
[P_Outbound].[CHCART00 - Carton Header].[Pallet #]>0 [P_Outbound].[CHCART00 - Carton Header].[Pallet #]

Is [CHCART00 - Carton Header] really a single name? Is [P_Outbound].[CHCART00 - Carton Header].[Pallet #] a database.table.column or schema.table.column? I would suggest you USE the database you work on and then don't specify it in names, you only specify schema, if it's not the default dbo schema and then all names inside a query would only be two part table.column.

Bye, Olaf.

RE: SQL case Statement help

(OP)
@Skipvought When the number changes how do I incorporate that in the query. The 20 digit pallet number will be different all the time.

RE: SQL case Statement help

(OP)
It should have read :
CASE WHEN [pallet #]>0 [pallet #] ELSE null END

I'm pretty certain the problem lies here. I can not find the correct language to use to differentiate the number of 20 digit numbers that are the pallet numbers.

RE: SQL case Statement help

The CASE statement misses a THEN, but you can't test whether a string is >0, if the [Pallet #] field is numeric, then this would just separte negative from positive pallet numbers.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close