subquery in the from clause?

[chenthorn]

I have a select statement that grabs 36 columns from my 40 column table in order to build a cursor on them so that I may compare each field to another table in order to prevent duplicate data from being inserted. Although slow, this process works fine when I only want to check one file at a time. I would like to parameritize this query to accept different table names that I keep in a temp table, but I dont want to recreate the query as a dynamic string. I thought I could put a subquery in the from clause like this :
SELECT COL1, COL2...COL3
FROM (SELECT tablename from FileDetails where tablename = @table)
I get a syntax error ("Line 2: Incorrect syntax near ')' &quot
Am I wrong in assuming that this can be done? I really do not want to have to place this all in a dynamic string due to the number of columns and their names just might push the 8000 byte limit.
thanks in advance!!

 

[SQLBill]

This seems like a poor way to do what you want:

FROM (SELECT tablename from FileDetails where tablename = @table)

You are inputting a tablename (@table) and then asking it to find the tablename and retrieve it (the Select portion). Let's say the tablename is MYTABLE, your script works like this:

inputs MYTABLE to @table:
FROM (SELECT tablename from FileDetails where tablename = MYTABLE)

then looks for MYTABLE:
where tablename = MYTABLE)

then retrieves MYTABLE
SELECT tablename


Why not just do:

FROM @table

-SQLBill

 

[chenthorn]

Iam building a cursor on the result set of this query. i dont want to build this into a dynamic string due to the size of the cursor and the number of columns that i would have to code. I will if I have to, but I am looking for better ways. When I run the sproc that this query lives in, I have a tempt able that holds all the table names that I want to run through. I was hoping that performing the
FROM (select tablename from filedetails where tablename = @table) would allow me to pass in the sproc parameter without having to dynamically code the string.
Its starting to look like it is not possible.

 

[sunila7]

Hi,
i this query u have give an alias for the subselect.

SELECT COL1, COL2...COL3
FROM (SELECT tablename from FileDetails where tablename = @table) AS TBL

Sunil

 

[ReportsMan]

I like SQLBills idea, but I don't think that will work; when I tried to declare a variable, assign it a table name and select from that variable it errs out.

Since you are using a curser anyway, why not just pull every column from the given table and disregard the results from the extra columns?

Then, its an easy matter just to create a proc with a tablename parameter and dynamically create the sql statement:

create procedure my_proc (@tbl varchar(25)) as
declare
@sql varchar (200)
@tnm varchar (25)

--set up the dynamic from criteria
select @tnm = " from " + @tbl

--create the sql statement
select @sql = "select * " + @tnm

--Run the statement
exec(@sql)

--your cursor code
??

You could throw the data from your statement into a temp table as well, however, you'd have to use a global temp table as non-global temp tables created in exec statements lose scope outside of the executed query.

ie:
--create the sql statement (into temp table)
select @sql = "select * into ##tbl_temp " + @tnm

Then, if you don't want the global table hanging around, throw those results into a non-global table and kill the global table (which you should do when you are done with it anyway)

ie:
select *
into #tbl_temp
from ##tbl_temp

drop table ##tbl_temp

 

[chenthorn]

I tried going down this road before. If I alias the subselect, I have to also alias each column name. When I do this, I get the following error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'CONTCT'...

I dont know that this is the answer.

 

[sunila7]

Hi,

In that query i see that u r having 1 field returned from the sub select and a lot of columns being returned from the first select... so i was thinking that could be the problem.

Can u put the exact SQL u r trying to run.

Sunil

 

[chenthorn]

Here is the declare cursor statement that I would like to work:
DECLARE X_cursor CURSOR scroll
FOR
SELECT LTRIM(RTRIM(a.CONTCT)), LTRIM(RTRIM(a.CONAME)), LTRIM(RTRIM(a.STREET)), LTRIM(RTRIM(a.SUFFIX)), LTRIM(RTRIM(a.DIRIND)), LTRIM(RTRIM(a.POSDIR)), LTRIM(RTRI(a.PRMNUM)), LTRIM(RTRIM(a.SECNUM)), LTRIM(RTRIM(a.CITY16)), LTRIM(RTRIM(a.STATE)), LTRIM(RTRIM(a.ZIP)), LTRIM(RTRIM(a.ZIP4)), LTRIM(RTRIM(a.CRCODE)), LTRIM(RTRIM(a.STCODE)), LTRIM(RTRIM(a.CNTYCD)), LTRIM(RTRIM(a.PHONE)), LTRIM(RTRIM(a.SIC)), LTRIM(RTRIM(a.FRNCOD)), LTRIM(RTRIM(a.POPCOD)), LTRIM(RTRIM(a.INDFRM)), LTRIM(RTRIMa.LASTNM)), LTRIM(RTRIM(a.FRSTNM)), LTRIM(RTRIM(a.PROTTL)), LTRIM(RTRIM(a.TTLCD)), LTRIM(RTRIM(a.GENCD)), LTRIM(RTRIM(a.ISCODE)), LTRIM(RTRIM(a.HDBRCH)), LTRIM(RTRIM(a.[KEY])), LTRIM(RTRIM(a.FAX)), LTRIM(RTRIM(a.OFFSIZ)), LTRIM(RTRIM(a.LOCNUM)), LTRIM(RTRIM(a.PRMSIC)), LTRIM(RTRIM(a.SSIC1)), LTRIM(RTRIM(a.SSIC2)), LTRIM(RTRIM(a.SSIC3)), LTRIM(RTRIM(a.SSIC4))
FROM @table
I would like to replace the from clause with :
"(SELECT tablename FROM filedetails WHERE tablename = @table) AS a", but now I am wondering if I can dynamically code just the declare portion of the cursor, or do I have to encode all of it through to the deallocation.

 

[sunila7]

Hi,
To open the cursor, u can try this SQL

SET @SQL ='DECLARE X_cursor CURSOR scroll
FOR
SELECT LTRIM(RTRIM(a.CONTCT)), LTRIM(RTRIM(a.CONAME)), LTRIM(RTRIM(a.STREET)), LTRIM(RTRIM(a.SUFFIX)),
LTRIM(RTRIM(a.DIRIND)), LTRIM(RTRIM(a.POSDIR)), LTRIM(RTRI(a.PRMNUM)), LTRIM(RTRIM(a.SECNUM)),
LTRIM(RTRIM(a.CITY16)), LTRIM(RTRIM(a.STATE)), LTRIM(RTRIM(a.ZIP)), LTRIM(RTRIM(a.ZIP4)),
LTRIM(RTRIM(a.CRCODE)), LTRIM(RTRIM(a.STCODE)), LTRIM(RTRIM(a.CNTYCD)), LTRIM(RTRIM(a.PHONE)), LTRIM(RTRIM(a.SIC)),
LTRIM(RTRIM(a.FRNCOD)), LTRIM(RTRIM(a.POPCOD)), LTRIM(RTRIM(a.INDFRM)), LTRIM(RTRIMa.LASTNM)), LTRIM(RTRIM(a.FRSTNM)),
LTRIM(RTRIM(a.PROTTL)), LTRIM(RTRIM(a.TTLCD)), LTRIM(RTRIM(a.GENCD)), LTRIM(RTRIM(a.ISCODE)), LTRIM(RTRIM(a.HDBRCH)),
LTRIM(RTRIM(a.[KEY])), LTRIM(RTRIM(a.FAX)), LTRIM(RTRIM(a.OFFSIZ)), LTRIM(RTRIM(a.LOCNUM)), LTRIM(RTRIM(a.PRMSIC)),
LTRIM(RTRIM(a.SSIC1)), LTRIM(RTRIM(a.SSIC2)), LTRIM(RTRIM(a.SSIC3)), LTRIM(RTRIM(a.SSIC4))
FROM ' + @table

exec(@SQL)

Sunil