Case statement with multiple else

General
Apprentice

Re: Case statement with multiple else

Hi,

Looking at your screenshot it looks like the data contains a space before the letter 'P'. That would cause this symptom.

 

If that is the case, try the following:

SELECT * FROM
(
SELECT
CD00_PKT_CTL_NBR ORDER_NBR,
CD00_CASE_NBR BOX_NBR,

CD00_SKU SKU,
CD00_LOCATION LOCATION_CODE,
CASE WHEN SUBSTR(trim(CD00_LOCATION), 1,1) = 'P' THEN 'Active'
ELSE 'Case Pick' END LOC_TYPE,
CD00_DLM DLM
from vancouver.cdcart00, vancouver.phpick00
)
Where DLM between '27-SEP-2017' and '28-SEP-2017'

If that doesn't help, you could try running a query which displays the first character in column CD00_LOCATION. See what that shows and then work from there. Something like:

SELECT * FROM
(
SELECT
CD00_PKT_CTL_NBR ORDER_NBR,
CD00_CASE_NBR BOX_NBR,
SUBSTR(CD00_LOCATION, 1,1) as first_char,
CD00_SKU SKU,
CD00_LOCATION LOCATION_CODE,
CASE WHEN SUBSTR(CD00_LOCATION, 1,1) = 'P' THEN 'Active'
ELSE 'Case Pick' END LOC_TYPE,
CD00_DLM DLM
from vancouver.cdcart00, vancouver.phpick00
)
Where DLM between '27-SEP-2017' and '28-SEP-2017'

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Case statement with multiple else

Hi DaveWellman,

 

Thank you so much... it work like a charm.  The data contains a space before the letter 'P' which caused the problem.  Once again, thank you so much for all your help and your help is greatly appreciate.  Thanks Dave!

 

CASE WHEN SUBSTR(trim(CD00_LOCATION), 1,1) = 'P' THEN 'Active'
ELSE 'Case Pick' END LOC_TYPE,

 

Enthusiast

Re: Case statement with multiple else

Hi DaveWellman,

 

Sorry to bother you again ... how can join relationship of the 2 tables [vancouver.cdcart00 & vancouver.phpick00] since some of the columns on the both table had the relationship with the same data?  Your help is greatly appreciate.  Thanks!

 

Apprentice

Re: Case statement with multiple else

Hi,

Sorry but I do not understand what you are asking.

 

Are you asking how to join these tables? (I notice that there is no join condition so the result will be a Cartesian product join of the two tables).

Do you want to specify particular join conditions?

 

If you can let me know what you are trying to achieve or give an example then that might help.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Enthusiast

Re: Case statement with multiple else

Hi DaveWellman,

 

Thank you so much...  I'm able to figure out how to join the table and it works :) ... However, there is another issue that I get stuck and need your help as I want only display one "ERP" number with calculate total of "TOTAL_UNITS" in stead of multiple rows.  Example: I just only want to display 1 row of ERP # 50338999 with Total Units: 497 [by calculate each >>> 95+125+94+85+98] instead of multi rows with different "TOTAL_UNITS" and also same as ERP # 50345138.  How can I have the SQL script that can do this calculation as per my script below?  Once again, thank you so much for all your help and your help is greatly appreciate.  Thanks!

 

SELECT
PH00_SHIPTO ERP,
CD00_SKU SKU,
PH00_TOTAL_NBR_UNITS TOTAL_UNITS,
PH00_SHIPTO_NAME CUSTOMER_NAME,
CD00_DLM DLM_DATE
from mississ.cdcart00, mississ.phpick00
where CD00_PKT_CTL_NBR = PH00_PKT_CTL_NBR
and CD00_SKU = 10020555
and PH00_SHIPTO in
(
'50318703'
,'50338999'
,'50345138'
)

 

 

 

img.jpg

Apprentice

Re: Case statement with multiple else

Hi,

No problem.

You might have to ask/answer some more questions about what you really want here (see below), but the basics are:

 

A simple total of TOTAL_UNITS uses the SUM function (see https://info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1145_151K/SUM.htm...):

 

SELECT
PH00_SHIPTO ERP,
SUM(PH00_TOTAL_NBR_UNITS) TOTAL_UNITS,
from mississ.cdcart00, mississ.phpick00
where CD00_PKT_CTL_NBR = PH00_PKT_CTL_NBR
and CD00_SKU = 10020555
and PH00_SHIPTO in
(
'50318703'
,'50338999'
,'50345138'
)
GROUP BY 1;

 

 

However, that will 'lose' all of the other columns in your previous SQL statement. You will only get one row per ERP value - but I think that is what you asked for.

 

Question:What do you want displayed for the other columns?

- If you want those displayed then you'll possibly need to think about using 'window aggregate' functions (see https://info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1145_151K/Ordered...)

 

Have a look at the above and decide if that is what you want.  If the above is not what you want can you 'mock up' an answer set (use Excel or something) using the data values shown in your screenshot.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Case statement with multiple else

Hi DaveWellman,

 

As I try to run the script that you provide... it keep getting error message and I cannot figure out what cause the problem.. please see the screen shot below for more detail.  Once again thank you so much for all your help and your help is greatly appreciate.  Thanks

img.jpg

Apprentice

Re: Case statement with multiple else

It looks like you've got a "," after the name TOTAL_UNITS. Remove it.

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Case statement with multiple else

Hi Dave,

 

Yes, you are corrected...it works now...  it just the coma ","  that make the error.  You have a good eye and very good in SQL (Oracle).  Once again, thank you so much for all your help.  Thanks Dave!

Enthusiast

Re: Case statement with multiple else

Hi Dave,

 

Is it possible if I can have column SKU, CUSTOMER_NAM & DLM_DATE for report? However with my SQL script below it give me the error message.  But when I take out the CD00_SKU SKU,  PHP00_SHIPTO_NAME CUSTOMER_NAME, & CD00_DLM DLM_DATE, ... it working fine but it only show 2 columns ERP & TOTAL_UNITS.  Once again, thank you so much for all your help and your help is greatly appreciate.  Thanks Dave!

 

SELECT
PH00_SHIPTO ERP,
CD00_SKU SKU,
SUM(PH00_TOTAL_NBR_UNITS) TOTAL_UNITS
PHP00_SHIPTO_NAME CUSTOMER_NAME,
CD00_DLM DLM_DATE,
from mississ.cdcart00, mississ.phpick00
where CD00_PKT_CTL_NBR = PH00_PKT_CTL_NBR
and CD00_SKU = 10020555
and PH00_SHIPTO in
(
'50318703'
,'50338999'
,'50345138'
)
GROUP BY PH00_SHIPTO

 

img1.jpg


img2.jpg