While updating the record in Sql using query...0(Zero) is getting omitted while storing in database

Question

My requirement is -- Am trying to store the value entered by user into database (One of the Table in the database). But when am entering 0921 as department, then 0 (Zero) is getting ommitted and only 921 is getting stored in the database !! Which is giving wrong output and wrong results to me.

$ write SQL "set feedback on"
$ write SQL "spool  Manager:[Aksh]akshay4.log"
$ write SQL "define IEMP_ID = '" + Ref + "';"
$ write SQL "define IYEAR = '" + File + "';"
$ write SQL "define IDEPT = '" + DEPT + "';"
$ write SQL "define IACC = '" + ACC + "';"
$ write SQL "Update EMPLOYEE set DEPT=&IDEPT where EMP_ID=&IEMP_ID and year=&IYEAR and ACC=&ACC;
$ write SQL "commit;"
$ write SQL "exit;"
$ close SQL

When I run the above code then I could see the below output:

old   1: Update employee set DEPT=&IDEPT where EMP_ID=&EMP_ID and year=&IYEAR and ACC=&ACC;
new   1: Update employee set DEPT=02150 where EMP_ID=14447 and year=2017 and ACC=1

But when I check the database after running the code..I could see only 2150 is getting saved in the database ... Any suggestions on this ??

Answer 1

You have an issue with your data type.

First of all, your column type for DEPT cannot be an integer - integers do not get stored with preceding zeroes.

So if you set an integer column to 02150, the number becomes 2150 because in the context of an integer number, the preceding 0 is pointless.

In order to fix this, you should be using a VARCHAR type data type for your DEPT column in the database.

Also, you need to change your SQL query to reflect that this is in fact a string and not a number you're storing (numbers don't have preceding zeroes).

So, to recap:

1. Verify your DEPT column is using a VARCHAR type datatype.
2. Change your SQL query to reflect it is a string and not a number you're storing.

Example:

Update employee set DEPT='02150' where EMP_ID=14447 and year=2017 and ACC=1

Answer 2

Skip the middle man, being the 'defines'. Just have DCL generate the desired line. The example below assumes the DCL variables are pure strings, to preserve leading zeroes and spaces as others pointed out. If some variables are integers, then you have to force them to be a string forcing a substitution inside double quotes using

"''numeric_variable'"

suggestion:

$ write SQL "set feedback on"
$ write SQL "spool  Manager:[Aksh]akshay4.log"
$ write SQL "Update EMPLOYEE set DEPT='" + dept 
    + "' where EMP_ID='" + ref
    + "' and YEAR='" + file 
    + "' and ACC='" + acc
    + "';"
$ write SQL "commit;" 
$ write SQL "exit;"