Skip to main content

SQL Select

  • February 18, 2015
  • 8 replies
  • 1 view
D

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.

    8 replies

    Chris Glazier
    Forum|alt.badge.img+2
    • Chris GlazierModerator
    • Moderator
    • February 18, 2015

    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

    D

    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.

    Thanks Chris!!

    D

    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.

    Thanks Chris!!

    D

    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

    Chris Glazier
    Forum|alt.badge.img+2
    • Chris GlazierModerator
    • Moderator
    • February 19, 2015

    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

    D

    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.

    D

    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.

    D

    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.

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement