Help with a query

Guys-

Any help with this query is greatly appreciated...

DECLARE @.input TABLE
(NodeId VARCHAR(10),
IsChecked CHAR(1))

DECLARE @.Actual TABLE
(AndSetId INT,
NodeId VARCHAR(10),
IsChecked CHAR(1))

INSERT INTO @.Input VALUES ('a', 'T')
INSERT INTO @.Input VALUES ('b', 'T')
INSERT INTO @.Input VALUES ('c', 'F')
INSERT INTO @.Input VALUES ('d', 'F')
INSERT INTO @.Input VALUES ('e', 'T')

INSERT INTO @.Actual VALUES (1, 'a', 'T')
INSERT INTO @.Actual VALUES (1, 'b', 'T')
INSERT INTO @.Actual VALUES (1, 'c', 'F')

INSERT INTO @.Actual VALUES (2, 'c', 'F')
INSERT INTO @.Actual VALUES (2, 'd', 'F')

INSERT INTO @.Actual VALUES (3, 'd', 'F')

INSERT INTO @.Actual VALUES (5, 'd', 'F')
INSERT INTO @.Actual VALUES (5, 'e', 'F')

INSERT INTO @.Actual VALUES (6, 'f', 'F')

INSERT INTO @.Actual VALUES (7, 'g', 'F')

-- We should get back 1, 2, 3, 4. We should not get back 5, 6, 7Did your instructor include the rules (the logic) behind this, or only give you the two inputs and the output that they expected? Have you recently covered anything like this in class?

-PatP|||Pat-

My apologies for not elaborating enough, what I was looking for.

Get all AndSetIds from @.Actual where

if NodeId is present in @.input then the isChecked should match.
eg: AndSetId 1
It has 3 records. And the records match with the records
in Input.
So 1 should be returned.

Where as let us look at AndSet 5.
It has NodeId 'e' as 'F' where as the input has 'e' as 'T'
So 5 should not be returned.

INSERT INTO @.Input VALUES ('a', 'T')
INSERT INTO @.Input VALUES ('b', 'T')
INSERT INTO @.Input VALUES ('c', 'F')
INSERT INTO @.Input VALUES ('d', 'F')
INSERT INTO @.Input VALUES ('e', 'T')

INSERT INTO @.Actual VALUES (1, 'a', 'T')
INSERT INTO @.Actual VALUES (1, 'b', 'T')
INSERT INTO @.Actual VALUES (1, 'c', 'F')

INSERT INTO @.Actual VALUES (2, 'c', 'F')
INSERT INTO @.Actual VALUES (2, 'd', 'F')

INSERT INTO @.Actual VALUES (3, 'd', 'F')

INSERT INTO @.Actual VALUES (5, 'd', 'F')
INSERT INTO @.Actual VALUES (5, 'e', 'F')

INSERT INTO @.Actual VALUES (6, 'f', 'F')

INSERT INTO @.Actual VALUES (7, 'g', 'F')|||Never mind. I think I have a solution. Thanks for your time Pat.sql

help with a query

this query don't work

use Common

go

declare @.DateTable Varchar(8)

declare @.TableName Varchar (255)

declare @.Currency Varchar (3)

select substring (TABLE_NAME,1,8) as date FROM information_schema.tables where TABLE_NAME not like 'IIM%' and TABLE_NAME not like 'sys%'

select substring (TABLE_NAME,9,11) as Currency FROM information_schema.tables where TABLE_NAME not like 'IIM%' and TABLE_NAME not like 'sys%'

set @.DateTable = date

set @.Currency = Currency

SELECT @.TableName = TABLE_NAME

FROM information_schema.tables

where TABLE_NAME not like 'IIM%' and TABLE_NAME not like 'sys%'

INSERT INTO [Common].[dbo].[IIM_RM_EXP_EUR_TEMP]

([Sedol]

,[Date]

,[Currency]

,[Name]

,[Candidate]

,[Benchmark]

,[Min]

,[Max]

,[Initial]

,[Alpha]

,[Specific]

,[Total]

,[Price]

,[Beta]

,[Buy.Cost]

,[Sell.Cost]

,[Factor.1]

,[Factor.2]

,[Factor.3]

,[Factor.4]

,[Factor.5]

,[Factor.6]

,[Factor.7]

,[Factor.8]

,[Factor.9]

,[Factor.10]

,[Factor.11]

,[Factor.12]

,[Factor.13]

,[Factor.14]

,[Factor.15]

,[Factor.16]

,[Factor.17]

,[Factor.18]

,[Factor.19]

,[Factor.20]

,[Factor.21]

,[Factor.22]

,[Factor.23]

,[Factor.24]

,[Style.1]

,[Style.2]

,[Style.3]

,[Style.4]

,[Style.5]

,[Country.1]

,[Country.2]

,[Country.3]

,[Country.4]

,[Country.5]

,[Country.6]

,[Country.7]

,[Country.8]

,[Sector.1]

,[Sector.2]

,[Sector.3]

,[Sector.4]

,[Sector.5]

,[Sector.6]

,[Sector.7]

,[Sector.8]

,[Sector.9]

,[Sector.10]

,[Sector.11]

,[Sector.12]

,[Sector.13]

,[Sector.14]

,[Sector.15]

,[Sector.16]

,[Sector.17]

,[Sector.18]

,[Group.1]

,[Group.2]

,[Group.3]

,[Group.4]

,[Group.5]

,[Group.6]

,[Group.7]

,[Group.8]

,[Group.9]

,[Group.10]

)

SELECT [ID]

,'@.DateTable'

,'@.Currency'

,[Name]

,[Candidate]

,[Benchmark]

,cast ([Min] as real)

,cast ([Max] as real)

,cast ([Initial] as real)

,cast ([Alpha] as real)

,cast ([Specific]as real)

,cast ([Total] as real )

,cast ([Price] as real)

,cast([Beta]as real)

,cast([Buy#Cost]as int)

,cast([Sell#Cost]as int)

,cast([Factor#1]as real)

,cast([Factor#2]as real)

,cast([Factor#3]as real)

,cast([Factor#4]as real)

,cast([Factor#5]as real)

,cast([Factor#6]as real)

,cast([Factor#7]as real)

,cast([Factor#8]as real)

,cast([Factor#9]as real)

,cast([Factor#10]as real)

,cast([Factor#11]as real)

,cast([Factor#12]as real)

,cast([Factor#13]as real)

,cast([Factor#14]as real)

,cast([Factor#15]as real)

,cast([Factor#16]as real)

,cast([Factor#17]as real)

,cast([Factor#18]as real)

,cast([Factor#19]as real)

,cast([Factor#20]as real)

,cast([Factor#21]as real)

,cast([Factor#22]as real)

,cast([Factor#23]as real)

,cast([Factor#24]as real)

,cast([Style#1]as Char)

,cast([Style#2]as Char)

,cast([Style#3]as Char)

,cast([Style#4]as Char)

,cast([Style#5]as Char)

,cast([Country#1]as Char)

,cast([Country#2]as Char)

,cast([Country#3]as Char)

,cast([Country#4]as Char)

,cast([Country#5]as Char)

,cast([Country#6]as Char)

,cast([Country#7]as Char)

,cast([Country#8]as Char)

,cast([Sector#1]as Char)

,cast([Sector#2]as Char)

,cast([Sector#3]as Char)

,cast([Sector#4]as Char)

,cast([Sector#5]as Char)

,cast([Sector#6]as Char)

,cast([Sector#7]as Char)

,cast([Sector#8]as Char)

,cast([Sector#9]as Char)

,cast([Sector#10]as Char)

,cast([Sector#11]as Char)

,cast([Sector#12]as Char)

,cast([Sector#13]as Char)

,cast([Sector#14]as Char)

,cast([Sector#15]as Char)

,cast([Sector#16]as Char)

,cast([Sector#17]as Char)

,cast([Sector#18]as Char)

,cast([Group#1]as Char)

,cast([Group#2]as Char)

,cast([Group#3]as Char)

,cast([Group#4]as Char)

,cast([Group#5]as Char)

,cast([Group#6]as Char)

,cast([Group#7]as Char)

,cast([Group#8]as Char)

,cast([Group#9]as Char)

,cast([Group#10]as Char)

FROM [Common].[dbo].[@.TableName]

You can't do:

FROM [Common].[dbo].[@.TableName]

In order to do this, you'll need to dynamically create the insert statement and use EXEC

eg

Code Snippet

DECLARE @.TableName VARCHAR(100)

SET @.TableName = 'sys.objects'
EXEC ('SELECT * FROM ' + @.TableName)

Help with a Query

Am I able to do something like this? The syntax below is obviously
wrong, I'm looking to see if there is a way to accomplish this.
DECLARE @.DBName varchar(5)
SET @.DBName = 'pubs'
SELECT * FROM @.DBName.dbo.authors
I have a master application and database containing data for several
companies. Each company uses a certain web-based sales software. I have
a local copy of those databases. Each of those databases is identical
in structure, they just contain each company's data.
I want to use stored procedures in my main database to run reports on
the data in each of the databases. I don't want to copy my stored
procedures into each database. Instead I want to call them from the
main database, and within the stored procedure, decide on which
database to run the t-sql.
Thanks!"George" <george.durzi@.gmail.com> wrote in message
news:1134592425.040038.242050@.z14g2000cwz.googlegroups.com...
> Am I able to do something like this? The syntax below is obviously
> wrong, I'm looking to see if there is a way to accomplish this.
> DECLARE @.DBName varchar(5)
> SET @.DBName = 'pubs'
> SELECT * FROM @.DBName.dbo.authors
> I have a master application and database containing data for several
> companies. Each company uses a certain web-based sales software. I have
> a local copy of those databases. Each of those databases is identical
> in structure, they just contain each company's data.
> I want to use stored procedures in my main database to run reports on
> the data in each of the databases. I don't want to copy my stored
> procedures into each database. Instead I want to call them from the
> main database, and within the stored procedure, decide on which
> database to run the t-sql.
> Thanks!
>
You will need to use Dynamic SQL.
Try the following:
EXECUTE ('SELECT * FROM ' + @.DBName + '.dbo.authors')
Rick Sawtell
MCT, MCSD, MCDBA|||Rick, the only issue with that is I actually am gonna be writing a
complex stored procedure.
It looks like dynamic sql is going to have to be the way to go though.|||Before diving into the dynamicity pool and drowning, take look up
"distributed partitioned views" in Books Online.
I'd create an extra database and use it as a central reporting data source.
ML
http://milambda.blogspot.com/