Home > IT Answers > SQL Server > dynamic temporary table variable usage
PaminBuckeyeLand
15 pts.
0
Q:
dynamic temporary table variable usage
Dynamic SQL, INNER JOIN, SQL tables, SQL variables
We have an sqr (running against a MS SQL Server DB), that references a dynamic temporary table variable in several begin-sql and begin-select statements. I am getting a syntax error in one begin-select statement where I can't resolve the problem. It occurs after the INNER JOIN on the [$Temp_Final_IDlist] temp table. It appears as if SQR doesn't see the replacement value for [$Temp_Final_IDlist].
I've already made sure that the variable is not empty.
Is there a limit in SQR on how many dynamic variables can be used in one program?

code:
Cursor #28:
SQL = SELECT SEV_21A.ID,
SEV_21A.HouseHoldKey,
SEV_21A.HouseHoldCombineFlag,
SEV_21A.OrderSeqNbrStd,
SEV_21A.PrefixRankAdjusted,
SEV_21A.SalutationPrefixCode,
SEV_21A.PrefixFormalSal,
SEV_21A.PrefixFormalSalPunc,
SEV_21A.NickName,
SEV_21A.FirstName,
SEV_21A.FirstNamePunc,
SEV_21A.LastName,
SEV_21A.Gender,

SEV_21B.ID,
SEV_21B.HouseHoldKey,
SEV_21B.HouseHoldCombineFlag,
SEV_21B.OrderSeqNbrStd,
SEV_21B.PrefixRankAdjusted,
SEV_21B.SalutationPrefixCode,
SEV_21B.PrefixFormalSal,
SEV_21B.PrefixFormalSalPunc,
SEV_21B.NickName,
SEV_21B.FirstName,
SEV_21B.FirstNamePunc,
SEV_21B.LastName,
SEV_21B.Gender

FROM Salutation_Elements_View SEV_21A
INNER JOIN
[$Temp_Final_IDList] TFI_21A ON SEV_21A.ID = TFI_21A.ID
AND TFI_21A.DropIDFlag = 'N'
INNER JOIN
Salutation_Elements_View SEV_21B ON SEV_21A.HouseHoldKey = SEV_21B.HouseHoldKey
AND SEV_21A.HouseHoldCombineFlag = SEV_21B.HouseHoldCombineFlag
AND SEV_21A.EntityType = SEV_21B.EntityType
AND SEV_21A.SalutationType = SEV_21B.SalutationType
AND SEV_21A.OrderSeqNbrStd <> SEV_21B.OrderSeqNbrStd
AND SEV_21B.LastName IS NOT NULL
AND SEV_21B.StopDateTime IS NULL
AND SEV_21B.StopUserID IS NULL

WHERE SEV_21A.LastName IS NOT NULL
AND SEV_21A.OrderSeqNbrStd = 1
AND SEV_21A.HouseHoldCombineFlag = 'Y'
AND SEV_21A.SalutationType = 'A'
AND SEV_21A.StopDateTime IS NULL
AND SEV_21A.StopUserID IS NULL

ORDER BY SEV_21A.HouseHoldKey, SEV_21A.ID
ASKED: Jun 25 2009  7:53 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Mrdenny
47070 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
What's the error message that you are getting? There's no limit in SQL to the number of temp tables you can use in a single batch. However I don't see a temp table (or table variable) in your select statement.
Last Answered: Jun 25 2009  8:43 PM GMT by Mrdenny   47070 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

PaminBuckeyeLand   15 pts.  |   Jun 25 2009  9:39PM GMT

SQR is returning a syntax error based on the SQL statement it evaluated.

There should be a dynamic variable with the name
of a temp table in it [$Temp_Final_IDList] after the last INNER JOIN shown.
The select statement (submitted in my original question) shows the variable.

It’s like everything after the INNER JOIN is ignored. I did run tests to display
the contents of the variable to make sure it wasn’t empty and it wasn’t!
I’m kind of clueless what to try next!

ACTUAL ERROR
(SQR 5528) ODBC SQL dbsql: SQLPrepare error 102 in cursor 28:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘JOIN’.
(SQR 5528) ODBC SQL dbsql: SQLPrepare error 8180 in cursor 28:
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

SQL: SELECT SEV_21A.ID, SEV_21A.HouseHoldKey, SEV_21A.HouseHoldCombineFlag,
SEV_21A.OrderSeqNbrStd, SEV_21A.PrefixRankAdjusted,
SEV_21A.SalutationPrefixCode, SEV_21A.PrefixFormalSal,
SEV_21A.PrefixFormalSalPunc, SEV_21A.NickName, SEV_21A.FirstName,
SEV_21A.FirstNamePunc, SEV_21A.LastName, SEV_21A.Gender, SEV_21B.ID,
SEV_21B.HouseHoldKey, SEV_21B.HouseHoldCombineFlag,
SEV_21B.OrderSeqNbrStd, SEV_21B.PrefixRankAdjusted,
SEV_21B.SalutationPrefixCode, SEV_21B.PrefixFormalSal,
SEV_21B.PrefixFormalSalPunc, SEV_21B.NickName, SEV_21B.FirstName,
SEV_21B.FirstNamePunc, SEV_21B.LastName, SEV_21B.Gender FROM
Salutation_Elements_View SEV_21A INNER JOIN Salutation_Elements_View
SEV_21B ON SEV_21A.HouseHoldKey = SEV_21B.HouseHoldKey AND
SEV_21A.HouseHoldCombineFlag = SEV_21B.HouseHoldCombineFlag AND
SEV_21A.EntityType = SEV_21B.EntityType AND
SEV_21A.SalutationType = SEV_21B.SalutationType AND
SEV_21A.OrderSeqNbrStd <> SEV_21B.OrderSeqNbrStd AND
SEV_21B.LastName IS NOT NULL AND SEV_21B.StopDateTime IS NULL AND
SEV_21B.StopUserID IS NULL INNER JOIN

Error on line 13724:
(SQR 3716) Error in SQL statement.

Errors were found in the program file.

SQR: Program Aborting.

 
0