How would I go about this ...checking if data exists in a table

• gothaimviii

  SSC-Addicted

  Points: 404

  More actions

  July 16, 2018 at 9:08 am

  #373586

  So I am working within a stored procedure that is loading data into a blank table from table 1. There is one field called "EmpFc"  in the blank table that is either going to be an 'A' or 'C' depending on what is found. Seeing if table 1 SSN = table 2 SSN (PROD data).

  The default is to always be an add "A" in that blank table field, unless the EmpFicaNbr (SSN) is found existing already in another table it is comparing to.

  I have the statement wrote as this, but it just produces an "A" for that field no matter what.

  case when exists(select FICA_NBR from PROD.DB.TABLE2 
      where FICA_NBR = dbo.TABLE1.EmpFicaNbr)
      then 'C'
      else 'A' end + ',' +

  Basically, I am wanting it to put a C in that field if it sees that the SSN already exists in the table it is comparing to. If it does not, it accepts whats already in there.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 16, 2018 at 9:16 am

  #1997692

  gothaimviii - Monday, July 16, 2018 9:08 AM

  So I am working within a stored procedure that is loading data into a blank table from table 1. There is one field called "EmpFc"  in the blank table that is either going to be an 'A' or 'C' depending on what is found. Seeing if table 1 SSN = table 2 SSN (PROD data).

  The default is to always be an add "A" in that blank table field, unless the EmpFicaNbr (SSN) is found existing already in another table it is comparing to.

  I have the statement wrote as this, but it just produces an "A" for that field no matter what.

  case when exists(select FICA_NBR from PROD.DB.TABLE2 
      where FICA_NBR = dbo.TABLE1.EmpFicaNbr)
      then 'C'
      else 'A' end + ',' +

  Basically, I am wanting it to put a C in that field if it sees that the SSN already exists in the table it is comparing to. If it does not, it accepts whats already in there.

  Need at least the rest of that query to be able to see what might be going on.   it would be best to provide table create statements for all the tables referenced in the query, along with SAMPLE data and the expected results based on that sample data.   No real SSNs, please.   Problem could be anything from an issue with how a column is being referenced to a genuine data issue.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• gothaimviii

  SSC-Addicted

  Points: 404

  More actions

  July 16, 2018 at 9:22 am

  #1997694

  this is actually the SP...

  you can see its taking data from a table, and inputting data into another table based on situations.

  Just basically manipulating the data that is going into the new table, its going to be used to produce a CSV file.

  USE [InterfaceDEV];
  GO
  SET ANSI_NULLS ON;
  GO
  SET QUOTED_IDENTIFIER ON;
  GO
  ALTER PROCEDURE [dbo].[iCIMS_HIRES_SP] AS

  ----delete from dbo.iCIMS_HIRES_OUT

  INSERT INTO dbo.iCIMS_HIRES_OUT
  SELECT '1' as SORT_ORDER,
          'EmpFc,EmpCompany,EmpEmployee,EmpLastName,EmpFirstName,EmpMiddleName,EmpFicaNbr,EmpEmpStatus,' +
          'EmpDateHired,EmpAdjHireDate,EmpProcessLevel,EmpJobCode,PemLocatCode,EmpUnionCode,EmpSalaryClass,' +
          'EmpPayFrequency,EmpPayRate,EmpAnnualHours,EmpNbrFte,EmpOtPlanCode,EmpAutoDeposit,EmpHmDistCo,' +
          'EmpHmAcctUnit,EmpHmAccount,PemBirthdate,PemTrueMarStat,PemSex,PemEeoClass,EmpAddr1,EmpCity,EmpState,' +
          'EmpZip,EmpCounty,PemHmPhoneNbr,PemBenDateC,PemSmoker,PemClockNbr,PepPosLevel,PepEffectDate' as F1 -- HEADER
  UNION
  SELECT '2' as SORT_ORDER,
          case when exists(select * from PROD.EMPLOYEE
      where FICA_NBR = dbo.iCIMS_HIRES.EmpFicaNbr)
      then 'C'
      else replace(upper (rtrim(EmpFc)), '"', '') end + ',' +
          replace(rtrim(EmpCompany), '"', '') + ',' +
          replace(rtrim(EmpEmployee), '"', '') + ',' +
          replace(upper(rtrim(EmpLastName)), '"', '') + ',' +
          replace(upper(rtrim(EmpFirstName)), '"', '') + ',' +
          replace(upper(rtrim(EmpMiddleName)), '"', '') + ',' +
          replace(rtrim(EmpFicaNbr), '"', '') + ',' +
          replace(upper(rtrim(EmpEmpStatus)), '"', '') + ',' +
          replace(rtrim(EmpDateHired), '"', '') + ',' +
          replace(rtrim(EmpAdjHireDate), '"', '') + ',' +
  --        rtrim(EmpProcessLevel) + ',' +
    case when replace(rtrim(EmpProcessLevel), '"', '') = '001IL'
      then '001'
      else replace(rtrim(EmpProcessLevel), '"', '') end + ',' +
          replace(rtrim(EmpJobCode), '"', '') + ',' +
          replace(rtrim(PemLocatCode), '"', '') + ',' +
          replace(rtrim(EmpUnionCode), '"', '') + ',' +
          replace(rtrim(EmpSalaryClass), '"', '') + ',' +
          replace(rtrim(EmpPayFrequency), '"', '') + ',' +
          replace(rtrim(EmpPayRate), '"', '') + ',' +
          replace(rtrim(EmpAnnualHours), '"', '') + ',' +
          replace(rtrim(EmpNbrFte), '"', '') + ',' +
          replace(rtrim(EmpOtPlanCode), '"', '') + ',' +
          replace(upper(rtrim(EmpAutoDeposit)), '"', '') + ',' +
  --        replace(rtrim(EmpHmDistCo), '"', '') + ',' +
    case when replace(rtrim(EmpHmDistCo), '"', '') = '001IL' --or
  --    replace(rtrim(EmpHmDistCo), '"', '') = '001'
      then '1'
      else replace(rtrim(EmpHmDistCo), '"', '') end + ',' +
  --        replace(rtrim(EmpHmAcctUnit), '"', '') + ',' +
    case when replace(rtrim(EmpHmAcctUnit), '"', '') = '001IL' or
       replace(rtrim(EmpHmAcctUnit), '"', '') = '001'
      then '000001'
      else replace(rtrim(EmpHmAcctUnit), '"', '') end + ',' +
          replace(rtrim(EmpHmAccount), '"', '') + ',' +
          replace(rtrim(PemBirthdate), '"', '') + ',' +
          replace(upper(rtrim(PemTrueMarStat)), '"', '') + ',' +
          replace(upper(rtrim(PemSex)), '"', '') + ',' +
          replace(upper(rtrim(PemEeoClass)), '"', '') + ',' +
          replace(upper(rtrim(EmpAddr1)), '"', '') + ',' +
          replace(upper(rtrim(EmpCity)), '"', '') + ',' +
          replace(upper(rtrim(EmpState)), '"', '') + ',' +
          replace(rtrim(EmpZip), '"', '') + ',' +
          replace(upper(rtrim(EmpCounty)), '"', '') + ',' +
          replace(rtrim(PemHmPhoneNbr), '"', '') + ',' +
          replace(rtrim(PemBenDateC), '"', '') + ',' +
          replace(upper(rtrim(PemSmoker)), '"', '') + ',' +
          replace(rtrim(PemClockNbr), '"', '') + ',' +
          replace(rtrim(PepPosLevel), '"', '') + ',' +
          replace(rtrim(PepEffectDate), '"', '')
          as F1
          from iCIMS_HIRES
          
          select F1 from dbo.iCIMS_HIRES_OUT
  order by SORT_ORDER
  GO

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 16, 2018 at 9:40 am

  #1997696

  gothaimviii - Monday, July 16, 2018 9:22 AM

  this is actually the SP...

  you can see its taking data from a table, and inputting data into another table based on situations.

  Just basically manipulating the data that is going into the new table, its going to be used to produce a CSV file.

  USE [InterfaceDEV];
  GO
  SET ANSI_NULLS ON;
  GO
  SET QUOTED_IDENTIFIER ON;
  GO
  ALTER PROCEDURE [dbo].[iCIMS_HIRES_SP] AS

  ----delete from dbo.iCIMS_HIRES_OUT

  INSERT INTO dbo.iCIMS_HIRES_OUT
  SELECT '1' as SORT_ORDER,
          'EmpFc,EmpCompany,EmpEmployee,EmpLastName,EmpFirstName,EmpMiddleName,EmpFicaNbr,EmpEmpStatus,' +
          'EmpDateHired,EmpAdjHireDate,EmpProcessLevel,EmpJobCode,PemLocatCode,EmpUnionCode,EmpSalaryClass,' +
          'EmpPayFrequency,EmpPayRate,EmpAnnualHours,EmpNbrFte,EmpOtPlanCode,EmpAutoDeposit,EmpHmDistCo,' +
          'EmpHmAcctUnit,EmpHmAccount,PemBirthdate,PemTrueMarStat,PemSex,PemEeoClass,EmpAddr1,EmpCity,EmpState,' +
          'EmpZip,EmpCounty,PemHmPhoneNbr,PemBenDateC,PemSmoker,PemClockNbr,PepPosLevel,PepEffectDate' as F1 -- HEADER
  UNION
  SELECT '2' as SORT_ORDER,
          case when exists(select * from PROD.EMPLOYEE
      where FICA_NBR = dbo.iCIMS_HIRES.EmpFicaNbr)
      then 'C'
      else replace(upper (rtrim(EmpFc)), '"', '') end + ',' +
          replace(rtrim(EmpCompany), '"', '') + ',' +
          replace(rtrim(EmpEmployee), '"', '') + ',' +
          replace(upper(rtrim(EmpLastName)), '"', '') + ',' +
          replace(upper(rtrim(EmpFirstName)), '"', '') + ',' +
          replace(upper(rtrim(EmpMiddleName)), '"', '') + ',' +
          replace(rtrim(EmpFicaNbr), '"', '') + ',' +
          replace(upper(rtrim(EmpEmpStatus)), '"', '') + ',' +
          replace(rtrim(EmpDateHired), '"', '') + ',' +
          replace(rtrim(EmpAdjHireDate), '"', '') + ',' +
  --        rtrim(EmpProcessLevel) + ',' +
    case when replace(rtrim(EmpProcessLevel), '"', '') = '001IL'
      then '001'
      else replace(rtrim(EmpProcessLevel), '"', '') end + ',' +
          replace(rtrim(EmpJobCode), '"', '') + ',' +
          replace(rtrim(PemLocatCode), '"', '') + ',' +
          replace(rtrim(EmpUnionCode), '"', '') + ',' +
          replace(rtrim(EmpSalaryClass), '"', '') + ',' +
          replace(rtrim(EmpPayFrequency), '"', '') + ',' +
          replace(rtrim(EmpPayRate), '"', '') + ',' +
          replace(rtrim(EmpAnnualHours), '"', '') + ',' +
          replace(rtrim(EmpNbrFte), '"', '') + ',' +
          replace(rtrim(EmpOtPlanCode), '"', '') + ',' +
          replace(upper(rtrim(EmpAutoDeposit)), '"', '') + ',' +
  --        replace(rtrim(EmpHmDistCo), '"', '') + ',' +
    case when replace(rtrim(EmpHmDistCo), '"', '') = '001IL' --or
  --    replace(rtrim(EmpHmDistCo), '"', '') = '001'
      then '1'
      else replace(rtrim(EmpHmDistCo), '"', '') end + ',' +
  --        replace(rtrim(EmpHmAcctUnit), '"', '') + ',' +
    case when replace(rtrim(EmpHmAcctUnit), '"', '') = '001IL' or
       replace(rtrim(EmpHmAcctUnit), '"', '') = '001'
      then '000001'
      else replace(rtrim(EmpHmAcctUnit), '"', '') end + ',' +
          replace(rtrim(EmpHmAccount), '"', '') + ',' +
          replace(rtrim(PemBirthdate), '"', '') + ',' +
          replace(upper(rtrim(PemTrueMarStat)), '"', '') + ',' +
          replace(upper(rtrim(PemSex)), '"', '') + ',' +
          replace(upper(rtrim(PemEeoClass)), '"', '') + ',' +
          replace(upper(rtrim(EmpAddr1)), '"', '') + ',' +
          replace(upper(rtrim(EmpCity)), '"', '') + ',' +
          replace(upper(rtrim(EmpState)), '"', '') + ',' +
          replace(rtrim(EmpZip), '"', '') + ',' +
          replace(upper(rtrim(EmpCounty)), '"', '') + ',' +
          replace(rtrim(PemHmPhoneNbr), '"', '') + ',' +
          replace(rtrim(PemBenDateC), '"', '') + ',' +
          replace(upper(rtrim(PemSmoker)), '"', '') + ',' +
          replace(rtrim(PemClockNbr), '"', '') + ',' +
          replace(rtrim(PepPosLevel), '"', '') + ',' +
          replace(rtrim(PepEffectDate), '"', '')
          as F1
          from iCIMS_HIRES
          
          select F1 from dbo.iCIMS_HIRES_OUT
  order by SORT_ORDER
  GO

  Let's try giving your primary source table an alias of "H", and then applying that alias everywhere.   Try this on for size:
  USE InterfaceDEV;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

  ALTER PROCEDURE dbo.iCIMS_HIRES_SP
