Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Runtime value expecting single quotes

    Hi,

    I have written a procedure & i am passing values (DATABASE_NAME) as an argument. There are slight difference in fetching up with the single & double quotes. Below is my code & kindly correct me

    CREATE PROCEDURE META_DATA (IN DATABASE_NAME VARCHAR(100))
    DYNAMIC RESULT SETS 1
    P1: BEGIN

    DECLARE cursor1 CURSOR WITH RETURN TO CALLER FOR VALUES

    'select ' ||DATABASE_NAME|| ' as server, TAB.CREATOR, TAB.NAME as TABLE_NAME,
    COL.NAME as COLUMN_NAME, COL.COLTYPE from ' ||DATABASE_NAME|| '.SYSIBM.SYSTABLES TAB,'
    ||DATABASE_NAME|| '.SYSIBM.SYSCOLUMNS COL where TAB.name not like "SYS%"
    and TAB.creator not like "SYS%" and TAB.type = "T" and COL.TBNAME=TAB.NAME';

    OPEN cursor1;
    END P1


    In the first case ||DATABASE_NAME|| has to be in single quotes & for the 2nd & 3rd it has to be ||DATABASE_NAME|| has to be double quotes.

    Please help me.

    Thanks in advance.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Code:
    'select '||''' ||DATABASE_NAME||'''|| ' as server,
    why would you need double quotes? in your where clause? Singles suffice.
    Dave

  3. #3
    Join Date
    Jul 2014
    Posts
    294

    Runtime value expecting single quotes

    Dave Thanks for the reply.
    this is my original query , which i am trying to make dynamic.

    select 'GSDB1_Server' as server, TAB.CREATOR, TAB.NAME as TABLE_NAME, COL.NAME as COLUMN_NAME, COL.COLTYPE from "GSDB1_Server".SYSIBM.SYSTABLES TAB,
    "GSDB1_Server".SYSIBM.SYSCOLUMNS COL where TAB.name not like 'SYS%'
    and TAB.creator not like 'SYS%' and TAB.type = 'T' and COL.TBNAME=TAB.NAME;


    I made changes as you said. I tried on my own to resolve, Unable to get what i am expecting.
    could you please help me on this,

    this is my query;

    'select '||'''' ||DATABASE_NAME|| ''''||' as server, TAB.CREATOR, TAB.NAME as TABLE_NAME,
    COL.NAME as COLUMN_NAME, COL.COLTYPE from '||'''' ||DATABASE_NAME||''''||'.SYSIBM.SYSTABLES TAB,
    '||'''' ||DATABASE_NAME||''''||'.SYSIBM.SYSCOLUMNS COL where TAB.name not like "SYS%"
    and TAB.creator not like "SYS%" and TAB.type = "T" and COL.TBNAME=TAB.NAME';



    This is my partial output ,

    select 'SAMPLE' as server, TAB.CREATOR, TAB.NAME as TABLE_NAME, COL.NAME as COLUMN_NAME, COL.COLTYPE from 'SAMPLE'.SYSIBM.SYSTABLES TAB, 'SAMPLE'.SYSIBM.SYSCOLUMNS COL where TAB.name not like "SYS%" and TAB.creator not like "SYS%" and TAB.type = "T" and COL.TBNAME=TAB.NAME

    I am expecting double quotes with 2nd & 3rd ('SAMPLE'.SYSIBM.SYSCOLUMNS) .I mean this has to be like this ("SAMPLE".SYSIBM.SYSCOLUMNS).
    Last edited by HABBIE; 08-06-14 at 14:42. Reason: full desription

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You have the wrong idea on three part names.

    It should be:
    Code:
    from SYSIBM.SYSTABLES TAB
         , SYSIBM.SYSCOLUMNS COL
    Also, start formatting your stuff for readability. I didn't even notice what you were trying to do the first time, because of it being a run on string, just thought you were putting as DBNAME = ....

    Anyway, a three part name is for accessing a remote DB2 system.
    A three-part name consists of:

    •A LOCATION name that uniquely identifies the remote server that you want to access
    •An AUTHORIZATION ID that identifies the owner of the object (the table or view) at the location that you want to access
    •An OBJECT name that identifies the object at the location that you want to access
    Whereas, you had database name authorization id and object.
    IBM Knowledge Center
    Dave

  5. #5
    Join Date
    Jul 2014
    Posts
    294

    Runtime value expecting single quotes

    Dave,

    I am sorry for the formatting stuff.I ll make it clear for readability.
    Yes, For these three part stuff, I am accessing the remote database using federation. For now I am accessing some other database by sitting on different database on a single machine.The static query which I can run using these three part stuff ,gives me the exact result. But I am facing issues when I am trying to make it dynamic.


    select 'SAMPLE' as server, TAB.CREATOR, TAB.NAME as TABLE_NAME, COL.NAME as COLUMN_NAME, COL.COLTYPE from '"SAMPLE"'.SYSIBM.SYSTABLES TAB, '"SAMPLE"'.SYSIBM.SYSCOLUMNS COL where TAB.name not like "SYS%" and TAB.creator not like "SYS%" and TAB.type = "T" and COL.TBNAME=TAB.NAME


    From the formatted output, I am trying to remove these single quotes around the "'SAMPLE"', as it has to be "SAMPLE".

  6. #6
    Join Date
    Jul 2014
    Posts
    294

    Runtime value expecting single quotes

    Now I can place the double quotes around the "SAMPLE".
    From this query the double quotes on the WHERE clause are stopping my out put. Kindly suggest me how to remove the double quotes on the WHERE clause.

    'select '||'''' ||DATABASE_NAME|| ''''||' as server, TAB.CREATOR,
    TAB.NAME as TABLE_NAME,
    COL.NAME as COLUMN_NAME, COL.COLTYPE from '||'"'||DATABASE_NAME||'"'||'.SYSIBM.SYSTABLES TAB,
    '||'"'||DATABASE_NAME||'"'||'.SYSIBM.SYSCOLUMNS COL where TAB.name not like "SYS%"
    and TAB.creator not like "SYS%" and TAB.type = "T" and COL.TBNAME=TAB.NAME';


    Thanks in advance

Tags for this Thread

Posting Permissions

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