Results 1 to 11 of 11
  1. #1
    Join Date
    May 2015
    Posts
    5

    Unanswered: DB2 Select Problem

    Hi,

    I am new to DB2, and have got a connection, via the company DSN to the database.

    However, i want to do the following, "Select * from [mc].[agent details]"

    [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "* from" was found following "select ". Expected tokens may include: "<value_expr_primary_or_stub>". SQLSTATE=42601

    Can anyone advise, i've looked at the tables schema in ADO and the table name is correct and present.

    Thanks

    Nathan,

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Nathan,

    What are the original schema name and table name of your table?
    You mustn't use brackets around these names...
    Regards,
    Mark.

  3. #3
    Join Date
    May 2015
    Posts
    14
    Hello Nathan.

    Try to do as Mark said:

    select * from schemaname.tablename or db2 "select * from schemaname.tablename".




    Quote Originally Posted by mark.b View Post
    Hi Nathan,

    What are the original schema name and table name of your table?
    You mustn't use brackets around these names...

  4. #4
    Join Date
    May 2015
    Posts
    5
    Hi

    The schema is MC and the table name is agent details. I used brackets, due to the space.

    The details of the table i got from .OpenSchema() using ADO.

    Thanks.

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Select * from mc."agent details"
    Regards,
    Mark.

  6. #6
    Join Date
    May 2015
    Posts
    5
    Hi, Thanks for that, that worked,

    Im having trouble adding criteria though.

    This errors select agencyno from mc."agent details" where agencyname='nnn'

    Thanks for your help. Ive downloaeded the manual from IBM to look at over time.

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    and the error is ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    May 2015
    Posts
    5
    HI,

    Thanks, i was going to look, but the error is

    [IBM][CLI Driver][DB2/NT] SQL0206N "AGENCYNAME" is not valid in the context where it is used. SQLSTATE=42703

    and the sql is

    select * from MC."Agent Details" WHERE AgencyName like 'a%'


    TIA.

  9. #9
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    In DB2 all the table names and column-names are assumed to be in UPPERCASE , so when the column-names or table-names are in MixedCase (like yours) you must always surround them with double-quotes everywhere they appear in SQL.

    Wise developers/designers avoid mixed case table-names and avoid mixed-case column-names, but sometimes popular applications use MixedCase so you always need to use double-quotes to tell DB2 not to force to capitals.

    select * from MC."Agent Details" WHERE "AgencyName" like 'a%'

  10. #10
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    The real column name you have to specify depends on how exactly this table was created.
    There are some rules:
    If the table was created like this:
    create table "Agent Details" (agencyno int, AgencyName varchar(10))
    then DB2 converts the 'AgencyName' name to upper case and you can use this name in your queries in any case: AgencyName, agencyname, AGENCYNAME, AgEnCyNaMe. DB2 converts such an object's name in your query to uppercase as well.

    But if you want to name your table or column CASE SENSITIVELY for some reason, then DB2 stores this name case sensitively and YOU HAVE TO reference this object exactly as it was specified during its creation. To do it you have to surround this name by double quotes.
    So, if you created your table like this:
    create table MC."Agent Details" (agencyno int, "AgencyName" varchar(10))
    then you have to query this table like this:
    select * from MC."Agent Details" WHERE "AgencyName" like 'a%'
    Regards,
    Mark.

  11. #11
    Join Date
    May 2015
    Posts
    5
    Many thanks all.

    I have a PDF to read on my adventure in DB2.

    Thanks again.

    Nathan,

Posting Permissions

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