AS

  --DELETE FROM dbo.iCIMS_HIRES_OUT
INSERT INTO dbo.iCIMS_HIRES_OUT (F1)
SELECT '1' AS SORT_ORDER,
'EmpFc,EmpCompany,EmpEmployee,EmpLastName,EmpFirstName,EmpMiddleName,EmpFicaNbr,EmpEmpStatus,' +
'EmpDateHired,EmpAdjHireDate,EmpProcessLevel,EmpJobCode,PemLocatCode,EmpUnionCode,EmpSalaryClass,' +
'EmpPayFrequency,EmpPayRate,EmpAnnualHours,EmpNbrFte,EmpOtPlanCode,EmpAutoDeposit,EmpHmDistCo,' +
'EmpHmAcctUnit,EmpHmAccount,PemBirthdate,PemTrueMarStat,PemSex,PemEeoClass,EmpAddr1,EmpCity,EmpState,' +
'EmpZip,EmpCounty,PemHmPhoneNbr,PemBenDateC,PemSmoker,PemClockNbr,PepPosLevel,PepEffectDate' AS F1 -- HEADER
UNION
SELECT '2' as SORT_ORDER,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM PROD.EMPLOYEE AS E
            WHERE E.FICA_NBR = H.EmpFicaNbr
            ) THEN 'C'
        ELSE REPLACE(UPPER(RTRIM(H.EmpFc)), '"', '')
    END + ',' +
    REPLACE(RTRIM(H.EmpCompany), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpEmployee), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpLastName)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpFirstName)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpMiddleName)), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpFicaNbr), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpEmpStatus)), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpDateHired), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpAdjHireDate), '"', '') + ',' +
    -- RTRIM(H.EmpProcessLevel) + ',' +
    CASE
        WHEN REPLACE(RTRIM(H.EmpProcessLevel), '"', '') = '001IL' THEN '001'
        ELSE REPLACE(RTRIM(H.EmpProcessLevel), '"', '')
    END + ',' +
    REPLACE(RTRIM(H.EmpJobCode), '"', '') + ',' +
    REPLACE(RTRIM(H.PemLocatCode), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpUnionCode), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpSalaryClass), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpPayFrequency), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpPayRate), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpAnnualHours), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpNbrFte), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpOtPlanCode), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpAutoDeposit)), '"', '') + ',' +
    -- REPLACE(RTRIM(H.EmpHmDistCo), '"', '') + ',' +
    CASE
        WHEN REPLACE(RTRIM(H.EmpHmDistCo), '"', '') = '001IL' THEN '1'
        --    or REPLACE(RTRIM(H.EmpHmDistCo), '"', '') = '001'
        ELSE REPLACE(RTRIM(H.EmpHmDistCo), '"', '')
    END + ',' +
    -- REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '') + ',' +
    CASE
        WHEN REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '') = '001IL' OR
            REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '') = '001' THEN '000001'
        ELSE REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '')
    END + ',' +
    REPLACE(RTRIM(H.EmpHmAccount), '"', '') + ',' +
    REPLACE(RTRIM(H.PemBirthdate), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.PemTrueMarStat)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.PemSex)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.PemEeoClass)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpAddr1)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpCity)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpState)), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpZip), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpCounty)), '"', '') + ',' +
    REPLACE(RTRIM(H.PemHmPhoneNbr), '"', '') + ',' +
    REPLACE(RTRIM(H.PemBenDateC), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.PemSmoker)), '"', '') + ',' +
    REPLACE(RTRIM(H.PemClockNbr), '"', '') + ',' +
    REPLACE(RTRIM(H.PepPosLevel), '"', '') + ',' +
    REPLACE(RTRIM(H.PepEffectDate), '"', '') AS F1
FROM iCIMS_HIRES AS H;

  SELECT HO.F1
FROM dbo.iCIMS_HIRES_OUT AS HO
ORDER BY HO.SORT_ORDER;
GO

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• gothaimviii

  SSC-Addicted

  Points: 404

  More actions

  July 16, 2018 at 10:01 am

  #1997702

  sgmunson - Monday, July 16, 2018 9:40 AM

  gothaimviii - Monday, July 16, 2018 9:22 AM

  this is actually the SP...

  you can see its taking data from a table, and inputting data into another table based on situations.

  Just basically manipulating the data that is going into the new table, its going to be used to produce a CSV file.

  USE [InterfaceDEV];
  GO
  SET ANSI_NULLS ON;
  GO
  SET QUOTED_IDENTIFIER ON;
  GO
  ALTER PROCEDURE [dbo].[iCIMS_HIRES_SP] AS

  ----delete from dbo.iCIMS_HIRES_OUT

  INSERT INTO dbo.iCIMS_HIRES_OUT
  SELECT '1' as SORT_ORDER,
          'EmpFc,EmpCompany,EmpEmployee,EmpLastName,EmpFirstName,EmpMiddleName,EmpFicaNbr,EmpEmpStatus,' +
          'EmpDateHired,EmpAdjHireDate,EmpProcessLevel,EmpJobCode,PemLocatCode,EmpUnionCode,EmpSalaryClass,' +
          'EmpPayFrequency,EmpPayRate,EmpAnnualHours,EmpNbrFte,EmpOtPlanCode,EmpAutoDeposit,EmpHmDistCo,' +
          'EmpHmAcctUnit,EmpHmAccount,PemBirthdate,PemTrueMarStat,PemSex,PemEeoClass,EmpAddr1,EmpCity,EmpState,' +
          'EmpZip,EmpCounty,PemHmPhoneNbr,PemBenDateC,PemSmoker,PemClockNbr,PepPosLevel,PepEffectDate' as F1 -- HEADER
  UNION
  SELECT '2' as SORT_ORDER,
          case when exists(select * from PROD.EMPLOYEE
      where FICA_NBR = dbo.iCIMS_HIRES.EmpFicaNbr)
      then 'C'
      else replace(upper (rtrim(EmpFc)), '"', '') end + ',' +
          replace(rtrim(EmpCompany), '"', '') + ',' +
          replace(rtrim(EmpEmployee), '"', '') + ',' +
          replace(upper(rtrim(EmpLastName)), '"', '') + ',' +
          replace(upper(rtrim(EmpFirstName)), '"', '') + ',' +
          replace(upper(rtrim(EmpMiddleName)), '"', '') + ',' +
          replace(rtrim(EmpFicaNbr), '"', '') + ',' +
          replace(upper(rtrim(EmpEmpStatus)), '"', '') + ',' +
          replace(rtrim(EmpDateHired), '"', '') + ',' +
          replace(rtrim(EmpAdjHireDate), '"', '') + ',' +
  --        rtrim(EmpProcessLevel) + ',' +
    case when replace(rtrim(EmpProcessLevel), '"', '') = '001IL'
      then '001'
      else replace(rtrim(EmpProcessLevel), '"', '') end + ',' +
          replace(rtrim(EmpJobCode), '"', '') + ',' +
          replace(rtrim(PemLocatCode), '"', '') + ',' +
          replace(rtrim(EmpUnionCode), '"', '') + ',' +
          replace(rtrim(EmpSalaryClass), '"', '') + ',' +
          replace(rtrim(EmpPayFrequency), '"', '') + ',' +
          replace(rtrim(EmpPayRate), '"', '') + ',' +
          replace(rtrim(EmpAnnualHours), '"', '') + ',' +
          replace(rtrim(EmpNbrFte), '"', '') + ',' +
          replace(rtrim(EmpOtPlanCode), '"', '') + ',' +
          replace(upper(rtrim(EmpAutoDeposit)), '"', '') + ',' +
  --        replace(rtrim(EmpHmDistCo), '"', '') + ',' +
    case when replace(rtrim(EmpHmDistCo), '"', '') = '001IL' --or
  --    replace(rtrim(EmpHmDistCo), '"', '') = '001'
      then '1'
      else replace(rtrim(EmpHmDistCo), '"', '') end + ',' +
  --        replace(rtrim(EmpHmAcctUnit), '"', '') + ',' +
    case when replace(rtrim(EmpHmAcctUnit), '"', '') = '001IL' or
       replace(rtrim(EmpHmAcctUnit), '"', '') = '001'
      then '000001'
      else replace(rtrim(EmpHmAcctUnit), '"', '') end + ',' +
          replace(rtrim(EmpHmAccount), '"', '') + ',' +
          replace(rtrim(PemBirthdate), '"', '') + ',' +
          replace(upper(rtrim(PemTrueMarStat)), '"', '') + ',' +
          replace(upper(rtrim(PemSex)), '"', '') + ',' +
          replace(upper(rtrim(PemEeoClass)), '"', '') + ',' +
          replace(upper(rtrim(EmpAddr1)), '"', '') + ',' +
          replace(upper(rtrim(EmpCity)), '"', '') + ',' +
          replace(upper(rtrim(EmpState)), '"', '') + ',' +
          replace(rtrim(EmpZip), '"', '') + ',' +
          replace(upper(rtrim(EmpCounty)), '"', '') + ',' +
          replace(rtrim(PemHmPhoneNbr), '"', '') + ',' +
          replace(rtrim(PemBenDateC), '"', '') + ',' +
          replace(upper(rtrim(PemSmoker)), '"', '') + ',' +
          replace(rtrim(PemClockNbr), '"', '') + ',' +
          replace(rtrim(PepPosLevel), '"', '') + ',' +
          replace(rtrim(PepEffectDate), '"', '')
          as F1
          from iCIMS_HIRES
          
          select F1 from dbo.iCIMS_HIRES_OUT
  order by SORT_ORDER
  GO

  Let's try giving your primary source table an alias of "H", and then applying that alias everywhere.   Try this on for size:
  USE InterfaceDEV;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

  ALTER PROCEDURE dbo.iCIMS_HIRES_SP
AS

  --DELETE FROM dbo.iCIMS_HIRES_OUT
INSERT INTO dbo.iCIMS_HIRES_OUT (F1)
SELECT '1' AS SORT_ORDER,
'EmpFc,EmpCompany,EmpEmployee,EmpLastName,EmpFirstName,EmpMiddleName,EmpFicaNbr,EmpEmpStatus,' +
'EmpDateHired,EmpAdjHireDate,EmpProcessLevel,EmpJobCode,PemLocatCode,EmpUnionCode,EmpSalaryClass,' +
'EmpPayFrequency,EmpPayRate,EmpAnnualHours,EmpNbrFte,EmpOtPlanCode,EmpAutoDeposit,EmpHmDistCo,' +
'EmpHmAcctUnit,EmpHmAccount,PemBirthdate,PemTrueMarStat,PemSex,PemEeoClass,EmpAddr1,EmpCity,EmpState,' +
'EmpZip,EmpCounty,PemHmPhoneNbr,PemBenDateC,PemSmoker,PemClockNbr,PepPosLevel,PepEffectDate' AS F1 -- HEADER
UNION
SELECT '2' as SORT_ORDER,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM PROD.EMPLOYEE AS E
            WHERE E.FICA_NBR = H.EmpFicaNbr
            ) THEN 'C'
        ELSE REPLACE(UPPER(RTRIM(H.EmpFc)), '"', '')
    END + ',' +
    REPLACE(RTRIM(H.EmpCompany), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpEmployee), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpLastName)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpFirstName)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpMiddleName)), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpFicaNbr), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpEmpStatus)), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpDateHired), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpAdjHireDate), '"', '') + ',' +
    -- RTRIM(H.EmpProcessLevel) + ',' +
    CASE
        WHEN REPLACE(RTRIM(H.EmpProcessLevel), '"', '') = '001IL' THEN '001'
        ELSE REPLACE(RTRIM(H.EmpProcessLevel), '"', '')
    END + ',' +
    REPLACE(RTRIM(H.EmpJobCode), '"', '') + ',' +
    REPLACE(RTRIM(H.PemLocatCode), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpUnionCode), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpSalaryClass), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpPayFrequency), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpPayRate), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpAnnualHours), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpNbrFte), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpOtPlanCode), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpAutoDeposit)), '"', '') + ',' +
    -- REPLACE(RTRIM(H.EmpHmDistCo), '"', '') + ',' +
    CASE
        WHEN REPLACE(RTRIM(H.EmpHmDistCo), '"', '') = '001IL' THEN '1'
        --    or REPLACE(RTRIM(H.EmpHmDistCo), '"', '') = '001'
        ELSE REPLACE(RTRIM(H.EmpHmDistCo), '"', '')
    END + ',' +
    -- REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '') + ',' +
    CASE
        WHEN REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '') = '001IL' OR
            REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '') = '001' THEN '000001'
        ELSE REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '')
    END + ',' +
    REPLACE(RTRIM(H.EmpHmAccount), '"', '') + ',' +
    REPLACE(RTRIM(H.PemBirthdate), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.PemTrueMarStat)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.PemSex)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.PemEeoClass)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpAddr1)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpCity)), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpState)), '"', '') + ',' +
    REPLACE(RTRIM(H.EmpZip), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.EmpCounty)), '"', '') + ',' +
    REPLACE(RTRIM(H.PemHmPhoneNbr), '"', '') + ',' +
    REPLACE(RTRIM(H.PemBenDateC), '"', '') + ',' +
    REPLACE(UPPER(RTRIM(H.PemSmoker)), '"', '') + ',' +
    REPLACE(RTRIM(H.PemClockNbr), '"', '') + ',' +
    REPLACE(RTRIM(H.PepPosLevel), '"', '') + ',' +
    REPLACE(RTRIM(H.PepEffectDate), '"', '') AS F1
FROM iCIMS_HIRES AS H;

  SELECT HO.F1
FROM dbo.iCIMS_HIRES_OUT AS HO
ORDER BY HO.SORT_ORDER;
GO

  I tried this as stated. when I leave the (F1) on the insert statement, i get an error of Lookup Error - SQL Server Database Error: The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

• gothaimviii

  SSC-Addicted

  Points: 404

  More actions

  July 16, 2018 at 10:06 am

  #1997704

  i figured it out...sort order was not defined in the insert statement.

  Anyway, I tried the whole alias thing and your case statement, and I still get A as the value even though it should be a C for the field I am testing.

• gothaimviii

  SSC-Addicted

  Points: 404

  More actions

  July 16, 2018 at 10:14 am

  #1997706

  I think I see what is going on. The table I am comparing to does not have "" in each field.

  The table I am pulling data from has "" in each field. (hence the reason I am removing the "" in each field I am inserting into the new table)

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  July 16, 2018 at 3:51 pm

  #1997750

  gothaimviii - Monday, July 16, 2018 10:14 AM

  I think I see what is going on. The table I am comparing to does not have "" in each field.

  The table I am pulling data from has "" in each field. (hence the reason I am removing the "" in each field I am inserting into the new table)

  Heh... cool.  Now all you have to do is to ...
  1.  Stop transmitting/receiving SSNs in clear text files.
  2.  Stop storing SSNs in clear text tables.

  --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)