Problem with cast convert and view all in TSQL

I have a view called vw_FormatTable1 which contains

Convert( CHAR ( <SIZE> ), Coalesce (Convert (VarChar, [<FIELD>],120),'')) AS [<FIELD>]

where <SIZE> is the width of the field to be output and <FIELD> is the field name

And this basic block is repeated for all approx 600 fields in my base table creating a view of over 50,000 characters.

My problem is as follows:

select 
  [CC_RENAL_SUPPORT_DAYS_1],
  CONVERT(CHAR(3), COALESCE(CONVERT(VARCHAR, [CC_RENAL_SUPPORT_DAYS_1]), '')) AS [CC_RENAL_SUPPORT_DAYS_SPC]
from Table1
group by [CC_RENAL_SUPPORT_DAYS_1]

produces different results from

select 
   [CC_RENAL_SUPPORT_DAYS_1],
   CONVERT(CHAR(3), COALESCE(CONVERT(VARCHAR, [CC_RENAL_SUPPORT_DAYS_1]),'')) AS [CC_RENAL_SUPPORT_DAYS_SPC]
from vw_FormatTable1
group by [CC_RENAL_SUPPORT_DAYS_1]

and I do not know why?

The first output is

[CC_RENAL_SUPPORT_DAYS_1]     [CC_RENAL_SUPPORT_DAYS_SPC]
--------------------------    --------------------------
001                           001
002                           002

whereas the second output is

[CC_RENAL_SUPPORT_DAYS_1]     [CC_RENAL_SUPPORT_DAYS_SPC]
--------------------------    --------------------------
1                             1
2                             2

The inclusion of the 120 is not causing this error (as I done some basic testing with and without it and it does not make a difference)

Can anyone answer:

• Why I am losing the leading zeros
• Why Cannot a not replicate the result in my first SQL statement?
• Is there a size limit on views?

Thanks in advance,

HighFever

