Post reply

Help with keeping the leading zeros

  • April 12, 2010 at 10:08 am

    #386682

    Hello,

    I run this query Select *

    into dbo.ReportFinal

    from

    (

    SELECT A.MedRecNo,

    A.AdmitDate, B.AdmitDate AS ReAdmitDate,

    A.DateOfDischarge, B.DateOfDischarge AS ReDateOfDischarge,

    A.PatientNo, B.PatientNo AS RePatientNumber,

    A.PatientName,

    A.DXCode, B.DXCode AS ReAdmitDXCode,

    A.LOS, A.DIAGName,

    A.DrgNo, B.DrgNo AS ReAdmitDrgNo,

    A.FinCode, A.FinDescr

    FROM report A

    INNER JOIN report B ON A.MedRecNo = B.MedRecNo AND

    A.AdmitDate <> B.AdmitDate and A.AdmitDate < B.AdmitDate

    )A

    the original table data type for MedRecNO is nvarchar(50) and for the new table is the same, but it keeps removing the leading zeros !

    so how to override that or what am doing wrong? :w00t:

    Please help, thanks

  • April 12, 2010 at 10:36 am

    #1150173

    the issue is datatypes; integers/number datatypes do not have preceeeding zeros;

    i think your table dbo.ReportFinal.MedRecNO is defined as an integer;

    you said it's defined as an nvarchar, but that's the only thing that makes sense, and is very common.

    an implicit conversion is converting the nvarchar to integer, so "00501" becomes 501 (note no quotes...it's number now!)

    if you ever had any MedRecNO that was "0501A", it would finally error out with an error saying unable to convert to int. right now, your query is not having that issue.

    you'll need to change the column definition in dbo.ReportFinal to preserve leading zeros.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 12, 2010 at 11:36 am

    #1150211

    Lowell is correct. You are converting this to a number in this table, and by convention, numbers do not include leading, or trailing zeros after the decimal.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • April 12, 2010 at 12:33 pm

    #1150249

    Based on what Lowell said, my recommendation is that you check the final table for the correct datatype. If you want, the CREATE TABLE statement for both tables... a million sets of eyes are better than just one.

    Also, do you have any triggers on the target table? If so, please post it.

    Last but not least, is the target table actually a target table or a view that updates and underlying table?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 14, 2010 at 8:47 am

    #1151415

    assumeing that the entire 50 will be prepended w/ zeros each time you can always use replicate function to put the zeros back. You didnt send desired example output so i cant really tell.

  • April 16, 2010 at 5:39 pm

    #1153222

    as far as I know you can't "SELECT * INTO TABLE" into an already created table, so I don't think the problem is the target table definition as it doesn't exist yet. somehow, from the subquery to the table creation, the varchar(50) is turning into an Integer you could either try CAST(A.MedRecNo AS NVARCHAR(50)) AS MedRecNo to force the datatype in the new table or REPLACE(STR(A.MedRecNo,50,0),' ','0') AS MedRecNo to enforce the zeros on the left

    --
    Thiago Dantas
    @DantHimself

  • April 17, 2010 at 8:57 am

    #1153340

    That really worked ! thanks so much and cheers for the reply and the brains....

    dant12 (4/16/2010)

    as far as I know you can't "SELECT * INTO TABLE" into an already created table, so I don't think the problem is the target table definition as it doesn't exist yet. somehow, from the subquery to the table creation, the varchar(50) is turning into an Integer you could either try CAST(A.MedRecNo AS NVARCHAR(50)) AS MedRecNo to force the datatype in the new table or REPLACE(STR(A.MedRecNo,50,0),' ','0') AS MedRecNo to enforce the zeros on the left

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply