cross join issue - 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)

cross join issue

Author

Topic

dbutler_05
Starting Member

17 Posts

Posted - 2006-06-01 : 12:11:10

Can someone help me with a rewrite of the following:

Declare @month as int
Set @Month = (SELECT TOP 1 closeOfMonthPeriod
FROM baseDates
WHERE (busDayUSA = 1) AND (baseDate < DATEADD([day], - 1, GETDATE()))
ORDER BY baseDate DESC)

Select A.AllowanceInLieuPYMonth, B.ARAdjustmentsPYMonth, C.CashDiscountsPYMonth, D.RetailStoresPYMonth,
E.SalesVolumeRebatesPYMonth

From
(Select AllowanceInLieuPYMonth From dbo.view_SimpleSummaryAllowanceInLieuPYMonth
WHERE THMNTH = @Month) AS A
Cross Join
(Select ARAdjustmentsPYMonth From dbo.view_SimpleSummaryARAdjustmentsPYMonth
WHERE THMNTH = @Month) as B
Cross Join
(Select CashDiscountsPYMonth From dbo.view_SimpleSummaryCashDiscountsPYMonth
WHERE THMNTH = @Month) as C
Cross Join
(Select RetailStoresPYMonth From dbo.view_SimpleSummaryRetailStoresPYMonth
WHERE THMNTH = @Month) as D
Cross Join
(Select SalesVolumeRebatesPYMonth From dbo.view_SimpleSummarySalesVolumeRebatesPYMonth
WHERE THMNTH = @Month) as E

Basically, I'm pulling a single field from multiple views with a cross join. Works fine unless there is no value for one of the select statements. In that case, ALL fail and return 'NULL'. I need a work around. Any suggestions would be appreciated.

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-01 : 12:30:30

If each subquery returns only one row, this should do it:


select
	AllowanceInLieuPYMonth =
	(Select AllowanceInLieuPYMonth 
	From dbo.view_SimpleSummaryAllowanceInLieuPYMonth
	WHERE THMNTH = @Month),
	
	ARAdjustmentsPYMonth =
	(Select ARAdjustmentsPYMonth 
	From dbo.view_SimpleSummaryARAdjustmentsPYMonth
	WHERE THMNTH = @Month),
	
	etc.

CODO ERGO SUM

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-01 : 12:34:38

try a full outer join instead and use the where clause as the join field

(Select AllowanceInLieuPYMonth From dbo.view_SimpleSummaryAllowanceInLieuPYMonth
WHERE THMNTH = @Month) AS A
full outer Join
(Select THMNTH, ARAdjustmentsPYMonth From dbo.view_SimpleSummaryARAdjustmentsPYMonth
WHERE THMNTH = @Month) as B
on B.THMNTH = @Month
full outer Join
(Select THMNTH, CashDiscountsPYMonth From dbo.view_SimpleSummaryCashDiscountsPYMonth
WHERE THMNTH = @Month) as C
on C.THMNTH = @Month
...

or you could use THMNTH to join the derived tables.

Can't help thinking this is the wrong way to go about this though.
I take you get a single row back from each query?

select
AllowanceInLieuPYMonth = (Select AllowanceInLieuPYMonth From dbo.view_SimpleSummaryAllowanceInLieuPYMonth
WHERE THMNTH = @Month) ,
....

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

dbutler_05
Starting Member

17 Posts

Posted - 2006-06-01 : 12:42:18

Works great. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources