Forcing Specific Distinct Columns....

Hi Guys,

I have a slight problem, a query that i have written produces data with 2 primary keys the same... however, DINSTINCT wont work in this case as the rows are still different...

Is their a way to force 1 column to always be unique?

Heres the query:

SELECT TOP 5 ORDER_ITEM.ItemID AS 'Item ID', ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity) FROM ORDER_ITEM OrdItem2
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
) AS Total_Purchased, SUM(ORDER_ITEM.ItemQuantity) AS 'Customer Purchased',
CUSTOMER.customerForename AS 'Customer Forename',
CUSTOMER.customerSurname AS 'Customer Surname'
FROM ITEM, ORDER_ITEM, ORDER_T, CUSTOMER
WHERE ITEM.ItemID = ORDER_ITEM.ItemID
AND ORDER_ITEM.OrderID = ORDER_0510096.OrderID
AND ORDER_T.CustomerID = CUSTOMER.CustomerID
GROUP BY ORDER_ITEM.ItemID, ITEM.ItemName,
CUSTOMER.customerForename, CUSTOMER.customerSurname
ORDER BY Total_Purchased DESC

The query is supposed to select the TOP 5 Products sold as well as selecting the customer that purchased the greatest amount of that item and the amount they purchased.

Currently, i will get 2 duplicate rows (except for customers name and the items the purchased. Like this:

ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage

Which is kinda annoying... is there anyway i can prevent this?

And also apart from the Where Joins... is there a more efficient way of writing this?

thx for reading :-)

--PhilkillsDoes no one know any way around this?|||Phil,
The SQL you have posted selecting:
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity)
...does not match with the columns in the dataset you say you are getting:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
Also, your problem sounds more like a data issue than a coding issue.|||Phil,
The SQL you have posted selecting:
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity)
...does not match with the columns in the dataset you say you are getting:
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
Also, your problem sounds more like a data issue than a coding issue.

on the contrary, i left out the data on purpose as it was irrelevent... i was only trying to point out an example of whats wrong... in the "EXAMPLE"
ItemID
8 36 30 Mathew Smith
8 36 6 Tony Wattage
The parts in bold should only appear once.... and the data it should select should be Mathew smith who purchased 30 of that item... 36 is the total amount sold, what i need the query to do... is to only display the person who bought the highest amount of the item that is part of the top 5 best selling items.

another "EXAMPLE":

ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
1.........30.................30......... ......................Jim
2.........20.................20......... ......................Jam
3.........10.................10......... ......................Flim
4.........5..................5......... .......................Flam
5.........2..................2......... .......................Stam

In this case... only 1 person purchased the items in the top 5...

However, if 5 people purchased the same item... the query would return:

ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
1.........30.................5.......... .....................Jim
1.........30.................5.......... .....................Jam
1.........30.................5.......... .....................Flim
1.........30.................5.......... .....................Flam
1.........30.................10......... .....................Stam

Notice how it returned the TOP 1 Item instead of the TOP 5 items?

The data is correct... the query is wrong... which is why i asked if i could put a constraint on the data returned saying that ItemID MUST be unique... unless ofcourse there is a better way to do it ;p

I hope this more accurately describes the problem i am having. :-)

--Philkills|||You are making this more confusing than it should be.

Lets break it down into parts.

Does this code give you the top five records that you want, without the customer information?
SELECT TOP 5
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||You are making this more confusing than it should be.

Lets break it down into parts.

Does this code give you the top five records that you want, without the customer information?
SELECT TOP 5
ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

yep that code gives the top 5 items ^^|||...and now you want the top customer for each of those items?|||...and now you want the top customer for each of those items?

correct...|||OK. I lied. This is complicated, but we are almost there...
Does this return the top CustomerID for each of those orders?:
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ORDER_ITEM CUSTOMER_ITEM
INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
ORDER_T.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||OK. I lied. This is complicated, but we are almost there...
Does this return the top CustomerID for each of those orders?:
SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ORDER_ITEM CUSTOMER_ITEM
INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
ORDER_T.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

Theres no table called CUSTOMER_ITEM... However, i have edited it to return the top customerID correctly.

SELECT ORDER_ITEM.ItemID AS 'Item ID',
ITEM.ItemName AS 'Item Name',
SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
CustomerID =
(SELECT TOP 1
ORDER_T.CustomerID
FROM ITEM
INNER JOIN ORDER_ITEMOrdItem2 ON ITEM.ItemID = OrdItem2.ItemID
INNER JOIN ORDER_T ON OrdItem2.OrderID = ORDER_T.OrderID
INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_T.CustomerID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC,
ORDER.CustomerID)
FROM ITEM
INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
GROUP BY ORDER_ITEM.ItemID,
ITEM_0510096.ItemName
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||ok, so i can display the customer ID... now how do i display the rest of the customer info ... ^_^|||Am i assuming that no one knows the answer? :confused:

lol, kinda funny a problem that even professionals can't figure out :p|||lol, kinda funny a problem that even professionals can't figure out :poh stop

yes, there are a lot of professionals here

what makes you think they want to be your personal free development center?

ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person

good luck, phil

:)|||oh stop

yes, there are a lot of professionals here

what makes you think they want to be your personal free development center?

ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person

good luck, phil

:)

Eh?, i asked 1 question in this thread... and simply gave more info... in my other posts...

If your refering to my other questions that i've asked around the forum... well, they are pretty much unrelated to this question. I have done a lot of research on SQL queries and the only questions i've asked are related to areas that i have found very difficult to find an answer to.

So as a last resort i turned to this support forum, now i realise your doing this out of the kindness of your heart (i think) and for that im grateful. I too help people, but my area of expertise is with C++/Other languages, i on the other hand will help people regardless of the complexity of the problem, infact the more complex the better :p keeps things interesting.

In regards to my original point about no one being able to solve the problem... well, that was based on the posts inside this thread:

OK. I lied. This is complicated, but we are almost there...

He admitted himself that this was complicated, still being new to SQL i do not yet know all of its limits... but when a proffessional admits that something is complicated, i would say its a pretty good indication that it actually is complicated...

When he never replied after that post... i just assumed that he gave up and admitted defeat... i.e. its impossible to do.... atleast efficiently.

So perhaps next time, before jumping in and making assumptions... you should consider all the facts and why i might have said such words.|||When he never replied after that post... i just assumed that he gave up and admitted defeat... i.e. its impossible to do.... atleast efficiently.
I.....already put a lot of time in helping you with this.
YOU...are impatient and rude.
I....did not log in over the weekend to do your work for you.
YOU...can figure out the rest yourself, loser.|||I.....already put a lot of time in helping you with this.
YOU...are impatient and rude.
I....did not log in over the weekend to do your work for you.
YOU...can figure out the rest yourself, loser.

I.... was not intending to be rude
YOU.... Misinterpreted my point.. obviously thinking i was taking a dig at you or your skill when i was not...
I.... Appreciate all your help so far
YOU.... Probably won't even read this :p

It's up to you whether you help me or not i suppose... but just for the record... the only reason i may have seemed slightly impatient was due to the ever approaching deadline :confused:|||as the old saying goes, "failure to plan on your part does not constitute an emergency on our part"

i'm sorry about your homework assignment coming due real soon, but that's life

if you would like to start a new thread, we can close this one and let it fade into the archives...|||Several times you bumped this thread by implying that people here were either too lazy or too incompetent to help you.

And the fact that now it turns out that this was some sort of homework assignment irks me even more.|||Not to bump a thread that seems to have everyone up in arms, but...

I like to try to solve these "puzzles", just to see if I can.

I understand the question as:
Find the top 5 items sold, the top customer for that item, how much
that customer bought, and how much - in total - of the item sold.

Using my own data, I came up with this:

