IngresSQL help for query to file

philthee d · #1 (**permalink**) 02-04-05, 11:49

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?

PaulMason · #2 (**permalink**) 02-04-05, 13:44

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

philthee d · #3 (**permalink**) 02-07-05, 03:49

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

philthee d · #4 (**permalink**) 02-07-05, 04:47

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]

PaulMason · #5 (**permalink**) 02-07-05, 05:34

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

philthee d · #6 (**permalink**) 02-07-05, 05:44

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

RedAxl · #7 (**permalink**) 02-07-05, 20:53

COPY SESSION.temp_session_table ()
INTO 'mapping_data.txt'

Internet Services

Web Development

Digital Marketing

Consumer Tech