Changing from drop table to truncate

• DHeath

  SSCertifiable

  Points: 5200

  More actions

  September 5, 2019 at 1:51 am

  #3677434

  Thanks for the help and assistance.  I am trying to make some changes but my error is that these cant be bound as alias and i dont understand where i am going wrong( because everything parses okay then doesnt run)... Please give some insight and show correction as well... Thank you

   

  -- Is First Remit

  IF OBJECT_ID('IsFirstRemit_D') IS NOT NULL

  TRUNCATE TABLE IsFirstRemit_D;

  SELECT MIN(a.ClaimDetailID) as ClaimDetailID

  ,a.FacilityPrefix

  ,a.AccountNumber

  ,a.RemitDate

  --INTO IsFirstRemit

  FROM Adena_EDI_16.dbo.Remit835_ClaimDetail_D

  JOIN (SELECT FacilityPrefix, AccountNumber, MIN(RemitDate) as MinRemitDate FROM Adena_EDI_16.dbo.Remit835_ClaimDetail_D GROUP BY FacilityPrefix, AccountNumber) b

  ON a.FacilityPrefix = b.FacilityPrefix AND a.AccountNumber = b.AccountNumber AND a.RemitDate = b.MinRemitDate

  GROUP BY a.FacilityPrefix, a.AccountNumber, a.RemitDate

  DHeath

• Ian Rennie

  SSC Enthusiast

  Points: 100

  More actions

  September 5, 2019 at 7:42 am

  #3677526

  The problem is that you haven't given your first table an alias:

   

  IF OBJECT_ID('IsFirstRemit_D') IS NOT NULL

  TRUNCATE TABLE IsFirstRemit_D;

  SELECT MIN(a.ClaimDetailID) as ClaimDetailID

  ,a.FacilityPrefix

  ,a.AccountNumber

  ,a.RemitDate

  --INTO IsFirstRemit

  FROM Adena_EDI_16.dbo.Remit835_ClaimDetail_D a

  JOIN (SELECT FacilityPrefix, AccountNumber, MIN(RemitDate) as MinRemitDate FROM Adena_EDI_16.dbo.Remit835_ClaimDetail_D GROUP BY FacilityPrefix, AccountNumber) b

  ON a.FacilityPrefix = b.FacilityPrefix AND a.AccountNumber = b.AccountNumber AND a.RemitDate = b.MinRemitDate

  GROUP BY a.FacilityPrefix, a.AccountNumber, a.RemitDate

  Incidentally, I'd get out of the habit of using a,b,c,d etcetera as aliases - the more aliases you use the more confusing it gets. Try and use more meaningful terms. Also, without having DDL posted it's a little difficult to be certain, but could you use ROW_NUMBER to identify the record you want without needing a subquery at all?

• DHeath

  SSCertifiable

  Points: 5200

  More actions

  September 5, 2019 at 7:43 pm

  #3677771

  thank you for the reply and that has me moving in the right direction.... BUT this is the current error

  IF OBJECT_ID('IsFirstRemit_D') IS NOT NULL

  TRUNCATE TABLE IsFirstRemit_D;

  SELECT MIN(a.ClaimDetailID) as ClaimDetailID

  ,a.FacilityPrefix

  ,a.AccountNumber

  ,a.RemitDate

  --INTO IsFirstRemit_D  --> error says this already exist well yes it does its a table that i am truncating and trying to refill...

  Any ideas?

   

  DHeath

• pietlinden

  SSC Guru

  Points: 63594

  More actions

  September 5, 2019 at 7:55 pm

  #3677774

  If the table already exists, use INSERT INTO instead of SELECT INTO (which creates a new table)

• DHeath

  SSCertifiable

  Points: 5200

  More actions

  September 8, 2019 at 4:42 pm

  #3678346

  thank you ...the insert into was where i was wrong...thank you so much

  DHeath