Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    108

    Unanswered: Can I reference special register USER in the FROM clause?

    Greeting All!
    I have a quick question. I want to code something like below in dynamic SQL:
    Code:
    select * from USER.table-name;
    I assume that "USER" is the special register, but DB2 does not interpret it as the special register in this context. Can you advise how can I achieve my need?

    Thanks you in advance.

    Gary

  2. #2
    Join Date
    May 2003
    Posts
    113
    no. db2(or sql standard don't allow this).
    but for dynamic sql, you can always construct your sql. for example in java, a normal query will be prepared and execute like below:


    String sqlstmt;
    ResultSet rs;
    sqlstmt = new String(" SELECT ts1 FROM T1 " )+

    rs = stmt.executeQuery(sqlstmt);

    System.out.println("query: " + sqlstmt);
    while (rs.next()) {
    System.out.println("ts1: " + rs.getTimestamp(1));
    }

    all you need to do is to construct this string " SELECT ts1 FROM T1 " dynamically.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by DBA-Jr
    Greeting All!
    I have a quick question. I want to code something like below in dynamic SQL:
    Code:
    select * from USER.table-name;
    I assume that "USER" is the special register, but DB2 does not interpret it as the special register in this context. Can you advise how can I achieve my need?

    Thanks you in advance.

    Gary
    You could also do:

    set current schema current user
    select * from table-name

    Andy

  4. #4
    Join Date
    Mar 2005
    Posts
    108
    Thanks. But I don't want to overwrite CURRENT SCHEMA. Any other suggestions?

    Quote Originally Posted by ARWinner
    You could also do:

    set current schema current user
    select * from table-name

    Andy

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    DBA-Jr,
    what is your version of DB2 and fixpack level? OS name and level?

    Where will result SQL be run? For example in Java application, CMD, etc.

    You could get user from special register:
    db2 values (current user)
    then write a program that concats two SQL strings into one string and then execute.

    Hope this helps,
    Grofaty

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •