September 5, 2019 at 1:51 am
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
September 5, 2019 at 7:42 am
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?
September 5, 2019 at 7:43 pm
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
September 5, 2019 at 7:55 pm
If the table already exists, use INSERT INTO instead of SELECT INTO (which creates a new table)
September 8, 2019 at 4:42 pm
thank you ...the insert into was where i was wrong...thank you so much
DHeath
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply