Not sure if I need to loop... 2

[ajaeger]

SQL 2005.

I have and Orders and Order_Lines table. There is one record in the Orders table and one or more corresponding records in the Order_Lines table. For each order (determined by Orders.ORDER_NUMBER), I need to concatenate a few fields from the Order_Lines table. The syntax I have below is close to what I need, except that it only returns one row where it concatenates the Order_Lines fields from ALL of the orders instead of returning multiple rows, one for each order.


Declare @Result varchar(8000)
Declare @Order_Number float

Create table #Results (ORDER_NUMBER float, FUNCTIONS text)

Select @Result =
IsNull(
@Result
+ ','
+ char(13)
+ Order_Lines.DESCRIPTION
+ ' - Qty. '
+ convert(varchar(10), Order_Lines.QUANTITY_ORDERED)
+ ' - $'
+ convert(varchar(10),Order_Lines.EXTENDED_AMOUNT)
,
+ char(13)
+ Order_Lines.DESCRIPTION
+ ' - Qty. '
+ convert(varchar(10), Order_Lines.QUANTITY_ORDERED)
+ ' - $'
+ convert(varchar(10),Order_Lines.EXTENDED_AMOUNT)
),
@Order_Number=Orders.ORDER_NUMBER
From Order_Lines, Orders, Order_Meet, Product_Function
Where Orders.ORDER_NUMBER=Order_Lines.ORDER_NUMBER and Orders.ORDER_NUMBER=Order_Meet.ORDER_NUMBER and
Order_Lines.PRODUCT_CODE=Product_Function.PRODUCT_CODE and
Order_Meet.MEETING='ABC10' and Orders.STATUS not in ('C','CT') and Order_Lines.QUANTITY_ORDERED>=1
Group by Orders.ORDER_NUMBER, Product_Function.SEQ, Order_Lines.DESCRIPTION, Order_Lines.QUANTITY_ORDERED, Order_Lines.EXTENDED_AMOUNT

Insert into #Results (ORDER_NUMBER, FUNCTIONS)
Select @Order_Number, @Result

select * from #Results

drop table #Results


What I get is this:
ORDER_NUMBER FUNCTIONS
22817 20X20 Booth - Qty. 1 - $9000.00,
Courtesy Discount - Qty. 1 - $-674.00,
Member Discount - Qty. 4 - $-2260.00,
10X20 Booth - Qty. 1 - $4500.00,
Corner Booth Fee - Qty. 1 - $200.00,
Member Discount - Qty. 2 - $-1130.00,
10X20 Booth - Qty. 1 - $4500.00

The above isn't correct as the functions are actually from 3 different orders. What I should see is:
ORDER_NUMBER FUNCTIONS
22817 20X20 Booth - Qty. 1 - $9000.00,
Courtesy Discount - Qty. 1 - $-674.00,
Member Discount - Qty. 4 - $-2260.00,
22818 10X20 Booth - Qty. 1 - $4500.00,
Corner Booth Fee - Qty. 1 - $200.00,
Member Discount - Qty. 2 - $-1130.00,
22819 10X20 Booth - Qty. 1 - $4500.00

Is it a loop that I need? How do I start to construct this? Thanks.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[bborissov]

ORDER_NUMBER - FLOAT??????????????????
WHY?

Create a function which will return what you need:

CREATE FUNCTION dbo.GetOrdersDesc(@OrderNumber float)
RETURNS varchar(max)
AS
     DECLARE @RetVal varchar(max)
     SET @RetVal = ''
     SELECT @RetVal
            + ','
            + char(13)
            + Order_Lines.DESCRIPTION
            + ' - Qty. '
            + convert(varchar(10), Order_Lines.QUANTITY_ORDERED)
            + ' - $'
            + convert(varchar(10),Order_Lines.EXTENDED_AMOUNT)
            ,
            + char(13)
            + Order_Lines.DESCRIPTION
            + ' - Qty. '
            + convert(varchar(10), Order_Lines.QUANTITY_ORDERED)
            + ' - $'
            + convert(varchar(10),Order_Lines.EXTENDED_AMOUNT)
     FROM Order_Lines
     WHERE Order_Lines.ORDER_NUMBER = @OrderNumber
     RETURN @RetVal
 END

And than use it that way:

SELECT Order_Number,
       dbo.GetOrdersDesc(Orders.Order_Number) AS Description
FROM Orders

not tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ajaeger]

When running this function, I was getting an error - "Select statements included within a function cannot return data to a client." From what I could read, the solution was to use a procedure instead.

Here's what I have, however, when I run an execute stmt, it just hangs.

CREATE PROCEDURE dbo.GetOrdersDesc(@OrderNumber int)
AS
Set NOCOUNT on
Declare @Order_Lines as int, @Counter as int, @Functions as varchar(max)
Set @Counter = 1
Set @Order_Lines =
(Select count(*) from Order_Lines, Orders Where Order_Lines.ORDER_NUMBER=@OrderNumber and
Orders.ORDER_NUMBER=Order_Lines.ORDER_NUMBER and Orders.STATUS not in ('C','CT') and Order_Lines.QUANTITY_ORDERED>=1)
Begin
While @Counter < @Order_Lines
Select @Functions = @Functions +
Order_Lines.DESCRIPTION
+ ' - Qty. '
+ convert(varchar(10), Order_Lines.QUANTITY_ORDERED)
+ ' - $'
+ convert(varchar(10),Order_Lines.EXTENDED_AMOUNT)
From Order_Lines, Orders, Order_Meet, Product_Function
Where Orders.ORDER_NUMBER=@OrderNumber and Orders.ORDER_NUMBER=Order_Lines.ORDER_NUMBER and
Orders.ORDER_NUMBER=Order_Meet.ORDER_NUMBER and Order_Lines.PRODUCT_CODE=Product_Function.PRODUCT_CODE and
Orders.STATUS not in ('C','CT') and Order_Lines.QUANTITY_ORDERED>=1
Group by Orders.ORDER_NUMBER, Product_Function.SEQ, Order_Lines.DESCRIPTION, Order_Lines.QUANTITY_ORDERED, Order_Lines.EXTENDED_AMOUNT
Set @Counter=@Counter+1
End

What I need is to pass in an order number, 100 for example. Then, I need to look at the records in the Order_Lines table for that order number. I have @Order_Lines holding the count of those Order_Lines for that particular order number. Then I want to loop through those order lines and concatenate everything into one field.

So my Order_Lines table looks like this:
ORDER_NUMBER DESCRITION QUANTITY_ORDERED EXTENDED_AMOUNT
100 ABC 1 50.00
100 DEF 1 25.00
100 GHI 2 20.00
100 JKL 1 100.00

The end result that I am trying to get to is the following concatenated into one field, @Functions:
ABC - Qty. 1 - $50.00
DEF - Qty. 1 - $25.00
GFI - Qty. 2 - $40.00
JKL - Qty. 1 - $100.00

Running exec GetOrdersDesc '100' just hangs.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[bborissov]

Sorry, I just copy and paste your SELECT w/o proper look in it and removed ISNULL function. The code should be:

CREATE FUNCTION dbo.GetOrdersDesc(@OrderNumber float)
RETURNS varchar(max)
AS
     DECLARE @RetVal varchar(max)
     SELECT ISNULL(@RetVal
            + ','
            + char(13),'')
            + Order_Lines.DESCRIPTION
            + ' - Qty. '
            + convert(varchar(10), Order_Lines.QUANTITY_ORDERED)
            + ' - $'
            + convert(varchar(10),Order_Lines.EXTENDED_AMOUNT)
     FROM Order_Lines
     WHERE Order_Lines.ORDER_NUMBER = @OrderNumber
     RETURN @RetVal
 END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ajaeger]

I noticed that. No problem. But using the function above still gives me the error that is leading me to the procedure that I can't get to work. Any thoughts on this? Thanks.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[bborissov]

My mistake (again)

ALTER FUNCTION dbo.GetOrdersDesc(@OrderNumber float)
RETURNS varchar(max)
AS
BEGIN
     DECLARE @RetVal varchar(max)
     [COLOR=red][b]SELECT @RetVal = [/b][/color]ISNULL(@RetVal
            + ','
            + CHAR(13)+CHAR(10),'')
            + Order_Lines.DESCRIPTION
            + ' - Qty. '
            + convert(varchar(10), Order_Lines.QUANTITY_ORDERED)
            + ' - $'
            + convert(varchar(10),Order_Lines.EXTENDED_AMOUNT)
     FROM Order_Lines
     WHERE Order_Lines.ORDER_NUMBER = @OrderNumber
     RETURN @RetVal
 END

