Disasterous Sql Query Modification

Question

Disasterous Sql Query Modification

Jurushia

SSC Eights!

Points: 947
More actions
September 21, 2005 at 7:11 am

#89997

Good Day,
I have a SQL Query that I've been working on and it works great. However, I added one item to the query that totally throws off everything. This one modification causes the query to take 4 minutes longer to complete and returns a resultset that is 1000x greater than what I need.
Here is the SQL query, with the modifications in RED:
select AdminNetwork.IONetworkName as AdminNetwork,
YU.YUNetworkName as ClientNetwork,
ZX.ZXnameLast + ', ' + ZX.ZXnameFirst As Client,
Sum(OOForcedAmount) As Forced
from workOzone EE, Ozonegenerator ZX, clientNetwork YU, worktask OO, workCompleted IU, internalNetwork AdminNetwork
where EEstatus != 'MAGIC'
AND OOlinkItemID = EEid
AND AdminNetwork.IOid = EEinternalNetworkID
AND EEOzoneGeneratorID = ZXid
AND ZXclientNetworkID = YUid
AND OOForcedDate between '9/1/2005' AND '9/20/2005'
AND IU.IUraysAmount > '00:00:00'
group by AdminNetwork.IONetworkName, YU.YUNetworkName, ZX.ZXnameLast + ', ' + ZX.ZXnameFirst, IU.IUraysAmount
ORDER BY AdminNetwork, ClientNetwork, Client ASC
I'd like to state again, that before I made the mods, the query worked just like I expected.
I can't for the life of me, figure out why the above mods causes the query to go bonkers. If anyone sees anything I did wrong, please let me know.
Thank you!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You're not joining the new table to the rest of the query. I also assume that you have a whole bunch of duplicate data or numbers that are way off. Learn the use the CORRECTR inner join syntaxe and that'll solve your problem.

Jurushia SSC Eights! Points: 947 More actions · Answer 2

Yes RGR'us, I am getting a TON of duplicate data. I've been experimenting with using LEFT JOIN, but something I'm doing is wrong.

I can't believe I'm getting paid $8/hour to do this stuff. I was up til 4am this morning working on the query, and I finished it and it worked like magic! But then Mr. Boss said he wanted to add another search column, and that just causes the query to throw up all over me. YUCK!

Thank you

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Welcome to my world .

Were you able to fix the problem with my info??

Jurushia SSC Eights! Points: 947 More actions · Answer 4

No, not yet...I modified the query to look like this:

select OO.OOid, AdminNetwork.IONetworkName as AdminNetwork,

YU.YUNetworkName as ClientNetwork,

ZX.ZXnameLast + ', ' + ZX.ZXnameFirst As Client,

Sum(OOForcedAmount) As Forced

from workOzone EE, Ozonegenerator ZX, clientNetwork YU, worktask OO, internalNetwork AdminNetwork

INNER JOIN workcompleted IU ON OO.OOid=IU.IUlinkItemID

where EEstatus != 'MAGIC'

AND OOlinkItemID = EEid

AND AdminNetwork.IOid = EEinternalNetworkID

AND EEOzoneGeneratorID = ZXid

AND ZXclientNetworkID = YUid

AND OOForcedDate between '9/1/2005' AND '9/20/2005'

AND IU.IUraysAmount > '00:00:00'

group by AdminNetwork.IONetworkName, YU.YUNetworkName, ZX.ZXnameLast + ', ' + ZX.ZXnameFirst, IU.IUraysAmount

ORDER BY AdminNetwork, ClientNetwork, Client ASC

but I keep getting an error:

The column prefix 'OO' does not match with a table name or alias name used in the query.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Go with a query designer. It'll be easier for you to build this. (create a new view and add the tables and there relationships, or you may want to take the old working query, make a view of out it and then add the new table, make the relation (left join)) and you should be fine.

Jurushia SSC Eights! Points: 947 More actions · Answer 6

Do you mean hire a query designer? We are trying to hire a DB programmer, but til then, I'm stuck with this mess.

BTW, the old query worked beautifully. Adding that one line destroyed it.

thank you RGR

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

No open enterprise manager, go into the right database, then in view, create a new view.

PASTE the old working query.

SAVE

Add the new table and use the interface to add the relation to the new table. Then make sure you select all rows from either of the required table and see if it works.

noeld SSC Guru Points: 96590 More actions · Answer 8

You are mixing two join styles you need to stick to one!

not sure exactly what are you trying to get but you can try:

select IU.IUraysAmount, AdminNetwork.IONetworkName as AdminNetwork,

YU.YUNetworkName as ClientNetwork,

ZX.ZXnameLast + ', ' + ZX.ZXnameFirst As Client,

Sum(OOForcedAmount) As Forced

from workOzone EE, Ozonegenerator ZX, clientNetwork YU, worktask OO, internalNetwork AdminNetwork, workcompleted IU

where EEstatus != 'MAGIC'

AND OOlinkItemID = EEid

AND AdminNetwork.IOid = EEinternalNetworkID

AND EEOzoneGeneratorID = ZXid

AND ZXclientNetworkID = YUid

AND OOForcedDate between '9/1/2005' AND '9/20/2005'

AND OO.OOid = IU.IUlinkItemID

AND IU.IUraysAmount > '00:00:00'

group by AdminNetwork.IONetworkName, YU.YUNetworkName, ZX.ZXnameLast + ', ' + ZX.ZXnameFirst, IU.IUraysAmount

ORDER BY AdminNetwork, ClientNetwork, Client ASC

* Noel

Jurushia SSC Eights! Points: 947 More actions · Answer 9

Wow...how did you do that?

It works now...thank you very much!

BTW, that query maker is awesome!