ALTER View [dbo].[vw_ver2_InpatientsLong_ReadyToExport] AS select top 100 percent Convert(CHAR(6),coalesce(convert(varchar,[PRIME RECIPIENT],120),'')) AS [PRIME RECIPIENT], Convert(CHAR(6),coalesce(convert(varchar,[COPY RECIPIENT 1],120),'')) AS [COPY RECIPIENT 1], Convert(CHAR(6),coalesce(convert(varchar,[COPY RECIPIENT 2],120),'')) AS [COPY RECIPIENT 2], Convert(CHAR(6),coalesce(convert(varchar,[COPY RECIPIENT 3],120),'')) AS [COPY RECIPIENT 3], Convert(CHAR(6),coalesce(convert(varchar,[COPY RECIPIENT 4],120),'')) AS [COPY RECIPIENT 4], Convert(CHAR(6),coalesce(convert(varchar,[COPY RECIPIENT 5],120),'')) AS [COPY RECIPIENT 5], Convert(CHAR(5),coalesce(convert(varchar,[SENDER],120),'')) AS [SENDER], Convert(CHAR(3),coalesce(convert(varchar,[CDS_GROUP],120),'')) AS [CDS GROUP], Convert(CHAR(3),coalesce(convert(varchar,[CDS_TYPE],120),'')) AS [CDS TYPE], Convert(CHAR(35),coalesce(convert(varchar,[CDS_ID],120),'')) AS [CDS_ID], Convert(CHAR(1),coalesce(convert(varchar,[TEST_FLAG],120),'')) AS [TEST_FLAG], Convert(CHAR(1),coalesce(convert(varchar,[UPDATE TYPE],120),'')) AS [UPDATE TYPE], Convert(CHAR(3),coalesce(convert(varchar,[PROTOCOL IDENTIFIER],120),'')) AS [PROTOCOL IDENTIFIER], Convert(CHAR(10),coalesce(convert(varchar,[BULKSTART],120),'')) AS [BULKSTART], Convert(CHAR(10),coalesce(convert(varchar,[BULKEND],120),'')) AS [BULKEND], Convert(CHAR(16),coalesce(convert(varchar,[DATETIME_CREATED],120),'')) AS [DATETIME_CREATED], Convert(CHAR(5),coalesce(convert(varchar,[PROVIDER],120),'')) AS [PROVIDER], Convert(CHAR(5),coalesce(convert(varchar,[PURCHASER],120),'')) AS [PURCHASER], Convert(CHAR(6),coalesce(convert(varchar,[SERIAL_NO],120),'')) AS [SERIAL_NO], Convert(CHAR(10),coalesce(convert(varchar,[CONTRACT_LINE_NO],120),'')) AS [CONTRACT_LINE_NO], Convert(CHAR(17),coalesce(convert(varchar,[PURCH_REF],120),'')) AS [PURCH_REF], Convert(CHAR(17),coalesce(convert(varchar,[NHS_NO],120),'')) AS [NHS_NO], Convert(CHAR(2),coalesce(convert(varchar,[NNN_STATUS_IND],120),'')) AS [NNN_STATUS_IND], Convert(CHAR(1),coalesce(convert(varchar,[name_format],120),'')) AS [name_status], Convert(CHAR(1),coalesce(convert(varchar,[address_format],120),'')) AS [address_status], Convert(CHAR(70),coalesce(convert(varchar,[NAME],120),'')) AS [NAME], Convert(CHAR(35),coalesce(convert(varchar,[FORENAME],120),'')) AS [FORENAME], Convert(CHAR(35),coalesce(convert(varchar,[HOMEADD1],120),'')) AS [HOMEADD1], Convert(CHAR(35),coalesce(convert(varchar,[HOMEADD2],120),'')) AS [HOMEADD2], Convert(CHAR(35),coalesce(convert(varchar,[HOMEADD3],120),'')) AS [HOMEADD3], Convert(CHAR(35),coalesce(convert(varchar,[HOMEADD4],120),'')) AS [HOMEADD4], Convert(CHAR(35),coalesce(convert(varchar,[HOMEADD5],120),'')) AS [HOMEADD5], Convert(CHAR(8),coalesce(convert(varchar,[POSTCODE],120),'')) AS [POSTCODE], Convert(CHAR(3),coalesce(convert(varchar,[HA],120),'')) AS [HA], Convert(CHAR(1),coalesce(convert(varchar,[SEX],120),'')) AS [SEX], Convert(CHAR(2),coalesce(convert(varchar,[CARER_SUPPORT_IND],120),'')) AS [CARER_SUPPORT_IND], Convert(CHAR(10),coalesce(convert(varchar,[DOB],120),'')) AS [DOB], Convert(CHAR(8),coalesce(convert(varchar,[GPREG],120),'')) AS [GPREG], Convert(CHAR(6),coalesce(convert(varchar,[PRACREG],120),'')) AS [PRACREG], Convert(CHAR(2),coalesce(convert(varchar,[ETHNICOR],120),'')) AS [ETHNICOR], Convert(CHAR(10),coalesce(convert(varchar,[LOCPATID],120),'')) AS [LOCPATID], Convert(CHAR(8),coalesce(convert(varchar,[REFERRER],120),'')) AS [REFERRER], Convert(CHAR(6),coalesce(convert(varchar,[REF_ORG],120),'')) AS [REF_ORG], Convert(CHAR(12),coalesce(convert(varchar,[PROV_SPELL],120),'')) AS [PROV_SPELL], Convert(CHAR(2),coalesce(convert(varchar,[ADMIN_CATEGORY],120),'')) AS [ADMIN_CATEGORY], Convert(CHAR(2),coalesce(convert(varchar,[LEGAL_STATUS],120),'')) AS [LEGAL_STATUS],

