0

I have to create an SQR that generates a list of EEIDs, if there were any changes to the Pension data in the past day. The SQR compiles and works perfectly when I hardcode in the table names.

However, when I tried using variables for the table names, I get a compile error I've pasted the portion of SQR that I'm trying to fix When I start using $tableName and $auditTableName as table variables, that's when I get the error and I'm not sure what is going wrong

Can anyone help?

Please and Thank You

!***************************
begin-procedure Process-Main
!***************************  
let $tableName = 'PS_PENSION_PLAN'
let $auditTableName = 'PS_AUDIT_PENSION_PLN'    
let $dummy-dyn-variable = ''

begin-SELECT DISTINCT
L.EMPLID
L.EMPL_RCD

    do someProcName(&L.EMPLID, &L.EMPL_RCD)

FROM [$dummy-dyn-variable]
(
    SELECT DISTINCT
    PP.EMPLID,
    PP.EMPL_RCD,
    PP.EFFDT,
    '1901-01-01 12:00:00' AS AUDIT_STAMP
    FROM [$dummy-dyn-variable] [$tableName] PP
    UNION
    SELECT DISTINCT
    A.EMPLID,
    A.EMPL_RCD,
    A.EFFDT,
    A.AUDIT_STAMP
    FROM [$dummy-dyn-variable] [$auditTableName] A
)L
WHERE DATEDIFF(DAY,CAST(L.AUDIT_STAMP AS DATE),SYSDATE) = 1
ORDER BY 1,2
end-SELECT
   
end-procedure

Edit:

does the UNION have anything to do with this? I keep receiving is this error:

(SQR 5528) ODBC SQL dbdesc: SQLNumResultCols error 102 in cursor 1:
   [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'FROM'.
(SQR 5528) ODBC SQL dbdesc: SQLNumResultCols error 8180 in cursor 1:
   [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Edit2:

Ok, initial problem solved with [$dummy-dyn-variable], which led to the next problem with the DO command. I've updated the code above with DO someProcName(param_a, param_b)

I am now getting an error saying:

(SQR 2002) DO arguments do not match procedure's

Weird part, if I remove the dynamic table variables and hardcode the table names in the FROM section, then it compiles properly without errors. This makes me believe that the error is not related to my someProcName (maybe?)

am I missing something here?

Improve this question
6
  • 1
    muralijilladakuntablogs.wordpress.com/2014/05/21/… Commented Apr 28, 2022 at 16:56
  • @Aung I already tried that suggestion FROM [$dummy_dyn_variable] [$auditTableName] A but it didn't work for me Commented Apr 28, 2022 at 17:29
  • 1
    Put [$dummy-dyn-variable] after the very first from. And make sure to set it to an empty string. Commented Apr 28, 2022 at 21:43
  • @Bob ... thanks for that reminder. I was able to compile successfully using [$dummy-dyn-variable] after every FROM. Unfortunately, this has led to the next error with a DO call that has stumped me. I've updated edit#2 in the OP .... maybe you can help? Commented Apr 29, 2022 at 3:33
  • 1
    For edit 2, a quick fix would be to move the & variables into $ or # variables (depending on what your proc requires) before you make the Do call, and pass those instead. Commented Apr 30, 2022 at 10:41
Related questions
1
pass my table name as parameter in sql query
2
use variable in FROM statement
2
SQL Server: issue with declaring variable in dynamic SQL
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.