StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POsql iteration syntax error
text
Body
copied!<p>Can anyone help me out with the sql below that, given a table parent_child_grouping which contains two columns parent_name and child_name, where a child can be a parent for other children.</p> <p>I'm trying to write a sql (sybase compatible sql- which eventually would go into a stored procedure) that given a list of parents, it would iterate and return all the children and sub children of the input. </p> <p>I wrote the below:</p> <pre><code> DECLARE @parents varchar(500) set @parents = "'parent1', 'parent2'" if exists (select 1 from sysobjects where type='U' and name='pg_result') drop table pg_result if exists (select 1 from sysobjects where type='U' and name='temp_intermediat_res') drop table temp_intermediat_res DECLARE @sql varchar(500) SET @sql = 'SELECT pg.parent_name, pg.child_name into pg_result FROM mydb..parent_child_grouping pg WHERE pg.parent_name IN (' +@parents+')' execute( @sql) go select r.parent_name,r.child_name into temp_intermediat_res from pg_result r go DECLARE @foundchildren integer set @foundchildren = 1 while(@foundchildren > 0) begin insert pg_result select pg.parent_name,pg.child_name from mydb..parent_child_grouping pg,temp_intermediat_res i where i.child_name=pg.parent_name set @foundchildren = @@rowcount select i.parent_name into parents_list from temp_intermediat_res i drop table temp_intermediat_res select pg.parent_name, pg.child_name into temp_intermediat_res from mydb..parent_child_grouping pg, parents_list p where p.parent_name=pg.parent_name drop table parents_list end --while go select pg.parent_name, pg.child_name from pg_result pg go </code></pre> <p>That's giving me the syntax error below: </p> <pre><code> >[Error] Script lines: 170-196 ---------------------- There is already an object named 'temp_intermediat_res' in the database. Msg: 2714, Level: 16, State: 1 Server: SYBDEV, Line: 16 </code></pre> <p>it's a bit weird as I had just dropped the table in the previous line </p> <p>Lines 16 and around it being the following (in the sql above): </p> <pre><code> drop table temp_intermediat_res select pg.* into temp_intermediat_res from </code></pre> <p>as if the drop table is not taking effect for some reason.</p> <p>i don't really need the temp_intermediat_res and pg_result to be permanent tables, and prepending their names with a # (to make them temporary) is also causing problems.</p> <p>any ideas please?</p> <p>I'm using sybase (select @@ version gives: Adaptive Server Enterprise/12.5.4/EBF 16785 ESD#10/P/Sun_svr4/OS 5.8/ase1254/2159/64-bit/FBO/Mon Nov 2 13:08:08 2009)</p> <p>Thanks</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload