×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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.

Students Click Here

CASE when is removing ZERO'S from a VARCHAR(3)

CASE when is removing ZERO'S from a VARCHAR(3)

CASE when is removing ZERO'S from a VARCHAR(3)

(OP)
When creating a CASE when command in a select statement it is cutting what was originally '079' to an output of '79'. I have tried to CAST the CASE as an INT(3) to avoid the problem but is still removing the zero's before numbers. Do you know why this would do this? I have also tried CAST to a VARCHAR(3) which the original data type.

Here is the script....

SELECT DISTINCT
    TRAINEE.TRAINEEID as 'Trainee ID',
    TRAINEEPOT.SERIALNUMBER as 'Learner Ref',
    TRAINEEPOT.POT as 'POT',
    TRAINEE.LASTNAME as 'Surname',
    TRAINEE.FIRSTNAME as 'First Name(s)',
    TRAINEE.DOB as DOB,
    datediff("yy",TRAINEE.DOB,TRAINEEPOT.STARTDATE) as 'Age at Start',
    TRAINEE.GENDER as 'Gender',
    EthLookup.LONGSTRING AS 'Ethnicity',
    DisLookup.LONGSTRING as 'Disabled?',
    TRAINEEPOT.STARTDATE as 'Client Start Date',
    TRAINEEPOT.TERMINATIONDATE as 'Client Leave Date',
    CONTRACTOR.DESCRIPTION as 'Contractor',    
    CONTRACTORDelivery.DESCRIPTION as 'Centre',
CASE WHEN TRAINEEPOT.ESFLOCALPROJECTNUMBER IS NULL OR TRAINEEPOT.ESFLOCALPROJECTNUMBER = '' THEN 123
           ELSE CAST (TRAINEEPOT.ESFLOCALPROJECTNUMBER AS VARCHAR(3))
           END as 'Project No',
    TRAINEEPOT.DELIVERYPOSTCODE as 'Delivery Postcode',
    TRAINEEDETAILS.NVQREF as ' Qualification Code',

MaytasFunc.maytasro.ckLSCTitleType(TRAINEEDETAILS.NVQREF,'title') AS 'Qualification Title',
MaytasFunc.maytasro.ckLSCTitleType(TRAINEEDETAILS.NVQREF,'type') AS 'Qualification Type'     

FROM
    Maytas3.TRAINEE TRAINEE
INNER JOIN Maytas3.TRAINEEPOT TRAINEEPOT ON
    TRAINEE.traineeid = TRAINEEPOT.traineeid
LEFT JOIN Maytas3.TRAINEEDETAILS TRAINEEDETAILS ON
    TRAINEEPOT.traineeid = TRAINEEDETAILS.traineeid
    AND    TRAINEEPOT.pot = TRAINEEDETAILS.pot
LEFT JOIN Maytas3.TRDELIVERYCONTRACTOR TRDELIVERYCONTRACTOR ON
    TRAINEEPOT.traineeid = TRDELIVERYCONTRACTOR.traineeid
    AND    TRAINEEPOT.pot = TRDELIVERYCONTRACTOR.pot
LEFT JOIN Maytas3.CONTRACTOR CONTRACTOR ON
    TRAINEEPOT.contractorid = CONTRACTOR.contractorid
LEFT JOIN Maytas3.CONTRACTOR CONTRACTORDelivery ON
    TRDELIVERYCONTRACTOR.contractorid = CONTRACTORDelivery.contractorid
LEFT JOIN Maytas3.Lookup EthLookup ON
    EthLookup.TABLENAME = 'TRAINEE' AND
    EthLookup.FIELDNAME = 'ETHNICGROUP' AND
    EthLookup.SHORTSTRING = TRAINEE.ethnicgroup
LEFT JOIN Maytas3.Lookup DisLookup ON
    DisLookup.TABLENAME = 'TRAINEEDETAILS' AND
    DisLookup.FIELDNAME = 'DISABLED' AND
    DisLookup.SHORTSTRING = TRAINEEDETAILS.disabled
WHERE
         CONTRACTOR.DESCRIPTION LIKE '%Batch%WS%'

RE: CASE when is removing ZERO'S from a VARCHAR(3)

Where you have THEN 123, make it say THEN '123'

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! Already a Member? Login

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