Changing from drop table to truncate

Question

Post reply

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ian Rennie SSC Enthusiast Points: 100 More actions · Answer 1

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 · Answer 2

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: 63417 More actions · Answer 3

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

DHeath SSCertifiable Points: 5200 More actions · Answer 4

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

DHeath