Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    5

    Unanswered: IngresSQL help for query to file

    Hi there, i want to run a query against an ingres database and output the results to file.
    I can do this for other queries, i have a problem that my query is joining 2 tables and they both contain the same field that i am requesting.
    I only want data ffrom that field from one of the tables.
    Here is my query
    Code:
    DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_session_table
    AS
    SELECT map_uid map_name, map_sex
    FROM map, linkage_maps
    WHERE map.map_uid = linkage_maps.map_uid
    
    ON COMMIT PRESERVE ROWS
    WITH NORECOVERY
    
    
    \g
    
    COPY SESSION.temp_session_table (
    map_uid = char(0)tab with null ('bOngO'),
    map_name = char(0)tab with null ('bOngO'),
    map_sex = char(0)tab with null ('bOngO'),
    
    )
    INTO 'mapping_data.txt'
    
    \g
    i want to select all map_uids and map_name from map and map_sex from linkage_maps where the map_uids between the 2 tables match up.

    i get an error saying E_US0835 line 1, Column 'map_uid' found in more than one FROM list table.

    if i try saying select map.map_uid .....
    then the first bit executes but fails on the
    E_US09CA line 1, Syntax error on ')'. The correct syntax is:
    COPY TABLE tablename (columnname = format [null_clause] {, ... })
    INTO|FROM 'filename'
    [with_clause]

    i know the syntax is ok for the declare and copy table part as i have used another query without this field in two tables problem and it creates the temporary table no problem.

    Any ideas anyone?

  2. #2
    Join Date
    Nov 2002
    Posts
    33
    Quote Originally Posted by philthee d
    Code:
    DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_session_table
    AS
    SELECT map_uid map_name, map_sex
    FROM map, linkage_maps
    WHERE map.map_uid = linkage_maps.map_uid
    
    ON COMMIT PRESERVE ROWS
    WITH NORECOVERY
    
    
    \g
    
    COPY SESSION.temp_session_table (
    map_uid = char(0)tab with null ('bOngO'),
    map_name = char(0)tab with null ('bOngO'),
    map_sex = char(0)tab with null ('bOngO'), <------
    
    )
    INTO 'mapping_data.txt'
    
    \g
    You seem to have an excess comma before the final bracket. Also, in the initial select you've got a space where you need a comma -

    Code:
    SELECT map_uid map_name, map_sex
    --------------^
    is the same as

    Code:
    SELECT map_uid AS map_name, map_sex
    when what you really mean (given the copy statement) is

    Code:
    SELECT map_uid, map_name, map_sex
    HTH
    Paul
    Paul Mason

  3. #3
    Join Date
    May 2004
    Posts
    5
    thats it, was the extra comma. The missing comma you mentioned i noticed aswell
    If i changed the SELECT statement to

    Code:
     SELECT map.map_uid, map_name, map_sex
    and deleted the extra comma before the final bracket then that seems to work.

    Thankyou

  4. #4
    Join Date
    May 2004
    Posts
    5
    no actually it doesn't work, i am mistaken
    if i do this i still get

    Code:
    continue
    * * * * * * * * * * * Executing . . .
    
    (5145 rows)
    continue
    * * * * * * * * * * Executing . . .
    
    E_US09CA line 1, Syntax error on '.'.  The correct syntax is:
        COPY TABLE tablename (columnname = format [null_clause] {, ... })
           INTO|FROM 'filename'
       [with_clause]

  5. #5
    Join Date
    Nov 2002
    Posts
    33
    Phil,

    Can you post the exact text of the query again - cut and paste it if possible - because I just ran your original - with the changes mentioned above - and it ran ok for me.

    Also what version of Ingres are you using?

    Regards
    Paul
    Paul Mason

  6. #6
    Join Date
    May 2004
    Posts
    5
    Quote Originally Posted by PaulMason
    Phil,

    Can you post the exact text of the query again - cut and paste it if possible - because I just ran your original - with the changes mentioned above - and it ran ok for me.

    Also what version of Ingres are you using?

    Regards
    Paul
    Hi Paul, its ok i do have it working definately this time.
    this is it

    Code:
    DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_session_table
    AS
    SELECT map.map_uid, map.map_name, linkage_maps.map_sex
    FROM map, linkage_maps
    WHERE map.map_uid = linkage_maps.map_uid
    
    ON COMMIT PRESERVE ROWS
    WITH NORECOVERY
    
    
    \g
    
    
    COPY SESSION.temp_session_table (
    map_uid = char(0)tab with null ('bOngO'),
    map_name = char(0)tab with null ('bOngO'),
    map_sex = char(0)nl with null ('bOngO')
    
    )
    INTO 'mapping_data.txt'
    
    \g
    where it seems i was going wrong was when i changed the SELECT to map.map_uid etc.. instead of just map_uid, i also changed it on the COPY SESSION part aswell to map.map_uid which was causing the problem.

    Thanks for your help

  7. #7
    Join Date
    Nov 2004
    Posts
    39
    COPY SESSION.temp_session_table ()
    INTO 'mapping_data.txt'

Posting Permissions

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