| Author |
Message
|
| shika123 |
 Posted: Tue Sep 08, 2009 8:50 pm Post subject: Passing table column name to esql procedure + help |
 |
|
Novice
Joined: 08 Sep 2009
Posts: 15
|
Hii,
I have an esql stmnt DELETE FROM Database.XXX.Table1 AS R WHERE R.INVOICECODE >= XVAL AND R.INVOICECODE <= YVAL; which works fine..
Now I want to keep this esql delete stmnt in an esql function as I have many different column names on which records are deleted..(I get diff column names from Input)
I feel I cannot pass the columnname to the procedure as argument.
..call Proc1(x,x,columnname) : as when this Proc1 contains above esql Delete stmnt, it will have R.columnname which will search the variable name in Table...
Any Ideas/suggestions how to customise this..
 |
|
| Back to top |
|
 |
| gregop |
| Posted: Tue Sep 08, 2009 11:52 pm Post subject: |
|
|
Voyager
Joined: 24 Nov 2006
Posts: 81
|
You can create your SQL statement as a string, then run as PASSTHRU. Something like:
SET sqlStatement = ('DELETE FROM TABLE1 WHERE ' || col1 || '=' || value1)
PASSTHRU(sqlStatement)
Alternatively take a look at esql EVAL statement to run a dymamic esql staement. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed Sep 09, 2009 1:16 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi,
Wouldn't R.{columnNameArgument} work ?
Regards. |
|
| Back to top |
|
|
| shika123 |
| Posted: Wed Sep 09, 2009 1:31 am Post subject: |
|
|
Novice
Joined: 08 Sep 2009
Posts: 15
|
Wouldn't R.{columnNameArgument} work ?
No It did not work..It cried as a syntax error |
|
| Back to top |
|
|
| sapana |
| Posted: Thu Sep 10, 2009 4:11 am Post subject: |
|
|
Apprentice
Joined: 16 Apr 2007
Posts: 33
Location: Pune
|
Try declaring columnName in function declaration to be of 'NAME' type.
I had tried this to pass tableName, the way you are trying to pass columnName and it worked. |
|
| Back to top |
|
|
| shika123 |
| Posted: Thu Sep 10, 2009 9:57 pm Post subject: |
|
|
Novice
Joined: 08 Sep 2009
Posts: 15
|
Try declaring columnName in function declaration to be of 'NAME' type.
I had tried this to pass tableName, the way you are trying to pass columnName and it worked.
I tried..It worked for Table name..but not for column name .,,as am giving as R.ColumnName (using the reference)... |
|
| Back to top |
|
|
| sapana |
| Posted: Fri Sep 11, 2009 1:30 am Post subject: |
|
|
Apprentice
Joined: 16 Apr 2007
Posts: 33
Location: Pune
|
I tried it as tableName.columnName and it worked.
But would like to know, which alternative is better, using PASSTHRU(sqlStatement) or the above one and why? |
|
| Back to top |
|
|
| shika123 |
| Posted: Fri Sep 11, 2009 4:17 am Post subject: |
|
|
Novice
Joined: 08 Sep 2009
Posts: 15
|
 Got it thri PAssthru stmnt..
I guess passthru is better... |
|
| Back to top |
|
|
|
|
