Create a view based on select with nested select

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)

Create a view based on select with nested select

Author

Topic

francism
Starting Member

22 Posts

Posted - 2008-11-05 : 13:43:35

I'd like to create a view based on a select with a nested select. I'm having trouble excluding the filter from the nested select.


SELECT ScheduledStartLTZ, PartyIdName, subject
FROM ActivityPointerPartyView
WHERE
  (ActivityId IN
      (SELECT ACTIVITYID
        FROM GGOF_CRMEXT.dbo.ActivityPointerPartyView
        WHERE
          (ScheduledStart >= '2008-10-06 04:00:00') AND (ScheduledStart < '2008-10-07 04:00:00') AND
          (ActivityTypeCode = 4201) AND
          (PartyUserId = 'pvale' OR OwnerUserId = 'pvale'))) AND
  (PartyUserId <> 'pvale')
ORDER BY ScheduledStart, Subject, PartyIdName

The select retrieves all other attendees of meetings where the person is an attendee. The nested select determines all the meetings where the person is an attendee. The main select then determines all the other attendees of these meetings. I would like to somehow create a view based on this, but keep the filters out of the view.

Any ideas will be appreciated.

Thanks,

Francis.

Microsoft CRM 3 - SQL Server 2000

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-05 : 18:51:00

what do you mean by 'Filters out of the view'? Can you explain clearly?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-06 : 12:17:55

I don't think you can do it that way. You would have to write a specific query, write a stored procedure that takes parameters or you would need to, in essence, do a Cartesian join to get all the Main Users and all the associated Attendies for each meeting the Main User was also associated with. Then you can filter on which evern Main User you are interested in (i.e. pvale).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 12:20:30

seem like what OP is looking for is procedure with some optional filters.

francism
Starting Member

22 Posts

Posted - 2008-11-07 : 19:16:05

I know the following is not correct SQL, but I'm trying to create a view based on the SQL, but I apply the filters when I select from the view.


CREATE VIEW dbo.view1
AS 
SELECT ScheduledStartLTZ, PartyIdName, subject
FROM ActivityPointerPartyView
WHERE
  (ActivityId IN
      (SELECT ACTIVITYID
        FROM GGOF_CRMEXT.dbo.ActivityPointerPartyView
        WHERE
          (ScheduledStart >= ???) AND (ScheduledStart < ???) AND
          (ActivityTypeCode = 4201) AND
          (PartyUserId = '???' OR OwnerUserId = '???'))) AND
  (PartyUserId <> '???')
ORDER BY ScheduledStart, Subject, PartyIdName

I would like to somehow pass the filter values when I run a select on the view,
[code]
select ScheduledStartLTZ, PartyIdName, subject from dbo.view1 where ??? and ??? and ???

Microsoft CRM 3 - SQL Server 2000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 19:50:15

You should write a table-valued function that accepts parameters.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 02:07:52

[code]CREATE FUNCTION YourFunction
(
@Start datetime=NULL,
@End datetime=NULL,
@UserID int=NULL,
@EUserID int=NULL
)
RETURNS TABLE
AS
RETURN
(
SELECT ScheduledStartLTZ, PartyIdName, subject
FROM ActivityPointerPartyView
WHERE
(ActivityId IN
(SELECT ACTIVITYID
FROM GGOF_CRMEXT.dbo.ActivityPointerPartyView
WHERE
(ScheduledStart >= @Start OR @Start IS NULL)
AND (ScheduledStart < @End OR @End IS NULL)
AND (ActivityTypeCode = 4201) AND
(PartyUserId = @UserID OR OwnerUserId = @UserID OR @UserID IS NULL))) AND
(PartyUserId <> @EUserID OR @EUserID IS NULL)
GO[/code]

and acll it like below

[code]SELECT * FROM dbo.YourFunction(val1,val2,val3,val4) ORDER BY ScheduledStart, Subject, PartyIdName[/code]

pass NULL if you dont want to filter on particular parameter

francism
Starting Member

22 Posts

Posted - 2008-11-12 : 10:38:56

visakh16, I've never created a function before but this looks like it's what I require.

Thanks,

Francis.

Microsoft CRM 3 - SQL Server 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 10:44:17

You're welcome

refer this article for samples

http://www.sqlteam.com/article/user-defined-functions

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 10:54:32

IN operator is not that fast with resultsets.

CREATE FUNCTION YourFunction
(
	@Start datetime = NULL,
	@End datetime = NULL,
	@UserID int = NULL,
	@eUserID int = NULL
)
RETURNS TABLE
AS
RETURN	SELECT		appv.ScheduledStartLTZ,
			appv.PartyIdName,
			appv.subject
	FROM		ActivityPointerPartyView AS appv
	INNER JOIN	(
				SELECT		activityID
				FROM		ActivityPointerPartyView
				WHERE		(scheduledStart >= @Start OR @Start IS NULL)
						AND (scheduledStart < @End OR @End IS NULL)
						AND ActivityTypeCode = 4201
						AND (@userID IN (partyUserID, ownerUserID) OR @userID IS NULL)
						AND (partyUserID <> @eUserID OR @eUserID IS NULL)
				GROUP BY	activityID
			) AS d ON d.activityID = appv.activityID

E 12°55'05.63"
N 56°04'39.26"

francism
Starting Member

22 Posts

Posted - 2008-11-12 : 10:57:33

Peso,

Thanks - I'll try your alternate code as well.

Regards,

Francis,.

Microsoft CRM 3 - SQL Server 2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources