dynamic temporary table variable usage

Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • PaminBuckeyeLand
    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.
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following