I have a query that is pulling data from 10 tables and a sub-query. One of the selects is for a location and I am using a case expression to clean up the data on output. The field it is pulling the data from is varchar, and the data can be values such as: USA800, admin, ccc-ulw, ccc-ury, 002-Carson, 066-Nellis BX, 042-Junction City (there are more).
I want the case express to report back a specific value if it one of the locations that don't start with a number, but if it is a number, I want it to return just the number, as an int (dropping the 0's).
Here is the query as I have it now (the entire SQL may not be needed, but I figured it would help context):
select ct.id as CaseID ,c.first_name as FirstName ,c.last_name as LastName ,c.id as CustomerID ,SUBSTRING(sdu.User_ID,CHARINDEX('-',sdu.User_ID)+1,(((LEN(sdu.User_ID))-CHARINDEX('-', REVERSE(sdu.User_ID)))-CHARINDEX('-',sdu.User_ID))) as [Username from Navigator] ,case sdu.Application_Name when 'Navigator' then sn.[RESP CODE] else '' end as [Responsibility Code from Navigator] ,case sdu.Application_Name when 'Navigator' then RIGHT(sn.[EMUL STATION], 4) else '' end as [CStat From Navigator] ,ce02.id as JobCode ,case l.id when 'USA800' then 'USA800' when 'admin' then 'Admin' when 'ccc-ulw' then 'CCC-Lawrence' when 'ccc-ury' then 'CCC-Raytown' when 'ccc-usj' then 'CCC-St Joseph' when 'ccc-uwf' then 'CCC-Wichita Falls' else cast(SUBSTRING(l.id, 0, charindex('-', l.id, 0)) as int) end as location ,l.id ,c2.first_name as SubmittedByFirstName ,c2.last_name as SubmittedByLastName ,sn.INST as Institution ,case sdupc.Application_Name when 'PartnerCare' then sdupc.User_ID else '' end as [Partnercare Username] from Case_Table ct left join Case_Type t on t.case_type_pk = ct.case_type_pk left join Case_Category cc on cc.case_category_pk = ct.case_category_pk left join (select max(cas.case_pk) as case_pk, customer_pk from Case_Table cas join Case_Type cat on cas.case_type_pk=cat.case_type_pk where cat.id = 'ASR' group by cas.customer_pk) ce01 on ce01.case_pk = ct.case_pk left join Custom_Entity02 ce02 on ce02.custom_entity02_pk = ct.Virtual_JobCode left join Customer c on ce01.customer_pk = c.customer_pk left join Location l on l.location_pk = c.location_pk right join Customer c2 on c2.customer_pk = ct.installed_by_customer_pk left join Prod.dbo.Staging_DataFeed_Users sdu on sdu.User_Name = c.description and sdu.application_name = 'Navigator' left join Prod.dbo.Staging_DataFeed_Users sdupc on sdupc.User_Name = c.description and sdupc.application_name = 'PartnerCare' left join Prod.dbo.Staging_Navigator sn on sn.NAME = c.description and sn.NAME = sdu.User_Name where t.id='ASR'
When running the query, I get a conversion failed error when converting the
varchar value to data type
Either I am missing something, or there is a better way to do it and I am open to ideas.
The customer is expecting integer in this case (they want it to look like an integer without the leading zeros).
The results of this query are used in a foxtrot script for de-provisioning users from applications and those numbers represent branches in our bank system. The leading zeroes cause issues. If the result is a string, it is ignored.