quick look over of some t-sql - 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

SQL Server Development (2000)

quick look over of some t-sql

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-14 : 12:38:22

create view Strat_picks_load as
select r.Resource_Interpretation_seq,
p.Strat_Pick_seq,
p.Strat_name_set,
s.Strat_Unit,
p.Pick_depth,
q.Pick_quality,
d.Pick_description
from strat_pick_dts p left join strat_unit s on p.strat_unit = s.strat_unit_legacy_formation
left join resource_interpretation_event_dts r on p.bwdgtmk_dbkey = r.bwdgtmk_dbkey
left join r_pick_quality q on p.pick_quality = q.legacy_code
left join r_pick_description d on p.pick_description = d.legacy_code

To me it looks fine, but it's my first attempt with left joins and I'm not sure if thats the correct syntax. I want to make sure that there is only one entry in the view for every one of the entries in strat_pick_dts. It just takes so long to run. So to create and get rid of it every time I see a correction is extremely time consuming. If anyone gets a chance, could you take a quick look? I just want to make sure the view contains exactly the same number of records as the table does (I've had a select query going on the view for over 45 mins now)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-14 : 12:41:52

try select count(47) from strat_pick_dts
then select count(47) from (select 47 from Strat_picks_load) a
that way you don't have to return all the data to your client . . .

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-14 : 12:49:46

heh, perfect. thnx page47. any reason you chose count(47)? or would count(any number) work the same way?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-14 : 13:02:26

all the answers can be found on page 47 . . . you just have to know how to uncover them . . .

(yeah, ... COUNT({[ALL | DISTINCT] expression] | *}) ...any expression that evaluates to the same number of rows as * will produce the same count.)

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-14 : 13:47:34

damnit... need to flip to page47 again

It seems my join there is producing some 8* as many records as it should be.

Is there something wrong with my syntax there that is producing all these extra records?

If I replace left join to inner join it also produces alot of extra records (same amount actually).. stupid cartesians

Edited by - M.E. on 05/14/2002 13:50:14

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-14 : 14:10:59

it probably not a join syntax issue.

do you have zero to one strat_unit.strat_unit_legacy_format to every one strat_pick_dts.strat_unit?
do you have zero to one resource_interpretation_event_dts.bwdgtmk_dbkey to every one strat_unit.strat_unit_legacy_format?
etc, etc . . .

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources