Need help understanding Qry created by access wizard 1

[dndaughtery]

Hello all, I need to place the following qry into a sproc but SQL Server 2000 doesn't like it and I don't understand the query,particularly the words with double quotes...is this setting a variable? The person who designed it isn't available for me to ask.


The qry:

INSERT INTO Audit_T_EC ( ColumnValueBefore, TableName, TableColumnName, TableKeyName, TableKeyValue, ColumnValueAfter, UpdatedBy, UpdatedDate )
SELECT Card.CH_STATUS, "Card" AS Expr1, "CH_STATUS" AS Expr2, "CardID" AS Expr3, Card.CardID, "P" AS Expr4, Rejects.Employee, Date() AS Expr5
FROM RejectCollections INNER JOIN (Rejects INNER JOIN Card ON Rejects.CardID = Card.CardID) ON RejectCollections.RECV_BY = Rejects.rejectid
WHERE (((Card.CH_STATUS)="T") AND ((RejectCollections.AMOUNT)<=[COLL_FACE]));

 

[DotNetGnat]

the words with double quotes are the hardcoded values...which means your Expr1 will always be equal to Card for all the records and so on...

-DNG

 

[gmmastros]

In SQL Server, you should use single quotes (the apostrophe) instead of double quotes.

It looks like it is 'hard coding' data in to certain fields. The values in double quotes represent the hard coded data.

There is also a 'Date() As Expr5' In SQL Server, you need to use GetDate() instead.

INSERT 
INTO 	Audit_T_EC 
		( 
		ColumnValueBefore, 
		TableName, 
		TableColumnName, 
		TableKeyName, 
		TableKeyValue, 
		ColumnValueAfter, 
		UpdatedBy, 
		UpdatedDate 
		)
SELECT 	Card.CH_STATUS, 
		'Card' AS Expr1, 
		'CH_STATUS' AS Expr2, 
		'CardID' AS Expr3, 
		Card.CardID, 
		'P' AS Expr4, 
		Rejects.Employee, 
		GetDate() AS Expr5
FROM 	RejectCollections 
		INNER JOIN (Rejects INNER JOIN Card ON Rejects.CardID = Card.CardID) 
			ON RejectCollections.RECV_BY = Rejects.rejectid
WHERE 	(((Card.CH_STATUS)="T") AND ((RejectCollections.AMOUNT)<=[COLL_FACE]));

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Ooops, I missed a double quote in the where clause. That should be changed also.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dndaughtery]

Thanks that worked great. But I have one more that is an update. I'm puzzeled because it states "Update RejectCollections" but uses "SET Card.CH_Status". The code is below




UPDATE RejectCollections INNER JOIN (Rejects INNER JOIN Card ON Rejects.CardID = Card.CardID) ON RejectCollections.RECV_BY = Rejects.rejectid SET Card.CH_STATUS = 'P'
WHERE ((Card.CH_STATUS='T') AND ((RejectCollections.AMOUNT)<=[COLL_FACE]));

 

[gmmastros]

Yeah. Access queries are not the same as Sql Queries.

With SQL, you need a from clause in your update statement if you are updating 'from' more than 1 table

try this...

Update	Card
SET 	Card.CH_STATUS = 'P'
From 	RejectCollections 
		INNER JOIN Rejects
			ON RejectCollections.RECV_BY = Rejects.rejectid 
		INNER JOIN Card ON Rejects.CardID = Card.CardID
WHERE ((Card.CH_STATUS='T') AND ((RejectCollections.AMOUNT)<=[COLL_FACE]));

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom