Subquery in WHERE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Subquery in WHERE

Author

Topic

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-04-28 : 18:01:35

It seems to be a simple query, but I can't make it working- I need to select * from a table which name is returning by subquery.

The subquery works by itself.

select *
from (
select name
from dbo.sysobjects
where (select datename(day, crdate))=(select datename(day, getdate()-1))
and (select datename(month, crdate))=(select datename( month, getdate()))
and (select datename(year, crdate))=(select datename( year, getdate()))
and xtype='U'
and name not like '%MD') A

EventTable 2003y04m27d00h00n24s SERVERX

-----------------------------------------------------

select name
from dbo.sysobjects
where (select datename(day, crdate))=(select datename(day, getdate()-1))
and (select datename(month, crdate))=(select datename( month, getdate()))
and (select datename(year, crdate))=(select datename( year, getdate()))
and xtype='U'
and name not like '%MD'

EventTable 2003y04m27d00h00n24s SERV

---------------------------------------------------------

select a.Action
from (
select name
from dbo.sysobjects
where (select datename(day, crdate))=(select datename(day, getdate()-1))
and (select datename(month, crdate))=(select datename( month, getdate()))
and (select datename(year, crdate))=(select datename( year, getdate()))
and xtype='U'
and name not like '%MD') A

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Action'.
-----------------------------------

Dobby

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-28 : 18:06:45

Well Action isn't a column of your subquery, only name is, so you can only say a.name or a.*. Actually Action isn't a column in sysobjects at all, at least in SQL2k.

What is it that you are trying to do with this query? We could probably help you rewrite it if you let us know what you want the result set to look like.

Tara

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-04-28 : 18:29:31

I have to DTS – Export tables that are created daily by an application. The name of the table is from the type as:

EventTable 2003y04m27d00h00n24s SERVERX

My plan is to use a query in DTS Wizard . (Is it better to use DTS Designer?)
The query must retrieve the name of the table from specified period of time-usually one day in the past
Then the main query must select * from that table and DTS it.
The package will be scheduled for daily execution.

Dobby

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-28 : 18:40:34

DTS Designer is usually the best way to go especially if this is something that is going to be used more than once.

Here is the query to get tables that are one day old:

SELECT name
FROM sysobjects
WHERE crdate > DATEADD(day, -1, getdate()) AND xtype = 'U'

I do not know why you need to select from a subquery to get your desired results. A subquery is not needed here.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-28 : 18:49:20

Do you have to use dts?
Where are you exporting it to?

bcp would be simpler for a text file (one statement).
If you are copying to another database then dmo.

Just call an SP to do the export for each table as a parameter (the sp could call a dts package if you wish).

declare @name varchar(128)
select @name = ''
create table #a (name varchar(128))
insert #a
select name
from dbo.sysobjects
where (select datename(day, crdate))=(select datename(day, getdate()-1))
and (select datename(month, crdate))=(select datename( month, getdate()))
and (select datename(year, crdate))=(select datename( year, getdate()))
and xtype='U'
and name not like '%MD'

while exists (select * from #a where name > @name)
begin
select @name = min(name) from #a where name > @name
exec spExport @name
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-04-28 : 19:02:18

This returns tables from today; I need tables from yesterday.

I'm not very good in explaining, there is what have to be done.

The point is to have the name of table only to be able to retrieve everything from that table. The Subquery gets the name of the table that must be input in main query.

SELECT *
FROM (
SELECT name
FROM sysobjects
WHERE ....

This is the simplest way I can think about.
Dobby

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-28 : 19:05:19

Aren't these equivalent:

SELECT *
FROM (
SELECT name
FROM sysobjects
WHERE ....

AND

SELECT name
FROM sysbojects
HWERE ....

????

Why do you need a subquery?

Tara

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-04-28 : 19:21:31

Tara,

I'd like to select * from table, which name is returned by subquery.

Smasher/nr,

The targer database is Oracle.

By the way, besides DTS Wizard I'm not able to open any DTS Designer.
What's the trick here?

Dobby

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-28 : 19:27:24

quote:

Tara,

I'd like to select * from table, which name is returned by subquery.

But why do you want to select * from table, you should never use select * in any queries. That is a standard that should be followed especially in production environments. What does select * do for you that select name doesn't do?

Tara

Edited by - tduggan on 04/28/2003 19:29:17

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-04-28 : 19:54:29

SELECT name
FROM sysobjects

returns the NAME of the table :
EventTable 2003y04m27d00h00n24s SERVERX

SELECT *
FROM "EventTable 2003y04m27d00h00n24s SERVERX"

returns the data from this table

Dobby

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-28 : 20:04:17

quote:

SELECT name
FROM sysobjects

returns the NAME of the table :
EventTable 2003y04m27d00h00n24s SERVERX

SELECT *
FROM "EventTable 2003y04m27d00h00n24s SERVERX"

returns the data from this table

Ummm, no it doesn't. You are trying to do dynamic sql but aren't actually doing it correctly. Here is an example of dynamic sql

DECLARE @sql VARCHAR(7000)
DECLARE @TableName SYSNAME

SET @TableName = 'SomeTable'

SET @sql = 'SELECT * FROM ' + @TableName

EXEC (@sql)

There are lots of threads about dynamic sql here, so just search on dynamic sql (do the search in the forum search). Also, there are a few articles also on dynamic sql here, so search the articles as well.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources