Common Table Expressions - 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 2005 Forums

Transact-SQL (2005)

Common Table Expressions

Author

Topic

mdkottman
Starting Member

7 Posts

Posted - 2007-05-04 : 12:16:20

Can you use an if statment within a common table expression? For example:

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
SELECT ManagerID, COUNT(*)
FROM HumanResources.Employee AS e
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID
)
IF GETDATE() > '1/1/2007'
BEGIN
SELECT TOP 1 * FROM DirReps
END
ELSE
BEGIN
SELECT TOP 10 * FROM DirReps
END;
GO

Will this work ??

Thanks!

Cogito ergo spam -- I'm pink therefore I'm Spam

pootle_flump

1064 Posts

Posted - 2007-05-04 : 12:31:14

Afraid not - you can only follow the CTE definition with a single DML statement - the CTE falls out of scope immediately after the first executed line.

BTW - surely after typing all that up you pressed F5 to find out for yourself?

You could work out if you want the top 1 or top 10 BEFORE the CTE definition and use a variable in your query (new to SQL Server 2005).

HTH

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-04 : 12:59:41

try
USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
SELECT ManagerID, COUNT(*)
FROM HumanResources.Employee AS e
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID
)
SELECT TOP 1 * FROM DirReps where GETDATE() > '1/1/2007'
union all
SELECT TOP 10 * FROM DirReps where GETDATE() <= '1/1/2007'

or even

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
SELECT ManagerID, COUNT(*)
FROM HumanResources.Employee AS e
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID
)
SELECT TOP (case when GETDATE() > '1/1/2007' then 1 else 10 end) FROM DirReps where GETDATE() > '1/1/2007'

What do you want to do about midnight?
Don't you want an order by clause?
I assume this isn't the complete code asd there's no point in using a CTE here.

==========================================
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.

mdkottman
Starting Member

7 Posts

Posted - 2007-05-04 : 13:43:22

Actually I was just using the top verb to keep the sql readable. Here is the whole thing I was trying:

DECLARE @sDist VARCHAR(500);
SET @sDist = '5031,5032';

SET @sDist = ',' + LTRIM(RTRIM(@sDist)) + ',';

WITH Dist_CTE ([Date],[DistNo],[Cases],[Vendor],[Carrer],[Type],[Warehouse],[Load2],[AppointmentDate])
AS
(
SELECT
pom.POSchedDelivDate AS [Date],
pom.DistNo AS [DistNo],
SUM(pod.OrdQty) AS [Cases],
dvm.DistVendName AS [Vendor],
cm.Name AS [Carrer],
dbo.fnGetTempProtText(olm.TempProt) AS [Type],
dm.Abbrev AS [Warehouse],
olm.OutLoadNo AS [Load2],
old.SchedDelivDate AS [AppointmentDate]

FROM
dbo.OutLoadDet old WITH(NOLOCK)
INNER JOIN dbo.OutLoadMast olm WITH(NOLOCK) ON old.OutLoadMast_ID=olm.ID
INNER JOIN dbo.PurchOrdMast pom WITH(NOLOCK) ON old.PurchOrdMast_ID=pom.ID
INNER JOIN dbo.PurchOrdDet pod WITH(NOLOCK) ON pom.ID=pod.PurchOrdMast_ID
INNER JOIN dbo.CarrMast cm WITH(NOLOCK) ON olm.CarrNo=cm.CarrNo
INNER JOIN dbo.DistVendMast dvm WITH(NOLOCK) ON pom.DistVendMast_ID=dvm.ID
INNER JOIN dbo.DistMast dm WITH(NOLOCK) ON pom.DistNo=dm.DistNo

GROUP BY
pom.POSchedDelivDate,
cm.[Name],
olm.TempProt,
pom.DistNo,
dm.Abbrev,
olm.OutLoadNo,
dvm.DistVendName,
old.SchedDelivDate,
dbo.fnCustPO (pom.DistNo,pom.PurchOrdNo,
dm.POPrefix,dvm.CarrPOPrefix,
dm.CarrPOPrefix,dvm.CarrPOSuffix,
dm.CarrPOSuffix)
HAVING
pom.POSchedDelivDate > GETDATE()
)
IF CHARINDEX(@sDist,'*')>0
BEGIN
SELECT [Date],[DistNo],[Cases],[Vendor],[Carrer],[Type],[Warehouse],[Load2]
FROM Dist_CTE
END
ELSE
BEGIN
SELECT [Date],[DistNo],[Cases],[Vendor],[Carrer],
[Type],[Warehouse],[Load2]
FROM Dist_CTE
WHERE
CHARINDEX(',' + CAST(DistNo AS VARCHAR(10))+ ',', @sDist) >0 AND
[Date] > GETDATE()
END
;

Cogito ergo spam -- I'm pink therefore I'm Spam

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-04 : 19:35:12

>> CHARINDEX(@sDist,'*')
do you mean CHARINDEX('*', @sDist) i.e. @sDist like '%*%'
the having clause should be a where clause.

The query could be
SELECT [Date],[DistNo],[Cases],[Vendor],[Carrer],[Type],[Warehouse],[Load2]
FROM Dist_CTE
where (CHARINDEX(',' + CAST(DistNo AS VARCHAR(10))+ ',', @sDist) >0 AND
[Date] > GETDATE())
or @sDist like '%*%'

I would use a derived table rather than a CTE.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources