SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Nested Select, and Table Joint
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Tsebi
Starting Member

4 Posts

Posted - 09/14/2006 :  10:34:15  Show Profile  Reply with Quote
Hi Guys

Am new to sql, and I wold appreciate help with optimising the folloing example. The result of the example should be to list a result with details of the Column names:

OPBal| Receipt| IssTrns| Transfer| ClBal

SELECT dbo.inventory.location, dbo.inventory.itemnum,
(select sum(dbo.matrectrans.linecost) where dbo.matrectrans.issuetype LIKE 'RECEIPT' ) As Receipt,
( select sum(dbo.matrectrans.linecost)where dbo.matrectrans.issuetype LIKE 'TRANSFER' ) As Transfer,
( select(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost)where dbo.IST_ITEMDETAIL.logdate='2006-07-20' ) As OpBal,
( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal,
( sum(matusetrans.linecost) ) As IssTrnf
FROM dbo.matrectrans, dbo.matusetrans, dbo.IST_ITEMDETAIL , ( dbo.inventory inner JOIN dbo.item
ON dbo.inventory.itemnum = dbo.item.itemnum AND dbo.inventory.orgid = dbo.item.orgid )

WHERE dbo.inventory.location = dbo.matusetrans.storeloc
AND dbo.inventory.itemnum = dbo.matrectrans.itemnum AND dbo.inventory.siteid = dbo.matrectrans.siteid

OR dbo.inventory.location = dbo.matrectrans.tostoreloc AND dbo.inventory.itemnum = dbo.matusetrans.itemnum
AND dbo.inventory.siteid = dbo.matusetrans.siteid OR dbo.inventory.location = dbo.matrectrans.fromstoreloc

OR
dbo.inventory.location = dbo.ist_itemdetail.location AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum
GROUP BY dbo.inventory.location, dbo.inventory.itemnum,dbo.matrectrans.issuetype,(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost),
dbo.IST_ITEMDETAIL.logdate,dbo.IST_ITEMDETAIL.curbal,dbo.IST_ITEMDETAIL.avgcost

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/14/2006 :  11:33:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yikes! Where does one even begin?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 09/14/2006 :  11:42:12  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
quote:
( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal,


I wonder if this query runs at all !!

Why are using both styles of writing joins (ANSI and Non-ANSI)? Stick to any one of them (better go for ANSI)?

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/14/2006 :  11:48:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think it is better that you provide us with table definitions, sample data and expected output based on that sample data.

1) You want only 5 columns in the resultset, but you have 7 columns selected in the query
2) Why is ITEM Table involved? No columns are selected from that table, nor is any filter applied
3) DO you have any idea of how this CROSS JOIN does on your WHERE clauses? You have FOUR clauses of which any must be tru to get the row.
4) You are grouping by column not in the select statements.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/14/2006 11:51:34
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/14/2006 :  11:56:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Look at the WHERE clause.
WHERE
		dbo.inventory.location = dbo.matusetrans.storeloc 
		AND dbo.inventory.itemnum = dbo.matrectrans.itemnum
		AND dbo.inventory.siteid = dbo.matrectrans.siteid 

		OR

		dbo.inventory.location = dbo.matrectrans.tostoreloc
		AND dbo.inventory.itemnum = dbo.matusetrans.itemnum
		AND dbo.inventory.siteid = dbo.matusetrans.siteid

		OR

		dbo.inventory.location = dbo.matrectrans.fromstoreloc 

		OR

		dbo.inventory.location = dbo.ist_itemdetail.location
		AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ws5926
Yak Posting Veteran

USA
88 Posts

Posted - 09/14/2006 :  12:27:53  Show Profile  Reply with Quote
Do people actually write SQL code like this?

Why would you preface every column name with dbo.? I could see in the FROM clause, but other than that it just makes it harder to read.

This person doesn't understand how the OR works, he needs some () in there.

I am curious to what the query plan would look like for this mess.

Live to Throw
Throw to Live
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 09/14/2006 :  12:50:10  Show Profile  Reply with Quote

Welcome to sqlteam.com Tsebi!

As a new member we would appreciate it if you would please take a moment 
to fill out the brief questionare:

1) Did you find the reponses to your post...
	a) helpful, friendly, and right on the mark
	b) the answers didn't really help you out
	c) you feel like you've been kicked in doo-dads and 
           left in a crumpled heap drown in your own vomit.

2) You would tell you co-workers that sqlteam.com...
	a) is great place to learn about Sql Server
	b) is a good place to go if you weren't able to find the answer elsewhere
	c) is a place to be avoided at all costs and that technical terrorism
           is the only worthwhile reason to come here again.

3) Which statement do you feel best describes the feeling you were left with:
	a) We were all new to sql server at some point, keep up the good work!
	b) You've got a long way to go but stick with us 
           and we'll see that you get there.
	c) take the drivel you call sql code, stuff it 
           up your @$$, and never come back here again!


Be One with the Optimizer
TG

Edited by - TG on 09/14/2006 13:00:09
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/14/2006 :  12:50:28  Show Profile  Reply with Quote
Why when I asked for a "Hamburger and Fries or hash-browns" did I only get the hash-browns?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/14/2006 :  13:02:22  Show Profile  Reply with Quote
I was just wondering what a Tablee of Joints would hold?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 09/14/2006 :  13:03:47  Show Profile  Reply with Quote
quote:
Originally posted by X002548

I was just wondering what a Tablee of Joints would hold?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam




Did Kristen say hash-brownies?

Be One with the Optimizer
TG
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts

Posted - 09/14/2006 :  15:23:06  Show Profile  Reply with Quote
quote:
Originally posted by TG


