Invalid column error with a twist... probably mixing the wrong concoction here...<please advise> 
Author Message
 Invalid column error with a twist... probably mixing the wrong concoction here...<please advise>

Hi all,

Please excuse my trying to squeeze an omlette into a eggshell.  

The following code represents my attempt to turn a set of sql
statements into a stored procedure.

The objective is to create a table with a column of ranked items based
on how the sys_id is grouped. I like the solution that I was provided
but am getting an unusual error (unusual for me!).

In either case the errors that I need help resolving are indicated
below.
Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'n'.
Server: Msg 207, Level 16, State 1, Line 3
Invalid column name 'n'.

I can fix this one..
Server: Msg 3701, Level 11, State 5, Procedure XPT_TBLTRANS, Line 13
Cannot drop the table 'T1', because it does not exist in the system
catalog.

I'm executing this:

'matter__cost_'

Here's the code:
-- START OF CONVERSION TO PROCEDURE DEFINITION
create procedure XPT_TBLTRANS


AS
BEGIN



-- START PROCESS
--drop table #T1


drop table T1


+ char(10) + char(13)

+  char(10) + char(13)

FROM T1 AS T2 WHERE T2.sys_id = T1.sys_id AND T2.n <= T1.n) '  +
char(10) + char(13)

+ 'ALTER TABLE T1 DROP COLUMN n ' +  + char(10) + char(13) + 'select

from T1 '  + char(10) + char(13)

char(10) + char(13)


END
-- END OF CONVERSION TO PROCEDURE DEFINITION

NOTE: I'd tried to program this using the sp_executesql (as soon as I
moved to sql 7) but it didn't seem to be the right tool because I
don't have much internally changing parameter...(and it was/is a tough
read on SBO)

                  ******   PLEASE READ LINE BELOW  ******
BTW - (this sql stuff isn't that easy -- can someone recommend the
best book on intermediate & advanced sql programming concepts)



Tue, 09 Dec 2003 04:48:21 GMT
 Invalid column error with a twist... probably mixing the wrong concoction here...<please advise>

Hi all,

I did get this going.. Here's the code; only problem is it took 1 min
21 secs to execute on about 30,000 rows (Don't know if that's really
bad or not but I get a sense it is for an sp).

Any thoughts on how to optimize the following? Please post it,

& Thanks again,
Tommie
------
<CODE>
create procedure XPT_TBLTRANS


AS
BEGIN






drop table T1



mkt_companyname.mclient'




UPDATE T1
  SET mv_sys_id = (SELECT COUNT(*)
                 FROM T1 AS T2
                 WHERE T2.sys_id = T1.sys_id
                   AND T2.nbr <= T1.nbr)'






END

Quote:

> Hi all,

> Please excuse my trying to squeeze an omlette into a eggshell.  

> The following code represents my attempt to turn a set of sql
> statements into a stored procedure.

> The objective is to create a table with a column of ranked items based
> on how the sys_id is grouped. I like the solution that I was provided
> but am getting an unusual error (unusual for me!).

> In either case the errors that I need help resolving are indicated
> below.
> Server: Msg 207, Level 16, State 3, Line 3
> Invalid column name 'n'.
> Server: Msg 207, Level 16, State 1, Line 3
> Invalid column name 'n'.

> I can fix this one..
> Server: Msg 3701, Level 11, State 5, Procedure XPT_TBLTRANS, Line 13
> Cannot drop the table 'T1', because it does not exist in the system
> catalog.

> I'm executing this:

> 'matter__cost_'

> Here's the code:
> -- START OF CONVERSION TO PROCEDURE DEFINITION
> create procedure XPT_TBLTRANS


> AS
> BEGIN



> -- START PROCESS
> --drop table #T1


> drop table T1


> + char(10) + char(13)

> +  char(10) + char(13)

> FROM T1 AS T2 WHERE T2.sys_id = T1.sys_id AND T2.n <= T1.n) '  +
> char(10) + char(13)

> + 'ALTER TABLE T1 DROP COLUMN n ' +  + char(10) + char(13) + 'select

> from T1 '  + char(10) + char(13)

> char(10) + char(13)


> END
> -- END OF CONVERSION TO PROCEDURE DEFINITION

> NOTE: I'd tried to program this using the sp_executesql (as soon as I
> moved to sql 7) but it didn't seem to be the right tool because I
> don't have much internally changing parameter...(and it was/is a tough
> read on SBO)

>                   ******   PLEASE READ LINE BELOW  ******
> BTW - (this sql stuff isn't that easy -- can someone recommend the
> best book on intermediate & advanced sql programming concepts)



Tue, 09 Dec 2003 22:41:58 GMT
 Invalid column error with a twist... probably mixing the wrong concoction here...<please advise>
Take a sample of the queries that would be generated from your building the statement and run them in Query Analyzer looking at the query plan that is generated.  This will help you to determine how indexes on your tables may improve the query performance.
Quote:

> Hi all,

> I did get this going.. Here's the code; only problem is it took 1 min
> 21 secs to execute on about 30,000 rows (Don't know if that's really
> bad or not but I get a sense it is for an sp).

> Any thoughts on how to optimize the following? Please post it,

> & Thanks again,
> Tommie
> ------
> <CODE>
> create procedure XPT_TBLTRANS


> AS
> BEGIN






> drop table T1



> mkt_companyname.mclient'




> UPDATE T1
>   SET mv_sys_id = (SELECT COUNT(*)
>                  FROM T1 AS T2
>                  WHERE T2.sys_id = T1.sys_id
>                    AND T2.nbr <= T1.nbr)'






> END


> > Hi all,

> > Please excuse my trying to squeeze an omlette into a eggshell.  

> > The following code represents my attempt to turn a set of sql
> > statements into a stored procedure.

> > The objective is to create a table with a column of ranked items based
> > on how the sys_id is grouped. I like the solution that I was provided
> > but am getting an unusual error (unusual for me!).

> > In either case the errors that I need help resolving are indicated
> > below.
> > Server: Msg 207, Level 16, State 3, Line 3
> > Invalid column name 'n'.
> > Server: Msg 207, Level 16, State 1, Line 3
> > Invalid column name 'n'.

> > I can fix this one..
> > Server: Msg 3701, Level 11, State 5, Procedure XPT_TBLTRANS, Line 13
> > Cannot drop the table 'T1', because it does not exist in the system
> > catalog.

> > I'm executing this:

> > 'matter__cost_'

> > Here's the code:
> > -- START OF CONVERSION TO PROCEDURE DEFINITION
> > create procedure XPT_TBLTRANS


> > AS
> > BEGIN



> > -- START PROCESS
> > --drop table #T1


> > drop table T1


> > + char(10) + char(13)

> > +  char(10) + char(13)

> > FROM T1 AS T2 WHERE T2.sys_id = T1.sys_id AND T2.n <= T1.n) '  +
> > char(10) + char(13)

> > + 'ALTER TABLE T1 DROP COLUMN n ' +  + char(10) + char(13) + 'select

> > from T1 '  + char(10) + char(13)

> > char(10) + char(13)


> > END
> > -- END OF CONVERSION TO PROCEDURE DEFINITION

> > NOTE: I'd tried to program this using the sp_executesql (as soon as I
> > moved to sql 7) but it didn't seem to be the right tool because I
> > don't have much internally changing parameter...(and it was/is a tough
> > read on SBO)

> >                   ******   PLEASE READ LINE BELOW  ******
> > BTW - (this sql stuff isn't that easy -- can someone recommend the
> > best book on intermediate & advanced sql programming concepts)



Wed, 10 Dec 2003 04:03:19 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. <<<RDBMS/UNIX/SYBASE ENGINEERS WANTED>>>>Oakland, CA

2. <<<RDBMS/UNIX/SYBASE ENGINEERS WANTED>>>>Oakland, CA

3. <<<RDBMS/UNIX/SYBASE ENGINEERS WANTED>>>>Oakland, CA

4. >>>>>---- BETA TESTERS WANTED -------<<<<<<<

5. >>>>>---- BETA TESTERS WANTED -------<<<<<<<

6. >>>>>---- BETA TESTERS WANTED -------<<<<<<<

7. >>>>>---- BETA TESTERS WANTED -------<<<<<<<

8. >>>>>> Attention CTO Candidates <<<<<<

9. <><><> www.FotoCD.net <><><>

10. <><><> www.FotoCD.net <><><>

11. <<<<<<ON SALE FOR OCP ORACLE DBA test(1ZO-013) material >>>>>>

12. <><><> www.FotoCD.net <><><>


 
Powered by phpBB® Forum Software