Update: Frum Dude's workaround is just fine, BUT... I still want confirmation that "-602 Specified database file already in use" is expected.

...and if so, it should be documented because no such restriction exists for other items referenced in a FROM clause. Folks WITHOUT years of experience abusing text file I/O in SQL Anywhere may struggle for a long time trying to "fix" this symptom.


I think the following query is choking because there are two references to the local view "v_property" in the master SELECT, and that causes two references to the same OPENSTRING FILE, and that can't be done... right?

WITH v_property AS 
   ( SELECT * 
       FROM OPENSTRING ( FILE 'C:/projects/foxhound/005b_rroad_master_property_list_v1200.txt' )
                  WITH ( property_id     VARCHAR ( 100 ),
                         property_source VARCHAR ( 100 ),
                         priority_usage  VARCHAR ( 100 ),
                         data_type       VARCHAR ( 100 ),
                         PropName        VARCHAR ( 100 ),
                         available_in    VARCHAR ( 100 ),
                         max_id          VARCHAR ( 100 ),
                         notes           LONG VARCHAR )
                OPTION ( DELIMITED BY '\x09' 
                         SKIP 1 )
            AS property
      WHERE priority_usage = 'fast'
        AND property_source IN ( 'db', 'eng' ) )
SELECT v_property.\*
  FROM v_property
           INNER JOIN ( SELECT PropName,
                               COUNT(*) AS PropName_count
                          FROM v_property
                         GROUP BY PropName
                        HAVING PropName_count >= 2 ) AS multi_property
           ON v_property.PropName = multi_property.PropName
 ORDER BY v_property.PropName,
       v_property.property_source;

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cannot access file
'C:/projects/foxhound/005b_rroad_master_property_list_v1200...' --
Specified database file already in use
SQLCODE=-602, ODBC 3 State="HY000"

asked 05 Jul '12, 17:47

Breck%20Carter's gravatar image

Breck Carter
25.7k427587847
accept rate: 20%

edited 08 Jul '12, 05:26


As the 'master of temp tables' ... I would suggest as follows:

SELECT * 
into #v_property
       FROM OPENSTRING ( FILE 'C:/projects/foxhound/005b_rroad_master_property_list_v1200.txt' )
                  WITH ( property_id     VARCHAR ( 100 ),
                         property_source VARCHAR ( 100 ),
                         priority_usage  VARCHAR ( 100 ),
                         data_type       VARCHAR ( 100 ),
                         PropName        VARCHAR ( 100 ),
                         available_in    VARCHAR ( 100 ),
                         max_id          VARCHAR ( 100 ),
                         notes           LONG VARCHAR )
                OPTION ( DELIMITED BY '\x09' 
                         SKIP 1 )
            AS property
      WHERE priority_usage = 'fast'
        AND property_source IN ( 'db', 'eng' );

SELECT v_property.*
  FROM #v_property v_property
           INNER JOIN ( SELECT PropName,
                               COUNT(*) AS PropName_count
                          FROM  #v_property v_property
                         GROUP BY PropName
                        HAVING PropName_count >= 2 ) AS multi_property
           ON v_property.PropName = multi_property.PropName
 ORDER BY v_property.PropName,
       v_property.property_source;
permanent link

answered 06 Jul '12, 06:16

Frum%20Dude's gravatar image

Frum Dude
136339
accept rate: 0%

edited 06 Jul '12, 06:33

Breck%20Carter's gravatar image

Breck Carter
25.7k427587847

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×102

question asked: 05 Jul '12, 17:47

question was seen: 1,149 times

last updated: 08 Jul '12, 05:26