SQL case Statement help

[Kaia2001]

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.

 

[SkipVought]

Hi,

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Olaf Doschke]

To get counts for any container:

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

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

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.

 

[Olaf Doschke]

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.

 

[Kaia2001]

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

 

[Kaia2001]

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.

 

[Olaf Doschke]

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.