The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
26.6k418575824
accept rate: 21%

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
26.6k418575824

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:

×101

question asked: 05 Jul '12, 17:47

question was seen: 1,114 times

last updated: 08 Jul '12, 05:26