Welcome to sqlteam.com Tsebi!

As a new member we would appreciate it if you would please take a moment 
to fill out the brief questionare:




I agree, TG. None of the prior posts were of the quality I would have expected from the caliber of the people who made them. It's a little disappointing.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/14/2006 :  15:24:28  Show Profile  Reply with Quote
Huh?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts

Posted - 09/14/2006 :  15:42:55  Show Profile  Reply with Quote
Brett,

Tara:
quote:

Why when I asked for a "Hamburger and Fries or hash-browns" did I only get the hash-browns?



Peter:
quote:

Yikes! Where does one even begin?



harsh:
quote:

I wonder if this query runs at all !!



You:
quote:

I was just wondering what a Tablee of Joints would hold?



Which of those answers provided any helpful info to the OP? Which were even effort to provide help? Which were even efforts to get more information in order to be able to help?

I thought they were pretty insulting to the OP (without the OP having earned the scorn by his manner of asking without providing info over and over again).

If you look at TG's post that I quoted before, I'd have wanted the answers to be:

1. a
2. a
3. a


Instead, the OP should be answering:

1. c
2. c
3. c


(Of course, TG then added the ever so helpful:

quote:

Did Kristen say hash-brownies?



which then sorta defeats the point of my agreeing in the first place.
Go to Top of Page

ws5926
Yak Posting Veteran

USA
88 Posts

Posted - 09/14/2006 :  15:54:43  Show Profile  Reply with Quote
I think that there are a lot of people out there who think forums are there so that they don't have to do research into their problem.

I personally try to not post until I have at least made an effort to solve the problem on my own and have hit Google to see if any web page out there has a solution.

This person is obviously trying to code outside of his abilities and hasn't looked very long at the query that he posted. The fact that he had a table in the FROM clause that wasn't even referenced in the rest of the query proves this.

No one flamed him and he shouldn't be discouraged to post on this forum, maybe he should just do a little more research before he turns for help.

Due to the fact that he hasn't responded to anyone's posts, it seems that he was just looking for someone to do the work for him and not looking into learning how to write proper SQL code.


Live to Throw
Throw to Live
Go to Top of Page

Tsebi
Starting Member

4 Posts

Posted - 09/14/2006 :  15:55:47  Show Profile  Reply with Quote
Hi guys

like i said am new to sql, and am refering to newbie

mistake with my first posting the output colums will be something like this
1.OPBal| Receipt| IssTrns| Transfer| ClBal|itemnum|location

where OPbal & opbal will be a values from the ist_itemdetail table,
Reciept and Trnsfer values from matrectranstable table when, isstrans a value from matusetrans,
itemnum & location strings from the inventory table.

2.item table is included as it contains all the items numbers
i tried using OUTER JOIN, however i receive the following error :
"Outer join operators cannot be specified in a query containing joined tables".

3. if i use AND instaed of the OR, the output is nothing

4. all the 4 clauses have to be true given that one of the computed columns,share same table name, and colum name, however with different condition

Great Thanks
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 09/14/2006 :  16:27:29  Show Profile  Reply with Quote
>>KenW: It's a little disappointing.
I noticed you haven't provided anything constructive yet either

Tsebi,

>>I wold appreciate help with optimising the folloing example
Is your question purely about optimization?

Here are two tips right off the bat:
1) column values that are subqueries kill optimization because the subquery must exectue for each row returned. It is much better to join the tables into the main query and perform the SUMs as a set.

2) ORs in the where clause force a tablescan. In other words any indexes that could normally be used won't be used. So if you can avoid them that would be good.
One way that may be possible is to UNION two seperate statments. That way you get the "effect" of the OR but the optimizer can still utilize any existing indexes.

If your question also was because the results you are getting are incorrect then the "hash-brown" point Kristen alluded to is right on the mark. You need to enclose ORed criteria in parenthises to isolate them from the AND criteria.

Be One with the Optimizer
TG
Go to Top of Page

Tsebi
Starting Member

4 Posts

Posted - 09/14/2006 :  16:33:00  Show Profile  Reply with Quote
Thanks TG

this gives me the idea, will try the various options and will shout out

Tsebi
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 09/14/2006 :  16:44:46  Show Profile  Reply with Quote
quote:
Originally posted by Tsebi

Thanks TG

this gives me the idea, will try the various options and will shout out

Tsebi


No problem :)

when you do shout out, check out the forum faq for the use of "code" tags. That way you won't loose the formatting.

Also check out the link in Brett's (X002548) posts for the ideal way to get a quick response that will help you.

Good luck!

Be One with the Optimizer
TG
Go to Top of Page

Tsebi
Starting Member

4 Posts

Posted - 09/14/2006 :  16:48:35  Show Profile  Reply with Quote
sure will do!

Tsebi
Se Nkganago Se Nthola Morwalo

Tsebi
[/quote]
No problem :)

when you do shout out, check out the forum faq for the use of "code" tags. That way you won't loose the formatting.

Also check out the link in Brett's (X002548) posts for the ideal way to get a quick response that will help you.

Good luck!

Be One with the Optimizer
TG
[/quote]
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/15/2006 :  01:11:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
KenW,

I think you were too fast with critisism of us trying to help. You didn't mention the four points (posted 09/14/2006 11:48:29) I made to TRY to structure and understand the problem. Also you didn't mention the attempt to visualize the WHERE clause problem with the cartesian product (posted 09/14/2006 11:56:09 ).

I had to start somewhere, right?



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

LazyDragon
Starting Member

30 Posts

Posted - 09/15/2006 :  02:43:16  Show Profile  Reply with Quote
Too many guys trying to sound like Celko.

LazyDragon
T-SQL Programmer
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000