Convert(CHAR(10),coalesce(convert(varchar,[MOM_NHS_NUM],120),'')) AS [MOM_NHS_NUM], Convert(CHAR(10),coalesce(convert(varchar,[MOM_PAT_ID],120),'')) AS [MOM_PAT_ID], Convert(CHAR(5),coalesce(convert(varchar,[MOM_PAT_ID_ORG],120),'')) AS [MOM_PAT_ID_ORG], Convert(CHAR(2),coalesce(convert(varchar,[MOM_NNN_STATUS],120),'')) AS [MOM_NNN_STATUS], Convert(CHAR(35),coalesce(convert(varchar,[MOM_USUAL_ADDRESSLINE_1],120),'')) AS 开发者_如何学编程[MOM_USUAL_ADDRESS_LINE_1], Convert(CHAR(35),coalesce(convert(varchar,[MOM_USUAL_ADDRESSLINE_2],120),'')) AS [MOM_USUAL_ADDRESS_LINE_2], Convert(CHAR(35),coalesce(convert(varchar,[MOM_USUAL_ADDRESSLINE_3],120),'')) AS [MOM_USUAL_ADDRESS_LINE_3], Convert(CHAR(35),coalesce(convert(varchar,[MOM_USUAL_ADDRESSLINE_4],120),'')) AS [MOM_USUAL_ADDRESS_LINE_4], Convert(CHAR(35),coalesce(convert(varchar,[MOM_USUAL_ADDRESSLINE_5],120),'')) AS [MOM_USUAL_ADDRESS_LINE_5], Convert(CHAR(8),coalesce(convert(varchar,[MOM_POSTCODE_OF_USUAL_ADDRESS],120),'')) AS [MOM_POSTCODE_OF_USUAL_ADDRESS], Convert(CHAR(17),coalesce(convert(varchar,[PROVIDER_REFERENCE_NUMBER],120),'')) AS [PROVIDER REFERENCE NUMBER], Convert(CHAR(5),coalesce(convert(varchar,[ORGANISATION_CODE(LOCAL PAT ID)],120),'')) AS [ORGANISATION CODE (LOCAL PAT ID)], Convert(CHAR(12),coalesce(convert(varchar,[UBRN],120),'')) AS [UBRN], Convert(CHAR(20),coalesce(convert(varchar,[CARE_PATHWAY_ID],120),'')) AS [CARE_PATHWAY_ID], Convert(CHAR(5),coalesce(convert(varchar,[CARE_PATWAY_ID_ORG],120),'')) AS [CARE_PATHWAY_ID_ORG], Convert(CHAR(2),coalesce(convert(varchar,[REF_TO_TREAT_PERIOD_STATUS],120),'')) AS [REF_TO_TREAT_PERIOD_STATUS], Convert(CHAR(10),coalesce(convert(varchar,[REF_TO_TREAT_PERIOD_START_DATE],120),'')) AS [REF_TO_TREAT_PERIOD_START_DATE], Convert(CHAR(10),coalesce(convert(varchar,[REF_TO_TREAT_PERIOD_END_DATE],120),'')) AS [REF_TO_TREAT_PERIOD_END_DATE] from tbl_SUS_CDS130

Nothing you do in the query with the formatting adds/removes leading zeros, so they must not be in the table. Leading zeros can only exist in a string based data type column, and then only if they are put there on purpose.

My best guess based on all given information is that Table1.CC_RENAL_SUPPORT_DAYS_1 is a char or varchar and has leading zeros in the column and that vw_FormatTable1.[CC_RENAL_SUPPORT_DAYS_1] is an int (or a char/varchar with no leading zeros).

if you need leading zeros in the second query, use this:

select 
    [CC_RENAL_SUPPORT_DAYS_1]
        ,RIGHT(ISNULL(REPLICATE('0',3)+CONVERT(VARCHAR, [CC_RENAL_SUPPORT_DAYS_1]),''),3) AS [CC_RENAL_SUPPORT_DAYS_SPC]
    from vw_FormatTable1 
    group by [CC_RENAL_SUPPORT_DAYS_1]