SELECT TOP 5 ORDER_DETAIL.PRODUCT,
ORDER_HEADER.SHIP_TO AS CUST_ID,
SUM(ORDER_DETAIL.SHIP_QTY) AS CUST_SOLD,
ITEM_TTL.TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
INNER JOIN (SELECT TOP 5 ORDER_DETAIL.PRODUCT,
SUM(ORDER_DETAIL.SHIP_QTY) AS TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID=ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC) AS ITEM_TTL ON ORDER_DETAIL.PRODUCT=ITEM_TTL.PRODUCT
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL)) AND (ORDER_DETAIL.PRODUCT IN
(SELECT TOP 5 ORDER_DETAIL.PRODUCT
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC))
GROUP BY ORDER_HEADER.SHIP_TO, ORDER_DETAIL.PRODUCT, ITEM_TTL.TTL_SOLD
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC

This returns:
Product Cust_ID Cust_Sold Ttl_Sold
F.........12.........1035......2700
B.........15.........672.......2457
D.........7.........1243......1972
C.........91.........814.......1757
A.........18.........593.......1549|||I suspect the TOP 5 in the outer query is redundant, since you have it included in your nested subquery.|||Several times you bumped this thread by implying that people here were either too lazy or too incompetent to help you.

And the fact that now it turns out that this was some sort of homework assignment irks me even more.

again suprise suprise you jump to conclusions that you think are right... i was NOT implying anything... if something can't be done... it can't be done... thats why i said what i did...

Not to bump a thread that seems to have everyone up in arms, but...

I like to try to solve these "puzzles", just to see if I can.

I understand the question as:
Find the top 5 items sold, the top customer for that item, how much
that customer bought, and how much - in total - of the item sold.

Using my own data, I came up with this:

Code:
SELECT TOP 5 ORDER_DETAIL.PRODUCT,
ORDER_HEADER.SHIP_TO AS CUST_ID,
SUM(ORDER_DETAIL.SHIP_QTY) AS CUST_SOLD,
ITEM_TTL.TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
INNER JOIN (SELECT TOP 5 ORDER_DETAIL.PRODUCT,
SUM(ORDER_DETAIL.SHIP_QTY) AS TTL_SOLD
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID=ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC) AS ITEM_TTL ON ORDER_DETAIL.PRODUCT=ITEM_TTL.PRODUCT
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL)) AND (ORDER_DETAIL.PRODUCT IN
(SELECT TOP 5 ORDER_DETAIL.PRODUCT
FROM ORDER_HEADER INNER JOIN
ORDER_DETAIL ON
ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID
WHERE (NOT (ORDER_HEADER.SHIP_DATE IS NULL))
GROUP BY ORDER_DETAIL.PRODUCT
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC))
GROUP BY ORDER_HEADER.SHIP_TO, ORDER_DETAIL.PRODUCT, ITEM_TTL.TTL_SOLD
ORDER BY SUM(ORDER_DETAIL.SHIP_QTY) DESC
This returns:
Product Cust_ID Cust_Sold Ttl_Sold
F.........12.........1035......2700
B.........15.........672.......2457
D.........7.........1243......1972
C.........91.........814.......1757
A.........18.........593.......1549

thank you very much mate for putting in the effort to solve this conundrum
:-). I appreciate it i really do ^^|||Am i assuming that no one knows the answer? :confused:
lol, kinda funny a problem that even professionals can't figure out :p
i on the other hand will help people regardless of the complexity of the problem,
When he never replied after that post... i just assumed that he gave up and admitted defeat...
YOU.... Probably won't even read this
You were acting like a complete twit. The fact that you seem oblivious to this I will chalk up to immaturity. Hopefully, you will soon grow up enough to be respectful to people who are volunteering their time and expertise to help you out.|||I suspect the TOP 5 in the outer query is redundant, since you have it included in your nested subquery.

I intended the top 5 in the outer query to pull the top 5 customers, while
the top 5 in the nested query is pulling just the top 5 products.sql

for xml explicit question

I've written some for xml explicit sql, now it works fine but what i want to
do now is add another set of elements at level 2.
For example:
<root>
<thingy number="1"/>
<thingy number="2"/>
<thingy number="3"/>
</root>
is what works fine, but now what i want is to have:
<root>
<thingy number="1"/>
<thingy number="2"/>
<thingy number="3"/>
<blah number="1"/>
<blah number="2"/>
</root>
when i write the for xml explicit to do this, it says that the element for
level 2 is already defined. or something like that. the way i see it is
that "thingy" and "blah" are both at level 2 and so should both have a tag
of 2 and a parent of 1. i could make the tag of "blah" equal to 3 but
wouldnt that make it appear under "thingy"... as (according to my book) tag
is effectively the nesting level?
i can write out full example code for you, but just for now, is there
anything obvious that i'm missing? its amazing how little resources are out
there, that talk about unusual xml explicit code!
thanks
Paul
The number you assign in the query isn't a level - it's a tag identifier. So
tags, 1 and 2 can be at the same level, they're just different tags. The
thing that determines the level is the parent, and you can assign the same
parent to as many tags as you like.
here's an example from the Northwind database
SELECT 1 As TAG, NULL As Parent,
ProductID AS [thingy!1!Number],
NULL AS [blah!2!Number]
FROM Products
UNION
SELECT 2 AS TAG, NULL AS Parent,
NULL,
CategoryID
FROM Categories
FOR XML EXPLICIT
As you'll see from the results, both thingy and blah are at the same level.
Hope that helps,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
http://www.microsoft.com/mspress/books/6137.asp
"Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
news:c66778$8kic9$1@.ID-141222.news.uni-berlin.de...
> I've written some for xml explicit sql, now it works fine but what i want
to
> do now is add another set of elements at level 2.
> For example:
> <root>
> <thingy number="1"/>
> <thingy number="2"/>
> <thingy number="3"/>
> </root>
> is what works fine, but now what i want is to have:
> <root>
> <thingy number="1"/>
> <thingy number="2"/>
> <thingy number="3"/>
> <blah number="1"/>
> <blah number="2"/>
> </root>
> when i write the for xml explicit to do this, it says that the element for
> level 2 is already defined. or something like that. the way i see it is
> that "thingy" and "blah" are both at level 2 and so should both have a
tag
> of 2 and a parent of 1. i could make the tag of "blah" equal to 3 but
> wouldnt that make it appear under "thingy"... as (according to my book)
tag
> is effectively the nesting level?
> i can write out full example code for you, but just for now, is there
> anything obvious that i'm missing? its amazing how little resources are
out
> there, that talk about unusual xml explicit code!
> thanks
> Paul
>
|||Thanks! I adjusted my XML accordingly and it works, beautifully.
Its amazing how simple this all is, when you get your head round it (famous
last words until my next problem, hehe). btw I found removing the for xml
explicit part and looking at the virtual (?) table is a big help on the way
to enlightenment
Thanks again
Paul
Graeme Malcolm (Content Master Ltd.) wrote:[vbcol=seagreen]
> The number you assign in the query isn't a level - it's a tag
> identifier. So tags, 1 and 2 can be at the same level, they're just
> different tags. The thing that determines the level is the parent,
> and you can assign the same parent to as many tags as you like.
> here's an example from the Northwind database
> SELECT 1 As TAG, NULL As Parent,
> ProductID AS [thingy!1!Number],
> NULL AS [blah!2!Number]
> FROM Products
> UNION
> SELECT 2 AS TAG, NULL AS Parent,
> NULL,
> CategoryID
> FROM Categories
> FOR XML EXPLICIT
> As you'll see from the results, both thingy and blah are at the same
> level.
> Hope that helps,
> Graeme
>
> "Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
> news:c66778$8kic9$1@.ID-141222.news.uni-berlin.de...

FOR UPDATE clause in cursor

I'm refactoring a cursor written by another employee. It includes a cursor
that uses the FOR UPDATE clause to update records as the cursor iterates
through the records. Please assume that the cursor cannot/should not be
replaced.
When executing the snippet of code below, I get the SQL Server error below.
What syntax would allow me to make the cursor read/write rather than read
only? I tried making the cursor DYNAMIC, but this did not appear to help.
Thanks in adavnce.
-Mark
Server: Msg 16957, Level 16, State 4, Procedure p_om_daily_reload, Line 402
FOR UPDATE cannot be specified on a READ ONLY cursor.
********************** SNIPPET OF CODE ****************************
declare @.audit_note varchar(200)
declare reg_curs_mentorhip_cancelled CURSOR DYNAMIC
FOR
-- Look for existing payment records using the reg_num that have been
marked as canceled in informix.
SELECT DISTINCT mp.mentorship_id, im.hours
FROM t_om_informix_mentorship im
INNER JOIN t_om_app_mentorship_payment mp
ON im.reg_num = mp.reg_num
WHERE im.reg_status = 'C'
FOR UPDATE -- MARK: Added to allow cursor data to be updated. Default is
read-only
OPEN reg_curs_mentorhip_cancelled
FETCH NEXT FROM reg_curs_mentorhip_cancelled INTO @.mentorship_id,
@.add_purchase
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
PRINT'* 4a , subtract the amount of time refunded'
UPDATE t_om_app_mentorship
SET time_purchased = time_purchased - @.add_purchase
WHERE mentorship_id = @.mentorship_id;
PRINT'* 4a , remove payment record'
DELETE t_om_app_mentorship_payment
WHERE CURRENT OF reg_curs_mentorhip_cancelled
PRINT'* 4a ,add audit trail'
SET @.audit_note = 'mentorship_id = ' + convert(varchar(15),
@.mentorship_id) + ', Hours affected: ' + CAST(@.add_purchase as varchar)
EXEC p_om_data_audit_trail_i @.user_key = 'unknown', @.priority = 1,
@.create_user = 'Reload_Script', @.category = 'Payments',
@.issue = 'Mentorship Payment either disappeared or was cancelled',
@.note = @.audit_note
END
FETCH NEXT FROM reg_curs_mentorhip_cancelled INTO @.mentorship_id,
@.add_purchase
END
CLOSE reg_curs_mentorhip_cancelled
DEALLOCATE reg_curs_mentorhip_cancelledThe cursor cannot be updateable because of the DISTINCT modifier in the
query.

> Please assume that the cursor cannot/should not be
> replaced.
No chance of me assuming that! Cursors bad. Updateable cursors worse.
;-)
David Portas
SQL Server MVP
--|||>> Please assume that the cursor cannot/should not be replaced. <<
Is this a teaching exercise of some kind? "Look Igor, screwdriver
better than rocks for cabinet making!!" "Igor not like screwdriver!
Igor have rock!"
Based on ~20 years with SQL, ten years on the ANSI Standards committee
of SQL, and six books on the language, I find Kosher Pork easier to
assume -- Hey, we got genetic engineering and can fix those feet!
I have written five cursors in my career and I know that at least three
of them could have been done with a CASE expression. They would have
run **orders of magnitude** faster and been easierr to maintain.
Also, when you post code could you consistently uppercase reserved
words? Learn about semi-colons in SQL? ISO_11179 rules? Not use PRINT
in application code (it is for debugging only)? What about the basic
rule that audit trails are external to the procedures, so you know that
they valid?
You have code like an OO or COBOL program. The basic problem; this is
procedural code written in SQL, a declarative language. Your entire
approach and vocabulary are non-relational (Records in SQL').
Dave was nicer than me (who isn't?). EVERYTHING needs to be re-done
whiel there is still time. This is so crappy it is not worh saving and
might have screwed up data integrity already.