Sql-make-table
From PTAGISWiki
Script started on Mon Mar 07 11:49:09 2005 sh-2.03$ source ~/.bash_profile [: too many arguments
Welcome to Ingres installation SG!!
II_SYSTEM = /usr/op_ing/ingII
Running II 2.6/0207 (su4.us5/02b)
sockeye:SG:rday: > sql -upittag ptagis3 INGRES TERMINAL MONITOR Copyright 2002 Computer Associates Intl, Inc.^M ESC)0Ingres SPARC SOLARIS Version II 2.6/0207 (su4.us5/02) login Mon Mar 7 11:50:05 2005
continue
- help count srrt;\g
Executing . . .
Name: count_srrt Owner: pittag Created: 18-mar-2003 16:38:43 Type: user table Version: II2.6
Column Information:
Key
Column Name Type Length Nulls Defaults Seq migr_yr char 2 yes null file_id char 15 yes null 1 species char 2 yes null run char 2 yes null rear_type char 2 yes null srrt_count integer 4 no no
continue
- help tag_hdr;\g
Executing . . .
Name: tag_hdr Owner: pittag Created: 18-mar-2003 16:46:58 Type: user table Version: II2.6
Column Information:
Key
Column Name Type Length Nulls Defaults Seq migr_yr char 4 yes null file_id char 15 yes null 1 t_attmpt_seq integer 4 no yes tag_site char 8 yes null tag_date date yes null nfish integer 4 yes null mort_no integer 4 yes null species char 2 yes null run char 2 yes null rear_type char 2 yes null hatchery char 4 yes null stock char 15 yes null brood_yr char 2 yes null raceway char 10 yes null capture_meth char 8 yes null tag_temp float 8 yes null tag_meth char 4 yes null org char 6 yes null coord_id char 3 yes null tagger char 20 yes null rel_file char 15 yes null r_attmpt_seq integer 4 no yes rel_date date yes null rel_num integer 4 yes null rel_site char 8 yes null rel_temp float 8 yes null river_km char 27 yes null epa_reach char 8 yes null transp_dur char 8 yes null transp_type char 20 yes null water_temp float 8 yes null assoc_mark char 30 yes null close_date date yes null tag_session varchar 100 yes null mon_rel_y_n char 1 yes null create_time date yes null update_time date yes null
continue te table RFD_tags_by_huc as
- select c.srrt_count, h.epa_reach from count_srrt c, tag_hdr h where
- c.file_id = h.file_id and h.migr_yr = '2004' ;
- \e
>>editor
(vi session)
- \p
create table RFD_tags_by_huc as select sum(c.srrt_count), h.epa_reach from count_srrt c, tag_hdr h where c.file_id = h.file_id and h.migr_yr = '2004' group by h.epa_reach order by 2 desc; continue
- \t\g\t
Mon Mar 7 11:57:15 2005 Executing . . .
E_US09C6 line 1, Syntax error on 'order'. The correct syntax is:
CREATE TABLE tablename
[(columnname {, ... })]
AS subselect
[with_clause]
(Mon Mar 7 11:57:15 2005)
Mon Mar 7 11:57:15 2005
- \e
>>editor
(vi session)
- \t\g\t
Mon Mar 7 11:57:46 2005 Executing . . .
(38 rows) Mon Mar 7 11:57:53 2005
- commit;\g
Executing . . .
continue
- select * from RFD_tags_by_huc;\g
Executing . . .
^Nlqqqqqqqqqqqqqwqqqqqqqqk^O
^Nx^Ocol1 ^Nx^Oepa_reac^Nx^O
^Ntqqqqqqqqqqqqqnqqqqqqqqu^O
^Nx^O 70255^Nx^O17020008^Nx^O
^Nx^O 544^Nx^O17020010^Nx^O
^Nx^O 1^Nx^O17020011^Nx^O
^Nx^O 40151^Nx^O17030001^Nx^O
^Nx^O 4902^Nx^O17030002^Nx^O
^Nx^O 7790^Nx^O17030003^Nx^O
^Nx^O 1199^Nx^O17050123^Nx^O
^Nx^O 8278^Nx^O17060102^Nx^O
^Nx^O 267^Nx^O17060103^Nx^O
^Nx^O 55925^Nx^O17060104^Nx^O
^Nx^O 19915^Nx^O17060105^Nx^O
^Nx^O 2270^Nx^O17060106^Nx^O
^Nx^O 12277^Nx^O17060201^Nx^O
^Nx^O 803^Nx^O17060202^Nx^O
^Nx^O 4933^Nx^O17060204^Nx^O
^Nx^O 12739^Nx^O17060205^Nx^O
^Nx^O 5355^Nx^O17060206^Nx^O
^Nx^O 3533^Nx^O17060207^Nx^O
^Nx^O 32340^Nx^O17060208^Nx^O
^Nx^O 8346^Nx^O17060209^Nx^O
^Nx^O 9999^Nx^O17060210^Nx^O
^Nx^O 2997^Nx^O17060301^Nx^O
^Nx^O 11147^Nx^O17060302^Nx^O
^Nx^O 12544^Nx^O17060303^Nx^O
^Nx^O 1911^Nx^O17060304^Nx^O
^Nx^O 16593^Nx^O17060305^Nx^O
^Nx^O 8271^Nx^O17060306^Nx^O
^Nx^O 51598^Nx^O17060308^Nx^O
^Nx^O 566^Nx^O17070102^Nx^O
^Nx^O 2379^Nx^O17070103^Nx^O
^Nx^O 14973^Nx^O17070105^Nx^O
^Nx^O 1573^Nx^O17070201^Nx^O
^Nx^O 87^Nx^O17070203^Nx^O
^Nx^O 3215^Nx^O17080003^Nx^O
^Nx^O 434^Nx^O17080006^Nx^O
^Nx^O 1868^Nx^O170900* ^Nx^O
^Nx^O 3010^Nx^O17090004^Nx^O
^Nx^O 372822^Nx^O ^Nx^O
^Nmqqqqqqqqqqqqqvqqqqqqqqj^O
(38 rows)
continue
- select sum(col1) from RFD_tags_by huc;\g
Executing . . .
^Nlqqqqqqqqqqqqqk^O
^Nx^Ocol1 ^Nx^O
^Ntqqqqqqqqqqqqqu^O
^Nx^O 807810^Nx^O
^Nmqqqqqqqqqqqqqj^O
(1 row)
continue
- \q
Your SQL statement(s) have been committed. Ingres Version II 2.6/0207 (su4.us5/02) logout Mon Mar 7 12:00:10 2005 sockeye:SG:rday: > copydb -upittag -c ptagis3 RFD_tags_by_huc INGRES COPYDB Copyright 2002 Computer Associates Intl, Inc.^M Unload directory is '/home/rday'. Reload directory is '/home/rday'. There is one table owned by user 'pittag'. sockeye:SG:rday: > ls -lt | head total 176634 -rw-r--r-- 1 rday pitadmin 642 Mar 7 12:01 copy.in -rw-r--r-- 1 rday pitadmin 348 Mar 7 12:01 copy.out -rw-r--r-- 1 rday pitadmin 8192 Mar 7 11:59 sql.txt drwxr-xr-x 3 rday pitadmin 512 Mar 2 09:46 javabuild -rw-r--r-- 1 rday pitadmin 4647483 Feb 28 16:13 ptagis-sef-source-020405a.zip -rw-r--r-- 1 rday pitadmin 3375 Feb 28 10:13 leech.war lrwxrwxrwx 1 rday pitadmin 18 Feb 25 13:19 cvs -> /usr/local/bin/cvs -rw-r--r-- 1 rday pitadmin 4654197 Feb 18 10:30 ptagis-sef-source-020905a.zip -rw-r--r-- 1 rday pitadmin 746 Feb 18 09:15 utilities.module.patch sockeye:SG:rday: > ^G^Gsql -upittag ptagis3 <copy.out INGRES TERMINAL MONITOR Copyright 2002 Computer Associates Intl, Inc.^M ESC)0Ingres SPARC SOLARIS Version II 2.6/0207 (su4.us5/02) login Mon Mar 7 12:03:08 2005
continue
- * * * go
- * set autocommit on
Executing . . .
continue
- * set lockmode session where readlock=nolock
Executing . . .
continue
- go
- * set session with privileges=all
Executing . . .
continue
- * * * * * copy rfd_tags_by_huc(
col1= c0tab with null(']^NULL^['),
epa_reach= varchar(0)nl with null(']^NULL^['),
nl= d1)
into '/home/rday/rfd_tags_by_huc.pit' Executing . . .
(38 rows) continue
Ingres Version II 2.6/0207 (su4.us5/02) logout Mon Mar 7 12:03:08 2005 sockeye:SG:rday: > ls -lt | head total 176638 -rw-r--r-- 1 rday pitadmin 1101 Mar 7 12:03 rfd_tags_by_huc.pit -rw-r--r-- 1 rday pitadmin 642 Mar 7 12:01 copy.in -rw-r--r-- 1 rday pitadmin 348 Mar 7 12:01 copy.out -rw-r--r-- 1 rday pitadmin 8192 Mar 7 11:59 sql.txt drwxr-xr-x 3 rday pitadmin 512 Mar 2 09:46 javabuild -rw-r--r-- 1 rday pitadmin 4647483 Feb 28 16:13 ptagis-sef-source-020405a.zip -rw-r--r-- 1 rday pitadmin 3375 Feb 28 10:13 leech.war lrwxrwxrwx 1 rday pitadmin 18 Feb 25 13:19 cvs -> /usr/local/bin/cvs -rw-r--r-- 1 rday pitadmin 4654197 Feb 18 10:30 ptagis-sef-source-020905a.zip
