multiple sql in uv stage

spracht · Post by **spracht** » Tue Oct 21, 2003 1:35 am

Hello folks,

I'm trying to issue multiple sql statements (as user defined sql) in a uv stage to drop and (re-)create a table and subsequently create secondary indices. I know that I can drop and create by enabling the correspondent controls. But whith user defined sql, statements separated by semicolon, I get error messages:

With each statement on separate lines, DS complains about something displayed like a pipe '|'. Having all statement on one line, CREATE (after the first semicolon) is said to be unexpected?

I hope somebody of you is ready to reveal the secret

.

spracht · Post by **spracht** » Tue Oct 21, 2003 7:53 am

As a workaround, I created three separate input links to the uv-stage to each execute one single sql.

kduke · Post by **kduke** » Tue Oct 21, 2003 9:43 am

Use a before routine. Much cleaner. Issue an execute statement for each line and not as all one string.

Kim.

ray.wurlod · Post by **ray.wurlod** » Tue Oct 21, 2003 4:53 pm

Here's another approach, provided there are no job parameters in the SQL.
Execute both/all SQL statements from your Administrator client Command window. Then select them and Save. (The selected SQL statements will be saved as a "paragraph" in the VOC file of the project. You will be prompted for the name under which to save.)
In the user-defined SQL of the UV stage, simply name this paragraph. (You may need to precede it with CALL - the paragraph is being invoked as a stored procedure.)