Custom Formatting Column Data - Teradata - UPC Numbers

Database

Custom Formatting Column Data - Teradata - UPC Numbers

Hi All - 

Thanks for taking time to read my question / post.

I'm a beginner in using Teradata and after searching the web for any insight or guidance, I'm still lost.  

How do I customize the format of the column results that I query below pertaining to an item UPC...

** For Example **

  • PI_UPC_CHEK_FL
    • This column is 1 digit number - there is no issue here, becausre query result equates to a 1 digit number
  • PI_UPC_NBR_SYS_CD
    • ISSUE
    • This column is a 2 digit number, however if the query result is less that 10 is will only result in a 1 digit number
    • How do I customize this data result so that it will add a 0 in the front of the 1 digit number if it result is less than 10?
    • Do I use FORMAT, CAST, or CASE?
  • PI_UPC_MFG_ID, PI_POS_CASE_ID, & PI_POS_UNIT_ID
    • ISSUES
    • These columns are a 5 digit number
    • How do I customize the data result for these columns if the results are less than 5 digits, by adding the necessary zeros in front of the results?
    • Do I use FORMAT, CAST, or CASE?

Here's another example of the UPC data that I'm having a problem with.

  • CASE UPC Format = 0-00-00000-00000
    • in Excel terms I would concatenate the following (PI_UPC_CHK_FL, PI_UPC_NBR_SYS_CD, PI_POS_MFG_ID, PI_POS_CASE_ID)
  • UNIT UPC Format = 00-00000-00000
    • in Excel terms I would concatenate the following (PI_UPC_NBR_SYS_CD, PI_MFG_ID, PI_POS_UNIT_ID)

Any thoughts or ideas?

/** ITEM RECAP **/

SELECT

PI_ITM_CD AS ITEM_NBR,
PI_UPC_CHK_FL AS UPC_CHK_FL,
PI_UPC_NBR_SYS_CD AS UPC_SYS_CD,
PI_POS_MFG_ID AS UPC_MFG_CD,
PI_POS_CASE_ID AS UPC_CASE_CD,
PI_POS_UNIT_ID AS UPC_UNIT_CD,
PI_BRND_DESC AS BRAND,
PI_ITM_DESC AS DESCRIPTION,
PI_ITM_PCK_QTY AS PACK,
PI_SIZE_TXT AS SZ,

FROM DATABASE.TABLE

;

1 REPLY
Enthusiast

Re: Custom Formatting Column Data - Teradata - UPC Numbers

Hi, This should work.

For PI_UPC_NBR_SYS_CD

Sel CAST(CAST(1 AS FORMAT'-9(2)') AS CHAR(2))

For PI_UPC_MFG_ID, PI_POS_CASE_ID, & PI_POS_UNIT_ID

Sel CAST(CAST(1 AS FORMAT'-9(5)') AS CHAR(5))

Thanks,

Dinesh