Update Query error

[jeffsturgeon2002]

I am getting the following error on this Update Query and cant quite get it right ... any help is greatly appreciated.

Server: Msg 107, Level 16, State 2, Line 61
The column prefix 'ppa' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 101
The column prefix 'ppa' does not match with a table name or alias name used in the query.

The Code

/* Insurance Balance Write-Off Update Query 1/20/2006*/
BEGIN TRAN
SET NOCOUNT ON

DECLARE @PaymentMethodId int, @PatientProfileID int
DECLARE @VisitTransactionsId int
DECLARE @TransactionsId int
DECLARE @PayerName varchar(50)
DECLARE @BatchId int
DECLARE @AdjustmentTypeMId int
DECLARE @AdjTypePtID int
DECLARE @Amount money

SELECT @PayerName = 'Balance Write-Off'
SELECT @BatchId = 23055
SELECT @AdjustmentTypeMID = 2
DECLARE @Source int
DECLARE @pvaPatBalance money
DECLARE @pvaInsBalance money



DECLARE @Visit int
DECLARE @CarrierId int

DECLARE c_visit CURSOR STATIC FORWARD_ONLY LOCAL FOR
	SELECT DISTINCT pv.PatientVisitId, pv.PrimaryInsuranceCarriersId, pv.PatientProfileID, ppa.InsBalance, ppa.PatBalance
	FROM PatientProfile p 
	JOIN PatientVisit pv ON pv.PatientProfileId = p.PatientProfileId
	JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
	JOIN PatientVisitAgg pva ON pv.PatientVisitID = pva.PatientVisitID
	JOIN PatientVisitProcsAgg ppa ON pv.PatientVisitId = ppa.PatientVisitId
	JOIN PatientVisitProcs pvp ON ppa.PatientVisitProcsID = pvp.PatientVisitProcsID
WHERE 
(
(1=1 AND ic.InsuranceCarriersID IN (295)) OR
(1=2 AND (NULL IS NULL OR ic.PolicyTypeMId IN (NULL))) OR
(1=3 AND pv.VisitOwnerMID = NULL) 
)
AND  
pvp.DateofEntry BETWEEN '01/01/2001' AND '11/30/2006' AND 
(NULL IS NULL OR pv.companyid  in (NULL)) AND
(NULL IS NULL OR pvp.proceduresid  in (NULL)) AND
pvp.Voided IS NULL
	GROUP BY pv.PatientVisitId, pv.PrimaryInsuranceCarriersId,pv.PatientProfileID, ppa.InsBalance,ppa.PatBalance
HAVING
(CASE 3
  when 1 then SUM(ppa.InsBalance+ppa.PatBalance) 
  when 2 then SUM(ppa.InsBalance)
  when 3 then SUM(ppa.PatBalance)
end > 0) OR (SUM(ppa.InsBalance+ppa.PatBalance) = 0 AND ppa.InsBalance <> 0) 
OR (0 = 1 AND (ppa.InsBalance <0 OR ppa.PatBalance < 0))

SELECT 	@Source = 3
  

OPEN c_visit
FETCH NEXT FROM c_visit INTO @Visit, @CarrierId, @PatientProfileID, @pvaInsBalance, @pvaPatBalance
WHILE (@@fetch_status<>-1)
BEGIN
	IF @Source IN (2,1) OR (@Source = 1 AND @pvaInsBalance <> 0) OR (0 = 1 AND ppa.InsBalance <0 )
	BEGIN
		INSERT	PaymentMethod(	Source, PayerName, PayerId, PayerType, BatchId, PaymentType, 
						Created, CreatedBy, LastModified, LastModifiedBy)
		VALUES	(2 /* Ins */, @PayerName, @CarrierId, 'Insurance', @BatchId, 6 /* Conveyance */, 
		 GetDate(), dbo.GetLogonID(), GetDate(), dbo.GetLogonID())

		SELECT	@PaymentMethodId = scope_identity()

		INSERT	VisitTransactions(	PatientVisitId, PaymentMethodId, 
								Payments, Adjustments, Transfers,
								Created, CreatedBy, LastModified, LastModifiedBy)
		VALUES					 (	@Visit, @PaymentMethodId,
								0, 0, 0,
								GetDate(), dbo.GetLogonID(), GetDate(), dbo.GetLogonID())
	
		SELECT	@VisitTransactionsId = scope_identity()

		INSERT	Transactions(	VisitTransactionsId, Type, Action, ActionTypeMId, Name,
							Created, CreatedBy, LastModified, LastModifiedBy)
		VALUES				(	@VisitTransactionsId, 'A', 'A', @AdjustmentTypeMId, 'Adjustment',
							GetDate(), dbo.GetLogonID(), GetDate(), dbo.GetLogonID())

		SELECT	@TransactionsId = scope_identity()

		INSERT	TransactionDistributions(	PatientVisitProcsId, TransactionsId, Amount, 
					Created, CreatedBy, LastModified, LastModifiedBy)											
		SELECT	pvp.PatientVisitProcsId, @TransactionsId, pvpa.InsBalance,	GetDate(), dbo.GetLogonID(), GetDate(), dbo.GetLogonID()
		FROM	PatientVisitProcs pvp 
		INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsId = pvpa.PatientVisitProcsId
		WHERE	pvp.PatientVisitId = @Visit AND pvpa.InsBalance <> 0 AND
		pvp.DateofEntry BETWEEN '01/01/2001' AND '11/30/2006' AND 
		(NULL IS NULL OR pvp.proceduresid  in (NULL)) AND
		pvp.Voided IS NULL

		--Set the correct bill status
		
		EXEC SetBillStatus @Visit
	END

	IF @Source IN (3,1) OR (@Source = 1 AND @pvaPatBalance <> 0) OR (0 = 1 AND ppa.PatBalance <0 )

	BEGIN
		INSERT	PaymentMethod(	Source, PayerName, PayerId, PayerType, BatchId, PaymentType, 
						Created, CreatedBy, LastModified, LastModifiedBy)
		VALUES	(1 /* Patient */, 'Contractual', @PatientProfileId, 'Patient', @BatchId, 6 /* Conveyance */, 
		 GetDate(), dbo.GetLogonID(), GetDate(), dbo.GetLogonID())

		SELECT	@PaymentMethodId = scope_identity()

		INSERT	VisitTransactions(	PatientVisitId, PaymentMethodId, 
								Payments, Adjustments, Transfers,
								Created, CreatedBy, LastModified, LastModifiedBy)
		VALUES					 (	@Visit, @PaymentMethodId,
								0, 0, 0,
								GetDate(), dbo.GetLogonID(), GetDate(), dbo.GetLogonID())
	
		SELECT	@VisitTransactionsId = scope_identity()

		INSERT	Transactions(	VisitTransactionsId, Type, Action, ActionTypeMId, Name,
							Created, CreatedBy, LastModified, LastModifiedBy)
		VALUES				(	@VisitTransactionsId, 'A', 'A', @AdjTypePtID, 'Adjustment',
							GetDate(), dbo.GetLogonID(), GetDate(), dbo.GetLogonID())

		SELECT	@TransactionsId = scope_identity()

		INSERT	TransactionDistributions(	PatientVisitProcsId, TransactionsId, Amount, 
					Created, CreatedBy, LastModified, LastModifiedBy)											
		SELECT	pvp.PatientVisitProcsId, @TransactionsId, pvpa.PatBalance,	GetDate(), dbo.GetLogonID(), GetDate(), dbo.GetLogonID()
		FROM	PatientVisitProcs pvp 
		INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsId = pvpa.PatientVisitProcsId
		WHERE	pvp.PatientVisitId = @Visit  AND pvpa.PatBalance <> 0 AND
		pvp.DateofEntry BETWEEN '01/01/2001' AND '11/30/2006' AND 
		(NULL IS NULL OR pvp.proceduresid  in (NULL)) AND
		pvp.Voided IS NULL

		--Set the correct bill status
		EXEC SetBillStatus @Visit
	END
	IF @Source = 1
		UPDATE PatientVisit SET Billstatus = 12 WHERE PatientVisitID = @Visit

		FETCH NEXT FROM c_visit INTO @Visit, @CarrierId, @PatientProfileID,@pvaInsBalance, @pvaPatBalance
END
DEALLOCATE c_visit


COMMIT TRAN

 

[SQLDenis]

you problem is here

IF @Source IN (3,1) OR (@Source = 1 AND @pvaPatBalance <> 0) OR (0 = 1 AND ppa.PatBalance <0 )

and here

IF @Source IN (2,1) OR (@Source = 1 AND @pvaInsBalance <> 0) OR (0 = 1 AND ppa.InsBalance <0 )

lines 101 and 61

if you double-click on the error message it should jump to the line in question

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLDenis]

what is more interesting is this

WHERE 
(
(1=1 AND ic.InsuranceCarriersID IN (295)) OR
(1=2 AND (NULL IS NULL OR ic.PolicyTypeMId IN (NULL))) OR
(1=3 AND pv.VisitOwnerMID = NULL) 
)

how can 1 =2 or 1=3 ever be true????

and what the hell is this

NULL IS NULL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Catadmin]

Denis,

If I'm not mistaken, I believe the 1=1, 1=2 and 1=3 are field references from the SELECT statement. So if the first field in the SELECT = 1 or the first field = 2 or the first field = 3...

I've seen this type of coding before. I personally dislike it because it can be so confusing, but it seems to work for some people.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLDenis]

that would be invalid syntax

try running this

select au_id as 1
from authors

you would have to do this

select au_id as [1]
from authors



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/