Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Florida
    Posts
    15

    Question Unanswered: qualified column names

    on UDB v8/AIX:
    I can create an sql insert sql statement with qualified column names i.e.
    (<view/schema>.<tablename>.<column name>):
    insert into wmdb.wmdatpdt ( wmdb.wmdatpdt.PERIOD, wmdb.wmdatpdt.FROM_DATE, wmdb.wmdatpdt.TO_DATE )
    values ( '000099', '1982-12-28', '1982-01-24' )

    and it works, no problem.........

    If I then issue the same sql statement (same table name) on OS/390 I get an error:

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "." was found following "". Expected tokens may include: ") , ". SQLSTATE=42601


    Can I use qualified names on OS/390 for insert and update sql statments ?

  2. #2
    Join Date
    Oct 2005
    Posts
    109
    Please see the zOS documentation for DB2:

    http://publib.boulder.ibm.com/infoce...jnrmstr438.htm

    It says for the column name:
    "that is, the table
    name must be the table or view name specified after the INTO keyword, and
    if a qualifier is specified for the table name, it must match the default qualifier."

    You can't give an qualifier here.
    Default qualifier is your sqlid.
    otherwise you have to set it:
    set current sqlid <your_qualifier>.

    Well, you don't need for an insert anyway. In a select-statement use an alias for the table instead.

    Juliane
    Juliane

  3. #3
    Join Date
    Nov 2004
    Posts
    374

    qualified

    I presume if you say
    insert into schema.tname tname (tname.col1....
    giving the table a symbolic name should be possible
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

  4. #4
    Join Date
    Oct 2003
    Location
    Florida
    Posts
    15
    thanks for replying, I tried that as well, same error, I looked in the db2 cross platform development guide and it says that the insert should work....but I cannot get it to work...I wonder if there is some setting in zOS that allows it to work ...

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    insert into wmdb.wmdatpdt (PERIOD, FROM_DATE, TO_DATE )
    values ( '000099', '1982-12-28', '1982-01-24' )
    The fully qualified table name "wmdb.wmdatpdt" unambigually defines the table, it's redundant to retell the table name of each column. Just pass the column name. That should work.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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