Pickup tax rate without using CROSS JOIN

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

General SQL Server Forums

New to SQL Server Programming

Pickup tax rate without using CROSS JOIN

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-08-29 : 00:39:40

I need to pickup a tax rate, that is stored on a 1 record file. I would like to avoid using the CROSS JOIN. Is there a way to SELECT the record and set a Variable = to the tax rate so I can pickup the rate in another SELECT statement on each record?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 01:16:18

Can you post table structure, sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-08-29 : 14:58:44

Table 1:
CtrlID char = 'CTRL'
TaxRate float

Table 2:
Shipment
ShipUnits
UnitRate

eg.
TaxRate = .07

Shipment Shipunits UnitRate
1000 100 15.345
1001 50 13.01

Result for each line (I need the Tax Rate for various calculations on each line):

1000 100 15.345 .07
1001 50 13.01 .07

Hope this helps.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-08-29 : 15:37:06

Is this what you mean?

DECLARE @TaxRate double
SELECT @TaxRate = dbo.Table1.TaxRate FROM dbo.Table1 WHERE dbo.Table1.CtrlID = @CtrlID


SELECT dbo.Table2.Shipment,
       dbo.Table2.Shipunits,
       dbo.Table2.UnitRate,
       @TaxRate
FROM   dbo.Table2
WHERE  blah

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-29 : 17:01:55

Why do you feel you need to avoid a join ?
For assigning a single variable I use;
SET @var = ( SELECT var_value FROM ... )

rockmoose

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-08-29 : 22:04:21

The reason I am avoiding the CROSS JOIN is because it appears to be highly inefficient. Using a variable makes more sense to me.

Dustin, I have used your suggestion (replacing Double with Float), thank you. However, I'm not sure how to reference the @TaxRate in the @Sql setting. I am using the @Sql variable to allow user flexibility with respect to filtering the data. Here's what I have so far in a downsized version:

Declare @Sql varchar(1000)
Declare @TaxRate float

Set @TaxRate = (SELECT PSTRate FROM tblCtrl WHERE CtrlID = 'CTRL')
Set @Sql = 'SELECT SHPM.*, PROD.ProductName, @TaxRate As PSTRate ' +
'FROM tblShipments SHPM ' +
'LEFT OUTER JOIN tblProducts PROD ON SHPM.ProductID = PROD.ProductID ' +
@XFilter
Exec (@Sql)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-30 : 04:08:28

The variable is out of scope in the dynamic sql.

Use a "non-equi" JOIN

SELECT SHPM.*, PROD.ProductName, tblCtrl.PSTRate
FROM tblShipments SHPM
INNER JOIN tblCtrl ON tblCtrl.CtrlID = 'CTRL'
LEFT OUTER JOIN tblProducts PROD ON SHPM.ProductID = PROD.ProductID

I'll wager that will be just as efficient, if not more than the variable approach.

There is nothing wrong with using a variable, but You are using dynamic sql inthe example,
(for some reason I cannot fathom !? #¤%) and variables are not in scope!
If You still want to use dynamic sql and variables, see sp_executesql, which can handle that.

You have to actually measure and compare the different approaches before concluding that one is
faster than the other.
Sql Server is very efficient with JOINS.

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-08-30 : 08:52:23

quote:
Originally posted by ingineu

The reason I am avoiding the CROSS JOIN is because it appears to be highly inefficient. Using a variable makes more sense to me.

Definitely not! Don't assume; always check the execution plans to determine the best way. Some people say "always avoid cross joins, if you use them it is inefficient and/or a mistake" but then they don't realize that many SQL statements can be made much more efficient and elegant by using a cross join in the right places.

Also, in this case, you can use subquery:

select t1.*, (select TaxRate from t2) as TaxRate
from
YourTable t1

However, that should have the same execution plan as a CROSS JOIN I would expect.

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-09-02 : 02:51:02

Sorry for the delay ..
Rockmoose, in response to (for some reason I cannot fathom !? #¤%), did you notice the '+ @XFilter'? This is where I pass the WHERE clause depending on what the user has selected to filter records on.

The Inner Join and subquery should do the trick. Thank you.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-02 : 11:12:05

quote:
Originally posted by ingineu

Sorry for the delay ..
Rockmoose, in response to (for some reason I cannot fathom !? #¤%), did you notice the '+ @XFilter'? This is where I pass the WHERE clause depending on what the user has selected to filter records on.

The Inner Join and subquery should do the trick. Thank you.

Didn't see it

Dynamic sql is not my #1 solution to problems.

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-09-02 : 16:40:35

If I had another option, I would use it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources