Foreach?

Hi,

I’m not sure whether a “Foreach” capability is achievable in SQL; I’ve examined joins, unions and subqueries but can’t come up with a solution.

Scenario is 3 related tables: Location, Room, Booking (date and guest name) and what I wish to do is produce a data grid that shows every for each Location and for each room the status on a given day as follows:

LocationRoomDateGuest

Main11/1/06Jones

Main21/1/06Smith

Main3nullnull

Annex11/1/06Bloggs

House1nullnull

In psuedo-code what I want to do is

For a given date

Show each location

for each location show each room

for each room show guest (if present) else show null.

I can generate Location and Room OK using LEFT OUTER JOIN but when I introduce the date check it all goes pear shaped in that (obviously) the grid is not populated with nulls in place of the non-existent booking records!

Craighton:

Is this the idea of it?

set nocount on

declare @.location table( location varchar (20) not null)
insert into @.location values ('Main')
insert into @.location values ('Annex')
insert into @.location values ('House')
insert into @.location values ('Planned')
--select * from @.location

declare @.room table ( location varchar (20) not null, room varchar (8) not null )
insert into @.room values ('Main', '1')
insert into @.room values ('Main', '2')
insert into @.room values ('Main', '3')
insert into @.room values ('Annex', '1')
insert into @.room values ('Annex', '2')
insert into @.room values ('House', '1')
--select * from @.room

declare @.booking table
( location varchar (20) not null,
room varchar (8) not null,
booking datetime not null,
guest varchar (20) null
)
insert into @.booking values ('Main', '1', '1/1/6', 'Jones')
insert into @.booking values ('Main', '2', '1/1/6', 'Smith')
insert into @.booking values ('Annex', '1', '1/1/6', 'Bloggs')
--select * from @.booking

select a.location,
b.room,
c.booking,
c.guest
from @.location a
full join @.room b
on a.location = b.location
full join @.booking c
on ( a.location = c.location or
b.location = c.location
)
and ( b.room = c.room )

-- -
-- S A M P L E O U T P U T :
-- -

-- location room booking guest
-- -- -- --
-- Main 1 2006-01-01 00:00:00.000 Jones
-- Main 2 2006-01-01 00:00:00.000 Smith
-- Main 3 NULL NULL
-- Annex 1 2006-01-01 00:00:00.000 Bloggs
-- Annex 2 NULL NULL
-- House 1 NULL NULL
-- Planned NULL NULL NULL

|||

I forgot the "for a given date part." You will need to add:

and c.booking = @.givenDate

|||what about using a cross apply in place of the join ?|||

You can do the following (assumes that only one guest can stay in a room):

select l.Location, r.Room, @.date as Date

, (select b.Guest from Booking as b where b.RoomId = r.RoomId and b.Date = @.date) as Guest

from Location as l

join Room as r

on r.LocationId = r.LocationId

I might have made some incorrect assumptions about your schema and relationships between the tables. But you should get the idea. You don't need to do outer joins. Also, if you need to do this for a range of dates then use a Calendar table and cross join with that like:

select l.Location, r.Room, c.dt as Date

, (select b.Guest from Booking as b where b.RoomId = r.RoomId and b.Date = c.dt) as Guest

from Location as l

join Room as r

on r.LocationId = r.LocationId

cross join Calendar as c

where c.dt >= @.date1 and c.dt < dateadd(week, 7, @.date1)

|||

Mugambo,

Thank you for this - it worked well until the date check was added! See below for the solution from Umachandar. again, thanks for your response.

|||

Carllop,

Thanks for this - Iused both solution from Umachander and one contained the (new to me!) Cross feature.

|||

Umachandar,

Many thanks - worked a treat!

Force Invalidate Snapshot???