I forgot to assign value to @RetVal

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ajaeger]

Thanks bborissov! I'm so close now. Here's what I have:

--CREATE THE FUNCTION

create FUNCTION [dbo].[FuncGetOrdersDesc](@OrderNumber float)
RETURNS varchar(max)
AS
BEGIN
DECLARE @RetVal varchar(max)
SELECT @RetVal = ISNULL(@RetVal + ',' + CHAR(13)+CHAR(10),'') + Order_Lines.DESCRIPTION + ' - Qty. ' + convert(varchar(10), Order_Lines.QUANTITY_ORDERED) + ' - $' + convert(varchar(10),Order_Lines.EXTENDED_AMOUNT)
FROM Order_Lines
WHERE Order_Lines.ORDER_NUMBER = @OrderNumber and Order_Lines.QUANTITY_ORDERED>1
RETURN @RetVal
END

--CREATE TRIGGER

Create trigger UpdateMeetFunc
On Orders
After Update As
If Update(UPDATED_DATE_TIME)
Begin
Update Order_Meet
Set Order_Meet.UF_10 = (SELECT dbo.FuncGetOrdersDesc(Orders.Order_Number)FROM Orders)
End

What I'm seeing
When I run
SELECT dbo.FuncGetOrdersDesc(Orders.Order_Number) AS Description, Order_Number FROM Orders WHERE Order_Number=22018
I see what I expect to - it returns the correct Order_Lines records for order # 22018.

But when I try to pair it with the trigger that fires when the order is updated, I get an error.

"2100:[MIcrosoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, > >= or when the subquery is used as an expression. 0100:[Microsoft}{ODBC SQL Server Driver][SQL Server]The statment has been terminted."

I don't think I have the syntax for the trigger correct. How do I get the particular order number (in this case 22018, from the update on the order table for that order #) to pass to the function.

Thanks.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[markros]

;with cte as (
select Orders.Order_Number, dbo.FuncGetOrdersDesc(Orders.Order_Number) as Updated_Value from Orders)

update Order_Meet set
Set UF_10 = cte.Updated_Value from Order_Metter inner join cte on Order_Meet.Order_Number = cte.Order_Number

 

[ajaeger]

Can you explain what the 'cte' does? That gets it working almost perfectly. The function is fine (except I'm trying to control the sort on the way the Order_Lines go into @RetVal, but that's another issue). The trigger is so close. Here's what I have:

Create trigger UpdateMeetFunc
On Orders
After Update As
If Update(UPDATED_DATE_TIME)
Begin
;with cte as (
select Orders.Order_Number, dbo.FuncGetOrdersDesc(Orders.Order_Number) as Updated_Value from Orders)
update Order_Meet
Set UF_10 = cte.Updated_Value from Order_Meet inner join cte on Order_Meet.Order_Number = cte.Order_Number
End

When I run this stmt in SQL, I get the correct list of values in the UF_10 field:
SELECT dbo.FuncGetOrdersDesc(Orders.Order_Number) AS Description FROM Orders WHERE Order_Number=22800

Results:
ABC - Qty. 1 - $50.00
DEF - Qty. 1 - $25.00

But, when I update the Orders table from within the database (ie, the trigger fires), the UF_10 field contains a preceding comma and hard return.

Results:
,
ABC - Qty. 1 - $50.00
DEF - Qty. 1 - $25.00

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[markros]

CTE is common table expressions that were introduced in SQL Server 2005. I think you should try to read up on them a bit, they are very powerful. You can also read on recursive common table expressions.

Anyway, I'm not sure why do we have a problem with update.

Try running in SSMS

;with cte as (select Orders.Order_Number, dbo.FuncGetOrdersDesc(Orders.Order_Number) as Updated_Value from Orders)

select * from cte

to see if the results are correct using this syntax.

I was unable yesterday to figure out a different way to use this UDF in a trigger, may be there is some other way.

 

[ajaeger]

Running that in SSMS gave me the correct results, so it's something with the trigger. Will keep troubleshooting.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com