I have 20 tables which have the same declaration
for example:
EXEC SQL DECLARE
Produktion TABLE
( ContainerGuid uniqueidentifier(36)
NOT NULL
,BelegId bigint(19) NOT NULL
,BelegJahr bigint(19) NOT NULL
,BelegNummer bigint(19) NOT NULL
,BelegPositionId bigint(19) NOT NULL
,BelegPositionsNummer int(10) NOT NULL
) END-EXEC.
Is it possible do create the select statement where the table-name is variable like:
move "Produktion" to sql-text
EXEC SQL
SELECT DISTINCT
ContainerGuid
INTO :guid-field
FROM :sql-text
* FROM Produktion
WHERE (BelegNummer = :dat-sql-belegnr
AND BelegPositionsNummer = :dat-sql-belegpos)
END-EXEC
move guid-field to dat-sql-cguid-Produktion.
You can do this using dynamic sql where you build the select statement right before you execute it.
Something like the following:
01 sql-buffer pic x(1000) value spaces.
string "SELECT DISTINCT ContainerGuid INTO ? FROM " delimited by size
sql-text delimited by " "
" * FROM Produktion WHERE (BelegNummer = ? AND BelegPositionsNummer = ?)"
delimited by size into sql-buffer
exec sql prepare mystatement from :sql-buffer end-exec
exec sql execute mystatement using :guid-field, :dat-sql-belegnr, :dat-sql-belegpos end-exec
I have 20 tables which have the same declaration
for example:
EXEC SQL DECLARE
Produktion TABLE
( ContainerGuid uniqueidentifier(36)
NOT NULL
,BelegId bigint(19) NOT NULL
,BelegJahr bigint(19) NOT NULL
,BelegNummer bigint(19) NOT NULL
,BelegPositionId bigint(19) NOT NULL
,BelegPositionsNummer int(10) NOT NULL
) END-EXEC.
Is it possible do create the select statement where the table-name is variable like:
move "Produktion" to sql-text
EXEC SQL
SELECT DISTINCT
ContainerGuid
INTO :guid-field
FROM :sql-text
* FROM Produktion
WHERE (BelegNummer = :dat-sql-belegnr
AND BelegPositionsNummer = :dat-sql-belegpos)
END-EXEC
move guid-field to dat-sql-cguid-Produktion.
I think, i do something wrong:
holen-Contguid section.
move "Produktion" to sql-text
move space to sql-buffer
string "SELECT DISTINCT ContainerGuid INTO ? FROM "
delimited by size
sql-text delimited by " "
" WHERE (BelegNummer = ? AND BelegPositionsNummer = ?)"
delimited by size into sql-buffer
exec sql prepare mystatement from :sql-buffer end-exec
exec sql execute mystatement
using :guid-field, :dat-sql-belegnr, :dat-sql-belegpos
end-exec
value of sql-buffer:
SELECT DISTINCT ContainerGuid INTO ? FROM Produktion WHERE (BelegNummer = ? AND BelegPositionsNummer = ?)
errormessage: Falsche Syntax in der Nähe von '@P1'.
errorcode: 102
I have 20 tables which have the same declaration
for example:
EXEC SQL DECLARE
Produktion TABLE
( ContainerGuid uniqueidentifier(36)
NOT NULL
,BelegId bigint(19) NOT NULL
,BelegJahr bigint(19) NOT NULL
,BelegNummer bigint(19) NOT NULL
,BelegPositionId bigint(19) NOT NULL
,BelegPositionsNummer int(10) NOT NULL
) END-EXEC.
Is it possible do create the select statement where the table-name is variable like:
move "Produktion" to sql-text
EXEC SQL
SELECT DISTINCT
ContainerGuid
INTO :guid-field
FROM :sql-text
* FROM Produktion
WHERE (BelegNummer = :dat-sql-belegnr
AND BelegPositionsNummer = :dat-sql-belegpos)
END-EXEC
move guid-field to dat-sql-cguid-Produktion.
Sorry, I should have looked at this closer before I posted the example.
When you are using host variables to return data and using dynamic sql you are required to use a cursor even if you are only performing a singleton select.
So it would look more like this:
move "Produktion" to sql-text
move space to sql-buffer
exec sql DECLARE C1 CURSOR FOR mystatement end-exec
string "SELECT DISTINCT ContainerGuid FROM "
delimited by size
sql-text delimited by " "
" WHERE (BelegNummer = ? AND BelegPositionsNummer = ?)"
delimited by size into sql-buffer
exec sql prepare mystatement from :sql-buffer end-exec
exec sql OPEN C1 USING :dat-sql-belegnr, :dat-sql-belegpos end-exec
exec sql FETCH C1 INTO :guid-field end-exec
exec sql CLOSE C1 end-exec
I have 20 tables which have the same declaration
for example:
EXEC SQL DECLARE
Produktion TABLE
( ContainerGuid uniqueidentifier(36)
NOT NULL
,BelegId bigint(19) NOT NULL
,BelegJahr bigint(19) NOT NULL
,BelegNummer bigint(19) NOT NULL
,BelegPositionId bigint(19) NOT NULL
,BelegPositionsNummer int(10) NOT NULL
) END-EXEC.
Is it possible do create the select statement where the table-name is variable like:
move "Produktion" to sql-text
EXEC SQL
SELECT DISTINCT
ContainerGuid
INTO :guid-field
FROM :sql-text
* FROM Produktion
WHERE (BelegNummer = :dat-sql-belegnr
AND BelegPositionsNummer = :dat-sql-belegpos)
END-EXEC
move guid-field to dat-sql-cguid-Produktion.
Hallo Chris,
thank you very much - it works.
I have 20 tables which have the same declaration
for example:
EXEC SQL DECLARE
Produktion TABLE
( ContainerGuid uniqueidentifier(36)
NOT NULL
,BelegId bigint(19) NOT NULL
,BelegJahr bigint(19) NOT NULL
,BelegNummer bigint(19) NOT NULL
,BelegPositionId bigint(19) NOT NULL
,BelegPositionsNummer int(10) NOT NULL
) END-EXEC.
Is it possible do create the select statement where the table-name is variable like:
move "Produktion" to sql-text
EXEC SQL
SELECT DISTINCT
ContainerGuid
INTO :guid-field
FROM :sql-text
* FROM Produktion
WHERE (BelegNummer = :dat-sql-belegnr
AND BelegPositionsNummer = :dat-sql-belegpos)
END-EXEC
move guid-field to dat-sql-cguid-Produktion.
Hallo Chris,
thank you very much - it works.
I have 20 tables which have the same declaration
for example:
EXEC SQL DECLARE
Produktion TABLE
( ContainerGuid uniqueidentifier(36)
NOT NULL
,BelegId bigint(19) NOT NULL
,BelegJahr bigint(19) NOT NULL
,BelegNummer bigint(19) NOT NULL
,BelegPositionId bigint(19) NOT NULL
,BelegPositionsNummer int(10) NOT NULL
) END-EXEC.
Is it possible do create the select statement where the table-name is variable like:
move "Produktion" to sql-text
EXEC SQL
SELECT DISTINCT
ContainerGuid
INTO :guid-field
FROM :sql-text
* FROM Produktion
WHERE (BelegNummer = :dat-sql-belegnr
AND BelegPositionsNummer = :dat-sql-belegpos)
END-EXEC
move guid-field to dat-sql-cguid-Produktion.
Hallo Chris,
thank you very much - it works.