I am sorry to post about this again, but I can't put my finger on what is wrong.
When we upgraded our ERP to a new version, I am almost certain that I setup Transactional Replication the exact same way....
I did the following to drop an article from a publication:
sp_dropsubscription
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.subscriber = 'SQL-05'
go
sp_droparticle
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.force_invalidate_snapshot = 1 (I HAVE TO DO THIS OR IT WON'T DROP THE ARTICLE & I Don't know why, it didn't used to be this way.....)
go
sp_refreshsubscriptions 'ReportsPUBL'
go
I made my change to the en_csrep_tbl_usr & did the following;
/* Add the Article back */
sp_addarticle
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.source_table = 'en_csrep_tbl_usr'
go
When I start the Snapshot agent it is doing a snapshot of everything & not just the one table!!!!
Anonymous Subscriptions are NOT allowed.
Can you tell me where to look or why it is making me do the @.force_invalidate_snapshot = 1 now?
thanx!
JUDE
Actually the one difference is that the subscriber is doing a PULL now as opposed to the Publisher doing a PUSH as it was setup in the past.
JUDE
"Jude" <judes@.email.uophx.edu> wrote in message news:ec29Ryv7GHA.4288@.TK2MSFTNGP02.phx.gbl...
I am sorry to post about this again, but I can't put my finger on what is wrong.
When we upgraded our ERP to a new version, I am almost certain that I setup Transactional Replication the exact same way....
I did the following to drop an article from a publication:
sp_dropsubscription
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.subscriber = 'SQL-05'
go
sp_droparticle
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.force_invalidate_snapshot = 1 (I HAVE TO DO THIS OR IT WON'T DROP THE ARTICLE & I Don't know why, it didn't used to be this way.....)
go
sp_refreshsubscriptions 'ReportsPUBL'
go
I made my change to the en_csrep_tbl_usr & did the following;
/* Add the Article back */
sp_addarticle
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.source_table = 'en_csrep_tbl_usr'
go
When I start the Snapshot agent it is doing a snapshot of everything & not just the one table!!!!
Anonymous Subscriptions are NOT allowed.
Can you tell me where to look or why it is making me do the @.force_invalidate_snapshot = 1 now?
thanx!
JUDE
|||Does this difference explain the problem?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jude" <judes@.email.uophx.edu> wrote in message news:enUr06v7GHA.4708@.TK2MSFTNGP05.phx.gbl...
Actually the one difference is that the subscriber is doing a PULL now as opposed to the Publisher doing a PUSH as it was setup in the past.
JUDE
"Jude" <judes@.email.uophx.edu> wrote in message news:ec29Ryv7GHA.4288@.TK2MSFTNGP02.phx.gbl...
I am sorry to post about this again, but I can't put my finger on what is wrong.
When we upgraded our ERP to a new version, I am almost certain that I setup Transactional Replication the exact same way....
I did the following to drop an article from a publication:
sp_dropsubscription
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.subscriber = 'SQL-05'
go
sp_droparticle
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.force_invalidate_snapshot = 1 (I HAVE TO DO THIS OR IT WON'T DROP THE ARTICLE & I Don't know why, it didn't used to be this way.....)
go
sp_refreshsubscriptions 'ReportsPUBL'
go
I made my change to the en_csrep_tbl_usr & did the following;
/* Add the Article back */
sp_addarticle
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.source_table = 'en_csrep_tbl_usr'
go
When I start the Snapshot agent it is doing a snapshot of everything & not just the one table!!!!
Anonymous Subscriptions are NOT allowed.
Can you tell me where to look or why it is making me do the @.force_invalidate_snapshot = 1 now?
thanx!
JUDE
|||No, should it? Should I change it to a PUSH & I won't have this issue any longer?
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:u4Lb1ry7GHA.4552@.TK2MSFTNGP05.phx.gbl...
Does this difference explain the problem?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jude" <judes@.email.uophx.edu> wrote in message news:enUr06v7GHA.4708@.TK2MSFTNGP05.phx.gbl...
Actually the one difference is that the subscriber is doing a PULL now as opposed to the Publisher doing a PUSH as it was setup in the past.
JUDE
"Jude" <judes@.email.uophx.edu> wrote in message news:ec29Ryv7GHA.4288@.TK2MSFTNGP02.phx.gbl...
I am sorry to post about this again, but I can't put my finger on what is wrong.
When we upgraded our ERP to a new version, I am almost certain that I setup Transactional Replication the exact same way....
I did the following to drop an article from a publication:
sp_dropsubscription
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.subscriber = 'SQL-05'
go
sp_droparticle
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.force_invalidate_snapshot = 1 (I HAVE TO DO THIS OR IT WON'T DROP THE ARTICLE & I Don't know why, it didn't used to be this way.....)
go
sp_refreshsubscriptions 'ReportsPUBL'
go
I made my change to the en_csrep_tbl_usr & did the following;
/* Add the Article back */
sp_addarticle
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.source_table = 'en_csrep_tbl_usr'
go
When I start the Snapshot agent it is doing a snapshot of everything & not just the one table!!!!
Anonymous Subscriptions are NOT allowed.
Can you tell me where to look or why it is making me do the @.force_invalidate_snapshot = 1 now?
thanx!
JUDE
|||It shouldn't matter, but I thought you were implying it did.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jude" <judes@.email.uophx.edu> wrote in message news:OPbRK4M8GHA.3280@.TK2MSFTNGP02.phx.gbl...
No, should it? Should I change it to a PUSH & I won't have this issue any longer?
JUDE
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:u4Lb1ry7GHA.4552@.TK2MSFTNGP05.phx.gbl...
Does this difference explain the problem?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jude" <judes@.email.uophx.edu> wrote in message news:enUr06v7GHA.4708@.TK2MSFTNGP05.phx.gbl...
Actually the one difference is that the subscriber is doing a PULL now as opposed to the Publisher doing a PUSH as it was setup in the past.
JUDE
"Jude" <judes@.email.uophx.edu> wrote in message news:ec29Ryv7GHA.4288@.TK2MSFTNGP02.phx.gbl...
I am sorry to post about this again, but I can't put my finger on what is wrong.
When we upgraded our ERP to a new version, I am almost certain that I setup Transactional Replication the exact same way....
I did the following to drop an article from a publication:
sp_dropsubscription
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.subscriber = 'SQL-05'
go
sp_droparticle
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.force_invalidate_snapshot = 1 (I HAVE TO DO THIS OR IT WON'T DROP THE ARTICLE & I Don't know why, it didn't used to be this way.....)
go
sp_refreshsubscriptions 'ReportsPUBL'
go
I made my change to the en_csrep_tbl_usr & did the following;
/* Add the Article back */
sp_addarticle
@.publication = 'ReportsPUBL'
, @.article = 'en_csrep_tbl_usr'
, @.source_table = 'en_csrep_tbl_usr'
go
When I start the Snapshot agent it is doing a snapshot of everything & not just the one table!!!!
Anonymous Subscriptions are NOT allowed.
Can you tell me where to look or why it is making me do the @.force_invalidate_snapshot = 1 now?
thanx!
JUDE

FOR XML AUTO, ELEMENTS

SELECT ... FOR XML AUTO, ELEMENTS returns a blob
My buisinessappl. can't retrieve a blob from a storedprocedure
Is there anyway i can convert the result in the storedprocedure to a text or
varchar
before returning it to my Buisinessappl.
Or maybe there is a property in MSSQL SERVER that i can change to fix this
Jens
Are you using SQL Server 2000 or 2005?
Can you change the client code to get the stream back if you are using SQL
Server 2000?
Best regards
Michael
"Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
> SELECT ... FOR XML AUTO, ELEMENTS returns a blob
> My buisinessappl. can't retrieve a blob from a storedprocedure
> Is there anyway i can convert the result in the storedprocedure to a text
> or
> varchar
> before returning it to my Buisinessappl.
> Or maybe there is a property in MSSQL SERVER that i can change to fix this
> Jens
|||I'm using SQL Server 2000 and on the clientside a appl built with
PowerBuilder 10.
Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
retrieves one row from the StoredProcedure but the one column that should
contain the XML is empty.
I need to somehow convert the result, varchar(32766) will do fine.
Is it possible to save the result from a SELECT .. FOR XML AUTO statement in
the database
regards
Jens
"Michael Rys [MSFT]" skrev:

> Are you using SQL Server 2000 or 2005?
> Can you change the client code to get the stream back if you are using SQL
> Server 2000?
> Best regards
> Michael
> "Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
> news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
>
>
|||If you use SQL Server 2000, you have to use the ADO/OLEDB ICommandStream
interface to get the FOR XML result back as a stream and not a rowset.
And there is no easy, performant way to assign the result of a FOR XML query
to a variable or column in SQL Server 2000. You would have to upgrade to SQL
Server 2005 to get this functionality.
Best regards
Michael
"Jens Mardh" <JensMardh@.discussions.microsoft.com> wrote in message
news:E54AEC3C-3783-4930-8F34-4C782FC72471@.microsoft.com...[vbcol=seagreen]
> I'm using SQL Server 2000 and on the clientside a appl built with
> PowerBuilder 10.
> Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
> retrieves one row from the StoredProcedure but the one column that should
> contain the XML is empty.
> I need to somehow convert the result, varchar(32766) will do fine.
> Is it possible to save the result from a SELECT .. FOR XML AUTO statement
> in
> the database
> regards
> Jens
> "Michael Rys [MSFT]" skrev: