[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Add a 0 padding to an id number less then 6

Posted on 2013-01-28
3
Medium Priority
?
297 Views
Last Modified: 2013-01-28
I have a Query that I export as a .udb file that is uploaded to our food service software.
The query is working ok now but I am getting errors on the upload because a few of the students have old ID numbers less then 6 digits and the sofwtare requires students with less then 6 digit card numbers to be padded with 0's.

Here is the query I have, I need to know how to pad the ID_NUM with a 0
SET NOCOUNT ON

SELECT LTRIM('!Version: 4.2') + CHAR(10) +
'!RecordCount:'+
cast((SELECT count(DISTINCT NAME_MASTER.ID_NUM)
FROM         NAME_MASTER INNER JOIN
                      STUDENT_MASTER ON NAME_MASTER.ID_NUM = STUDENT_MASTER.ID_NUM INNER JOIN
                      SUBSID_MASTER ON NAME_MASTER.ID_NUM = SUBSID_MASTER.ID_NUM INNER JOIN
                      STUDENT_CRS_HIST ON STUDENT_MASTER.ID_NUM = STUDENT_CRS_HIST.ID_NUM
WHERE    ((SUBSID_MASTER.UDEF_2A_1 = 'F') AND (STUDENT_CRS_HIST.TRM_CDE = 'SP') AND (STUDENT_CRS_HIST.YR_CDE = '2012') AND 
                      (STUDENT_MASTER.ROOM_TYPE = '1S')) OR
                      ((SUBSID_MASTER.UDEF_2A_1 = 'F') AND (STUDENT_CRS_HIST.TRM_CDE = 'SP') AND (STUDENT_CRS_HIST.YR_CDE = '2012') AND 
                      (STUDENT_MASTER.ROOM_TYPE = '2S')) OR
                      ((SUBSID_MASTER.UDEF_2A_1 = 'F') AND (STUDENT_CRS_HIST.TRM_CDE = 'SP') AND (STUDENT_CRS_HIST.YR_CDE = '2012') AND 
                      (STUDENT_MASTER.ROOM_TYPE = '3S')) OR
                      ((SUBSID_MASTER.UDEF_2A_1 = 'F') AND (STUDENT_CRS_HIST.TRM_CDE = 'SP') AND (STUDENT_CRS_HIST.YR_CDE = '2012') AND 
                      (STUDENT_MASTER.ROOM_TYPE = '4S')) OR
                      ((SUBSID_MASTER.UDEF_2A_1 = 'F') AND (STUDENT_CRS_HIST.TRM_CDE = 'SP') AND (STUDENT_CRS_HIST.YR_CDE = '2012') AND 
                      (STUDENT_MASTER.ROOM_TYPE = '5S')) OR
                      ((SUBSID_MASTER.UDEF_2A_1 = 'F') AND (STUDENT_CRS_HIST.TRM_CDE = 'SP') AND (STUDENT_CRS_HIST.YR_CDE = '2012') AND 
                      (STUDENT_MASTER.ROOM_TYPE = '6S'))
                      ) AS varchar)
 +CHAR(10)+
'!BeginFormat' + CHAR(10) +
 '!Odyssey_PCS,' + '      ' + '1,' + '      ' + '6,' + '      ' + '"IDNUMBER"' +CHAR(10)+
'!Odyssey_PCS,' +  '      ' + '1,'+ '      ' + '6,'+ '      ' + '"CARDNUMBER"' +CHAR(10)+
'!Odyssey_PCS,' + '      ' + '14,' + '      ' + '20,' + '      ' + '"LASTNAME"' +CHAR(10)+
'!Odyssey_PCS,' + '      ' + '34,' + '      ' + '21,' + '      ' + '"FIRSTNAME"' +CHAR(10)+
'!EndFormat'+ CHAR(10) +
RTRIM('!BeginData')

SELECT DISTINCT LTRIM(CAST(NAME_MASTER.ID_NUM AS CHAR(14))) + CAST(NAME_MASTER.LAST_NAME AS CHAR(22)) + RTRIM(NAME_MASTER.FIRST_NAME) 
FROM         NAME_MASTER INNER JOIN
                      STUDENT_MASTER ON NAME_MASTER.ID_NUM = STUDENT_MASTER.ID_NUM INNER JOIN
                      SUBSID_MASTER ON NAME_MASTER.ID_NUM = SUBSID_MASTER.ID_NUM INNER JOIN
                      STUDENT_CRS_HIST ON STUDENT_MASTER.ID_NUM = STUDENT_CRS_HIST.ID_NUM
WHERE     ((SUBSID_MASTER.UDEF_2A_1 = 'F') AND (STUDENT_CRS_HIST.TRM_CDE = 'SP') AND (STUDENT_CRS_HIST.YR_CDE = '2012') AND 
                      (STUDENT_MASTER.ROOM_TYPE = '1S')) OR
                      ((SUBSID_MASTER.UDEF_2A_1 = 'F') AND (STUDENT_CRS_HIST.TRM_CDE = 'SP') AND (STUDENT_CRS_HIST.YR_CDE = '2012') AND 
                      (STUDENT_MASTER.ROOM_TYPE = '2S')) OR
                      ((SUBSID_MASTER.UDEF_2A_1 = 'F') AND (STUDENT_CRS_HIST.TRM_CDE = 'SP') AND (STUDENT_CRS_HIST.YR_CDE = '2012') AND 
                      (STUDENT_MASTER.ROOM_TYPE = '3S')) OR
                      ((SUBSID_MASTER.UDEF_2A_1 = 'F') AND (STUDENT_CRS_HIST.TRM_CDE = 'SP') AND (STUDENT_CRS_HIST.YR_CDE = '2012') AND 
                      (STUDENT_MASTER.ROOM_TYPE = '4S')) OR
                      ((SUBSID_MASTER.UDEF_2A_1 = 'F') AND (STUDENT_CRS_HIST.TRM_CDE = 'SP') AND (STUDENT_CRS_HIST.YR_CDE = '2012') AND 
                      (STUDENT_MASTER.ROOM_TYPE = '5S')) OR
                      ((SUBSID_MASTER.UDEF_2A_1 = 'F') AND (STUDENT_CRS_HIST.TRM_CDE = 'SP') AND (STUDENT_CRS_HIST.YR_CDE = '2012') AND 
                      (STUDENT_MASTER.ROOM_TYPE = '6S'))


SELECT '!EndData' AS ' '

Open in new window

0
Comment
Question by:CCC-Ravens
  • 2
3 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 38828966
You'll have to pass it as text, as numeric formats do not support leading digits.

In T-SQL, copy-paste the below into SSMS to verify that it works, then plug it into your code:

Declare @student_id int = 12345

SELECT RIGHT('000000' + CAST(@student_id as varchar(6)),6)
0
 

Author Closing Comment

by:CCC-Ravens
ID: 38829025
Perfect that worked
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38829056
Thanks for the grade.  Good luck with your project. -Jim
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question