Topic
  • 8 replies
  • Latest Post - ‏2014-04-16T12:06:25Z by jjoowcn
jjoowcn
jjoowcn
5 Posts

Pinned topic Can't execute SQL in Data Studio

‏2014-04-04T12:41:21Z |

Hi,

I am new to data studio and I'm trying to write SQL statements to get results from the table I have created.

When I typed the SQL such as "SELECT * FROM MyScheme."TableName";  " or "SELECT COUNT(*) FROM MyScheme."TableName";   ",

it works and the entire table and the row numbers will be displayed.

However, when I typed the SQL statements with condition or I had to project a particular field, like:
SELECT CustomerName From MyScheme."Customer"  ;  

it doesn't work and will display : "MyScheme.Customer" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=4.17.30"

I can't think of the solution to solve this problem, and I'd really appreciate if anyone could help:)

 

Updated on 2014-04-12T05:43:31Z at 2014-04-12T05:43:31Z by jjoowcn
  • NiuMac
    NiuMac
    1 Post

    Re: Can't execute SQL in Data Studio

    ‏2014-04-05T02:47:28Z  

    how about using db2 command line instead?

  • jjoowcn
    jjoowcn
    5 Posts

    Re: Can't execute SQL in Data Studio

    ‏2014-04-05T02:55:28Z  
    • NiuMac
    • ‏2014-04-05T02:47:28Z

    how about using db2 command line instead?

    How to use db2 command line? Sorry, I'm really new to data studio and db2...

  • G.Paulus
    G.Paulus
    3 Posts

    Re: Can't execute SQL in Data Studio

    ‏2014-04-13T17:10:19Z  

    Hi,

    does the table "Customer" really exist?

    Your query is like the following: SELECT ... FROM MyScheme."Customer" ...

    Please submit the following SQL statement: SELECT tabschema, tabname FROM syscat.tables where UPPER(tabname) = 'CUSTOMER'

    Are tabschema and tabname in the result in upper case or case sensitive?

    Is the result as follows?

    tabschema   tabname

    MYSCHEME    CUSTOMER

    When you put the schema or table name within the SQL in double quotes the object(s) are treated as case sensitive.

    Issue tabschema and tabname within your SQL without double quotes. Does this work?

    Best regards,

    Gerhard Paulus

    Updated on 2014-04-13T17:19:43Z at 2014-04-13T17:19:43Z by G.Paulus
  • jjoowcn
    jjoowcn
    5 Posts

    Re: Can't execute SQL in Data Studio

    ‏2014-04-13T23:35:27Z  
    • G.Paulus
    • ‏2014-04-13T17:10:19Z

    Hi,

    does the table "Customer" really exist?

    Your query is like the following: SELECT ... FROM MyScheme."Customer" ...

    Please submit the following SQL statement: SELECT tabschema, tabname FROM syscat.tables where UPPER(tabname) = 'CUSTOMER'

    Are tabschema and tabname in the result in upper case or case sensitive?

    Is the result as follows?

    tabschema   tabname

    MYSCHEME    CUSTOMER

    When you put the schema or table name within the SQL in double quotes the object(s) are treated as case sensitive.

    Issue tabschema and tabname within your SQL without double quotes. Does this work?

    Best regards,

    Gerhard Paulus

    Dear Gerhard,

    Hi,

    The result is:

    tabschema   tabname

    C1234567    Customer

     

    But I found that SQL (say, Select Name FROM schema.Customer Where Age='25';) doesn't work when I import cvs file into the table. SQL works when I insert data one by one by using the SQL INSERT INTO.....
    Why this happened? Did I miss some steps to make the cvs file become the data I can retrieve? Thanks.

    Updated on 2014-04-14T02:52:54Z at 2014-04-14T02:52:54Z by jjoowcn
  • G.Paulus
    G.Paulus
    3 Posts

    Re: Can't execute SQL in Data Studio

    ‏2014-04-15T05:22:18Z  
    • jjoowcn
    • ‏2014-04-13T23:35:27Z

    Dear Gerhard,

    Hi,

    The result is:

    tabschema   tabname

    C1234567    Customer

     

    But I found that SQL (say, Select Name FROM schema.Customer Where Age='25';) doesn't work when I import cvs file into the table. SQL works when I insert data one by one by using the SQL INSERT INTO.....
    Why this happened? Did I miss some steps to make the cvs file become the data I can retrieve? Thanks.

    What means that your mentioned SQL does not work? Do you get an error or no rows returned?

    What's the error message that you get when import csv file into the table?

  • jjoowcn
    jjoowcn
    5 Posts

    Re: Can't execute SQL in Data Studio

    ‏2014-04-15T10:47:48Z  
    • G.Paulus
    • ‏2014-04-15T05:22:18Z

    What means that your mentioned SQL does not work? Do you get an error or no rows returned?

    What's the error message that you get when import csv file into the table?

    There was no error message when I loaded the csv file, all the rows are successfully loaded and I can see all the records by clicking "browse data".

    When I tried to use SQL likes "Select * FROM schema.tablename;" it returned the result; but when I tried to type "Select columnname FROM schema.tablename Where...."   it didnt work and return -->

    "schema.tablename" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=4.17.30

    And all SQL involving return of particular columns didnt work, even though the syntax is correct!

    I noticed that there is no problem when I inserted all the columns and records into the tables by using SQL statemen t'Insert into...' instead of loading the csv file.

    So, I think that there may have problems other than the syntax problem.

    1) Is it okay to have _ in the column names like "CORE_SUBJECT"??
    2) Did I miss some steps? Or there are others way to import csv file?
    My method is : Window ->Open Perspective ->Data->...->Schemas->tables->tableName->data->load...

    Correct me if I'm wrong. Thank you.

  • G.Paulus
    G.Paulus
    3 Posts

    Re: Can't execute SQL in Data Studio

    ‏2014-04-16T10:31:34Z  
    • jjoowcn
    • ‏2014-04-15T10:47:48Z

    There was no error message when I loaded the csv file, all the rows are successfully loaded and I can see all the records by clicking "browse data".

    When I tried to use SQL likes "Select * FROM schema.tablename;" it returned the result; but when I tried to type "Select columnname FROM schema.tablename Where...."   it didnt work and return -->

    "schema.tablename" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=4.17.30

    And all SQL involving return of particular columns didnt work, even though the syntax is correct!

    I noticed that there is no problem when I inserted all the columns and records into the tables by using SQL statemen t'Insert into...' instead of loading the csv file.

    So, I think that there may have problems other than the syntax problem.

    1) Is it okay to have _ in the column names like "CORE_SUBJECT"??
    2) Did I miss some steps? Or there are others way to import csv file?
    My method is : Window ->Open Perspective ->Data->...->Schemas->tables->tableName->data->load...

    Correct me if I'm wrong. Thank you.

    When import/load the csv file was successful and browse data works then the table contains the imported/loaded data.

    Select * FROM schema.tablename -> WORKS

    Select columnname FROM schema.tablename Where.... -> DOES NOT WORK

     

    The 2nd SQL throws the sql error SQL0204N  "<name>" is an undefined name.
    Make sure querying an existing table in 2nd SQL like you do in the 1st SQL!

     

    1) Is it okay to have _ in the column names like "CORE_SUBJECT"??

       Yes, that is OK!
    2) Did I miss some steps? Or there are others way to import csv file?

       I think you do not miss any steps when import from csv file!

    But I think you miss something when querying the table.

    Updated on 2014-04-16T10:33:57Z at 2014-04-16T10:33:57Z by G.Paulus
  • jjoowcn
    jjoowcn
    5 Posts

    Re: Can't execute SQL in Data Studio

    ‏2014-04-16T12:06:25Z  
    • G.Paulus
    • ‏2014-04-16T10:31:34Z

    When import/load the csv file was successful and browse data works then the table contains the imported/loaded data.

    Select * FROM schema.tablename -> WORKS

    Select columnname FROM schema.tablename Where.... -> DOES NOT WORK

     

    The 2nd SQL throws the sql error SQL0204N  "<name>" is an undefined name.
    Make sure querying an existing table in 2nd SQL like you do in the 1st SQL!

     

    1) Is it okay to have _ in the column names like "CORE_SUBJECT"??

       Yes, that is OK!
    2) Did I miss some steps? Or there are others way to import csv file?

       I think you do not miss any steps when import from csv file!

    But I think you miss something when querying the table.

    Select * FROM schema.tablename -> WORKS

    Select columnname FROM schema.tablename Where.... -> DOES NOT WORK

    But the SQL above are querying the same table, so I think the table exists.
    btw, I would like to ask how I can make use of SQL statement to return a result having schema (Name1,Name2)?