CURSOR with aView that has ORDER BY Doesn't work

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

Transact-SQL (2000)

CURSOR with aView that has ORDER BY Doesn't work

Author

Topic

00kevin
Yak Posting Veteran

78 Posts

Posted - 2004-04-19 : 16:43:20

ok here is my problem

I created a view that contains an ORDER BY clause and saved in in SQL Server

then I created a strored procedure that uses a CURSOR to iterate through that view.

When I execute the stored procedure it never finishes executing and nothing happens.

If I remove the ORDER BY clause from the view the stored procedure runs perfectly . Even if I remove the ORDER BY clause from the view and add it to the SELECT clause of the CURSOR it still fails to work.

Has anyone else encountered this problem?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-19 : 16:55:43

Have you tried adding PRINT statements to see where it is getting hung up? Why do you need a cursor for this? Perhaps if you posted your code, we could help you out here. When I used to use cursors, I was able to use ORDER BYs without any problems.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-19 : 17:00:56

Why not post the view and the cursor....

Brett

8-)

00kevin
Yak Posting Veteran

78 Posts

Posted - 2004-04-19 : 17:06:01

ok I solved the problem by adding the STATIC Extended Argument to the declare statment.

"DECLARE ARCursor CURSOR STATIC"

ok. I'll post the code so you guys can cut it up. The reason I've used a cursor is because the process is going to become more and more complicated as business rules are added.

ALTER PROCEDURE spAR_Invoice_Lease_Payments
(
@create_user_id int =0, -- 0 default indicates that the job was executed on schedule

)
AS

declare @customer_id int
declare @lease_id int
declare @contract_id int
declare @business_unit_id int

declare @payment_method_id int
declare @payment_due_id int
declare @payment_date datetime
declare @payment_amount money

declare @quantity int
declare @product_code varchar(25)
declare @product_code_description varchar(100)

--payment due / contract equipment related tax information for equipment
declare @tax1_name varchar(10)
declare @tax1_rate decimal(9)
declare @tax2_name varchar(10)
declare @tax2_rate decimal(9)
declare @is_compound bit
declare @tax1_taxable_amount money
declare @tax2_taxable_amount money
declare @tax_province_id int

--customer related tax information
declare @cust_is_tax1_exempt bit
declare @cust_is_tax2_exempt bit

--business unit information -same as 'remit payment to' info on invoices
declare @bu_logo_file_name varchar(50)
declare @bu_friendly_name varchar(50)
declare @bu_legal_name varchar(50)
declare @bu_location_name varchar(50)
declare @bu_address1 varchar(50)
declare @bu_address2 varchar(50)
declare @bu_address3 varchar(50)
declare @bu_city varchar(50)
declare @bu_postal_code varchar(50)
declare @bu_province_id int
declare @bu_customer_id int

-- bill to information
declare @to_location_name varchar(50)
declare @to_address1 varchar(50)
declare @to_address2 varchar(50)
declare @to_address3 varchar(50)
declare @to_city varchar(50)
declare @to_postal_code varchar(50)
declare @to_first_name varchar(50)
declare @to_middle_name varchar(50)
declare @to_last_name varchar(50)
declare @to_title varchar(50)
declare @to_province_id int

-- INTERNAL VARIABLES
declare @prev_customer_id int
declare @prev_business_unit_id int
declare @ar_header_id int
declare @ar_create_date datetime
declare @is_complete bit
declare @tax1_amount money
declare @tax2_amount money
declare @ar_source_id int

-- INITIALIZATIONS
set @ar_create_date = getDate()
set @is_complete = 0
set @ar_header_id =1
set @prev_customer_id = -1
set @prev_business_unit_id = -1
set @tax1_amount = 0
set @tax2_amount = 0
set @ar_source_id = 1

-- for the sake of speed it is best to use a combined view and only use one cursor.
DECLARE ARCursor CURSOR STATIC FOR

SELECT customer_id,
lease_id,
contract_id,
business_unit_id,

payment_method_id,
payment_due_id,
payment_date,
payment_amount,

quantity,
product_code,
product_code_description,

-- tax information for equipment
tax1_name,
tax1_rate,
tax2_name,
tax2_rate,

is_compound,
tax1_taxable_amount,
tax2_taxable_amount,
tax_province_id,

--customer related tax information
cust_is_tax1_exempt,
cust_is_tax2_exempt,

--business unit information -same as 'remit payment to' info on invoices
bu_logo_file_name,
bu_friendly_name,
bu_legal_name,
bu_location_name,

bu_address1,
bu_address2,
bu_address3,
bu_city,

bu_postal_code,
bu_province_id,

-- bill to information
to_location_name,
to_address1,
to_address2,
to_address3,

to_city,
to_postal_code,
to_first_name,
to_middle_name,

to_last_name,
to_title,
to_province_id

FROM vwBILLING_INVOICE_INFO_FOR_LEASE_PAYMENTS

OPEN ARCursor
FETCH NEXT FROM ARCursor
INTO

@customer_id,
@lease_id,
@contract_id,
@business_unit_id,

@payment_method_id,
@payment_due_id,
@payment_date,
@payment_amount,

@quantity,
@product_code,
@product_code_description,

--payment due / contract equipment related tax information for equipment
@tax1_name,
@tax1_rate,
@tax2_name,
@tax2_rate,

@is_compound,
@tax1_taxable_amount,
@tax2_taxable_amount,
@tax_province_id,

--customer related tax information
@cust_is_tax1_exempt,
@cust_is_tax2_exempt,

--business unit information -same as 'remit payment to' info on invoices
@bu_logo_file_name,
@bu_friendly_name,
@bu_legal_name,
@bu_location_name,

@bu_address1,
@bu_address2,
@bu_address3,
@bu_city,

@bu_postal_code,
@bu_province_id,
--@bu_customer_id,

-- bill to information
@to_location_name,
@to_address1,
@to_address2,
@to_address3,

@to_city,
@to_postal_code,
@to_first_name,
@to_middle_name,

@to_last_name,
@to_title,
@to_province_id

WHILE @@FETCH_STATUS = 0
BEGIN

if(@customer_id != @prev_customer_id) -- this trick avoids a nested cursor
begin

exec spAR_HEADER_save
@ar_header_id,
@ar_source_id,
@business_unit_id,
@customer_id,
@payment_date,
@payment_method_id,
@is_complete,
@ar_create_date,
@create_user_id

set @ar_header_id = @@Identity

exec spAR_HEADER_BILLING_HISTORY_save
0,
@ar_header_id,
@bu_legal_name,
@bu_location_name,

@bu_address1,
@bu_address2,
@bu_address3,
@bu_city,

@bu_postal_code,
@bu_province_id,
@to_location_name,
@to_address1,

@to_address2,
@to_address3,
@to_city,
@to_postal_code,

@to_province_id,
@to_first_name,
@to_middle_name,
@to_last_name,

@to_title,
@ar_create_date,
@create_user_id

exec spAR_HEADER_TAX_HISTORY_save
0,
@ar_header_id,
@tax_province_id,
@customer_id,
@ar_create_date,
@create_user_id
end

--set @prev_business_unit_id = @business_unit_id
set @prev_customer_id = @customer_id

-- calculate taxes
if (@cust_is_tax1_exempt !=1)
set @tax1_amount = @tax1_taxable_amount * @tax1_rate
else
set @tax1_amount = 0

if (@cust_is_tax2_exempt !=1)

if(@is_compound !=1)
set @tax2_amount = @tax2_taxable_amount * @tax2_rate
else
set @tax2_amount = @tax2_taxable_amount + @tax1_amount * @tax2_rate
else
set @tax2_amount = 0

exec spAR_DETAIL_save
0,
@ar_header_id,
0,
0,
@product_code_description,
@quantity,
@payment_amount,
@tax_province_id,
@tax1_amount,
@tax2_amount,
@lease_id,
@contract_id,
@ar_create_date,
@create_user_id

FETCH NEXT FROM ARCursor
INTO
@customer_id,
@lease_id,
@contract_id,
@business_unit_id,

@payment_method_id,
@payment_due_id,
@payment_date,
@payment_amount,

@quantity,
@product_code,
@product_code_description,

--payment due / contract equipment related tax information for equipment
@tax1_name,
@tax1_rate,
@tax2_name,
@tax2_rate,

@is_compound,
@tax1_taxable_amount,
@tax2_taxable_amount,
@tax_province_id,

--customer related tax information
@cust_is_tax1_exempt,
@cust_is_tax2_exempt,

--business unit information -same as 'remit payment to' info on invoices
@bu_logo_file_name,
@bu_friendly_name,
@bu_legal_name,
@bu_location_name,

@bu_address1,
@bu_address2,
@bu_address3,
@bu_city,

@bu_postal_code,
@bu_province_id,
--@bu_customer_id,

-- bill to information
@to_location_name,
@to_address1,
@to_address2,
@to_address3,

@to_city,
@to_postal_code,
@to_first_name,
@to_middle_name,

@to_last_name,
@to_title,
@to_province_id

END
CLOSE ARCursor

DEALLOCATE ARCursor

00kevin
Yak Posting Veteran

78 Posts

Posted - 2004-04-19 : 17:07:24

you asked for the code. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-19 : 17:09:00

quote:
Originally posted by 00kevin

The reason I've used a cursor is because the process is going to become more and more complicated as business rules are added.

As it becomes more complex, the cursor is going to become slower and slower and eventually grind to a halt. What is your code doing? Not programmatically what it is doing, but the business reason of it.

Tara

00kevin
Yak Posting Veteran

78 Posts

Posted - 2004-04-19 : 17:11:11

ALTER VIEW dbo.vwBILLING_INVOICE_INFO_FOR_LEASE_PAYMENTS
AS
SELECT TOP 100 PERCENT dbo.vwBILLING_LEASE_PAYMENTS_DUE.payment_due_id, dbo.vwBILLING_LEASE_PAYMENTS_DUE.lease_id,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.business_unit_id, dbo.vwBILLING_LEASE_PAYMENTS_DUE.contract_invoice_group_id,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.customer_id, dbo.vwBILLING_LEASE_PAYMENTS_DUE.payment_method_id,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.contract_id, dbo.vwBILLING_LEASE_PAYMENTS_DUE.payment_type_id,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.contract_equipment_id, dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax1_name,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax1_rate, dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax2_name,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax2_rate, dbo.vwBILLING_LEASE_PAYMENTS_DUE.is_compound,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax1_taxable_amount, dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax2_taxable_amount,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.payment_date, dbo.vwBILLING_LEASE_PAYMENTS_DUE.payment_amount,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.is_invoiced, dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_location_name,
dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_address1, dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_address2,
dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_address3, dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_city,
dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_postal_code, dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_first_name,
dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_middle_name, dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_last_name,
dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_title, dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_province_id,
dbo.vwBUSINESS_UNIT.bu_logo_file_name, dbo.vwBUSINESS_UNIT.bu_friendly_name, dbo.vwBUSINESS_UNIT.bu_legal_name,
dbo.vwBUSINESS_UNIT.bu_location_name, dbo.vwBUSINESS_UNIT.bu_address1, dbo.vwBUSINESS_UNIT.bu_address2,
dbo.vwBUSINESS_UNIT.bu_address3, dbo.vwBUSINESS_UNIT.bu_city, dbo.vwBUSINESS_UNIT.bu_postal_code,
dbo.vwBUSINESS_UNIT.bu_province_id, dbo.CONTRACT_EQUIPMENT.quantity, dbo.CONTRACT_PRODUCT.product_code,
dbo.CONTRACT_PRODUCT.product_code_description, dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax_province_id,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.is_tax1_exempt, dbo.vwBILLING_LEASE_PAYMENTS_DUE.is_tax2_exempt,
dbo.ROOT_TAX_INFO.is_tax1_exempt AS cust_is_tax1_exempt, dbo.ROOT_TAX_INFO.is_tax2_exempt AS cust_is_tax2_exempt
FROM dbo.vwBILLING_LEASE_PAYMENTS_DUE INNER JOIN
dbo.vwBILLING_CONTRACT_INVOICE_INFO ON
dbo.vwBILLING_LEASE_PAYMENTS_DUE.contract_invoice_group_id = dbo.vwBILLING_CONTRACT_INVOICE_INFO.contract_invoice_group_id INNER JOIN
dbo.vwBUSINESS_UNIT ON dbo.vwBILLING_LEASE_PAYMENTS_DUE.business_unit_id = dbo.vwBUSINESS_UNIT.business_unit_id INNER JOIN
dbo.CONTRACT_EQUIPMENT ON
dbo.vwBILLING_LEASE_PAYMENTS_DUE.contract_equipment_id = dbo.CONTRACT_EQUIPMENT.contract_equipment_id INNER JOIN
dbo.CONTRACT_PRODUCT ON dbo.CONTRACT_EQUIPMENT.contract_product_id = dbo.CONTRACT_PRODUCT.contract_product_id INNER JOIN
dbo.ROOT_TAX_INFO ON dbo.vwBILLING_LEASE_PAYMENTS_DUE.customer_id = dbo.ROOT_TAX_INFO.root_id AND
dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax_province_id = dbo.ROOT_TAX_INFO.tax_province_id
ORDER BY dbo.vwBILLING_LEASE_PAYMENTS_DUE.customer_id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-19 : 17:14:18

Was the view generated from Enterprise Manager? I've only seen SELECT TOP 100 PERCENT when it has been generated from EM. Views can not contain ORDER BYs unless there is a TOP clause. It does not appear that you need one. When you call the view, that's when you put the ORDER BY on it.

ALTER VIEW dbo.vwBILLING_INVOICE_INFO_FOR_LEASE_PAYMENTS
AS
SELECT dbo.vwBILLING_LEASE_PAYMENTS_DUE.payment_due_id, dbo.vwBILLING_LEASE_PAYMENTS_DUE.lease_id,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.business_unit_id, dbo.vwBILLING_LEASE_PAYMENTS_DUE.contract_invoice_group_id,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.customer_id, dbo.vwBILLING_LEASE_PAYMENTS_DUE.payment_method_id,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.contract_id, dbo.vwBILLING_LEASE_PAYMENTS_DUE.payment_type_id,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.contract_equipment_id, dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax1_name,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax1_rate, dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax2_name,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax2_rate, dbo.vwBILLING_LEASE_PAYMENTS_DUE.is_compound,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax1_taxable_amount, dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax2_taxable_amount,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.payment_date, dbo.vwBILLING_LEASE_PAYMENTS_DUE.payment_amount,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.is_invoiced, dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_location_name,
dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_address1, dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_address2,
dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_address3, dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_city,
dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_postal_code, dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_first_name,
dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_middle_name, dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_last_name,
dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_title, dbo.vwBILLING_CONTRACT_INVOICE_INFO.to_province_id,
dbo.vwBUSINESS_UNIT.bu_logo_file_name, dbo.vwBUSINESS_UNIT.bu_friendly_name, dbo.vwBUSINESS_UNIT.bu_legal_name,
dbo.vwBUSINESS_UNIT.bu_location_name, dbo.vwBUSINESS_UNIT.bu_address1, dbo.vwBUSINESS_UNIT.bu_address2,
dbo.vwBUSINESS_UNIT.bu_address3, dbo.vwBUSINESS_UNIT.bu_city, dbo.vwBUSINESS_UNIT.bu_postal_code,
dbo.vwBUSINESS_UNIT.bu_province_id, dbo.CONTRACT_EQUIPMENT.quantity, dbo.CONTRACT_PRODUCT.product_code,
dbo.CONTRACT_PRODUCT.product_code_description, dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax_province_id,
dbo.vwBILLING_LEASE_PAYMENTS_DUE.is_tax1_exempt, dbo.vwBILLING_LEASE_PAYMENTS_DUE.is_tax2_exempt,
dbo.ROOT_TAX_INFO.is_tax1_exempt AS cust_is_tax1_exempt, dbo.ROOT_TAX_INFO.is_tax2_exempt AS cust_is_tax2_exempt
FROM dbo.vwBILLING_LEASE_PAYMENTS_DUE INNER JOIN
dbo.vwBILLING_CONTRACT_INVOICE_INFO ON
dbo.vwBILLING_LEASE_PAYMENTS_DUE.contract_invoice_group_id = dbo.vwBILLING_CONTRACT_INVOICE_INFO.contract_invoice_group_id INNER JOIN
dbo.vwBUSINESS_UNIT ON dbo.vwBILLING_LEASE_PAYMENTS_DUE.business_unit_id = dbo.vwBUSINESS_UNIT.business_unit_id INNER JOIN
dbo.CONTRACT_EQUIPMENT ON
dbo.vwBILLING_LEASE_PAYMENTS_DUE.contract_equipment_id = dbo.CONTRACT_EQUIPMENT.contract_equipment_id INNER JOIN
dbo.CONTRACT_PRODUCT ON dbo.CONTRACT_EQUIPMENT.contract_product_id = dbo.CONTRACT_PRODUCT.contract_product_id INNER JOIN
dbo.ROOT_TAX_INFO ON dbo.vwBILLING_LEASE_PAYMENTS_DUE.customer_id = dbo.ROOT_TAX_INFO.root_id AND
dbo.vwBILLING_LEASE_PAYMENTS_DUE.tax_province_id = dbo.ROOT_TAX_INFO.tax_province_id

Tara

00kevin
Yak Posting Veteran

78 Posts

Posted - 2004-04-19 : 17:18:21

ok the code is going through leasing contracts and the associated payments due to generate invoices. (AR entries). It has to calculate taxes for each equipment on the leasing contract. and store the history of the billing information and the taxes charged. It is a rather complex process.

00kevin
Yak Posting Veteran

78 Posts

Posted - 2004-04-19 : 17:22:14

if you remove the top 100 percent it will give you this error when you try to alter it in QA
Server: Msg 1033, Level 15, State 1, Procedure vwBILLING_INVOICE_INFO_FOR_LEASE_PAYMENTS, Line 35
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

00kevin
Yak Posting Veteran

78 Posts

Posted - 2004-04-19 : 17:25:24

also.. if you remove the order by and add it when you call it within the CURSOR it still fails.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-19 : 17:25:34

Yes, that is why I mentioned that you should not have an ORDER BY in your VIEW. When you call the view, that's when you add an ORDER BY:

SELECT * --<--(should be a column list)
FROM vwBILLING_INVOICE_INFO_FOR_LEASE_PAYMENTS
ORDER BY dbo.vwBILLING_LEASE_PAYMENTS_DUE.customer_id

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-19 : 17:27:26

Have you added PRINT statements to determine where it is failing? Does it work if you add a WHERE clause in the cursor:

SELECT * --<--(should be a column list)
FROM vwBILLING_INVOICE_INFO_FOR_LEASE_PAYMENTS
WHERE...
ORDER BY dbo.vwBILLING_LEASE_PAYMENTS_DUE.customer_id

I suspect that it is actually working since you aren't getting an error, it's just very slow since it is using a cursor. I would narrow down the result set using a WHERE and test it to make sure it works. If it does, you know that it's just running very slow.

Tara

00kevin
Yak Posting Veteran

78 Posts

Posted - 2004-04-19 : 17:27:35

I tried doing that within the cursor but it doesn't work. the sp hangs

doing this fails:
DECLARE ARCursor CURSOR FOR

SELECT customer_id,
lease_id,
contract_id,
business_unit_id,

payment_method_id,
payment_due_id,
payment_date,
payment_amount,

quantity,
product_code,
product_code_description,

-- tax information for equipment
tax1_name,
tax1_rate,
tax2_name,
tax2_rate,

is_compound,
tax1_taxable_amount,
tax2_taxable_amount,
tax_province_id,

--customer related tax information
cust_is_tax1_exempt,
cust_is_tax2_exempt,

--business unit information -same as 'remit payment to' info on invoices
bu_logo_file_name,
bu_friendly_name,
bu_legal_name,
bu_location_name,

bu_address1,
bu_address2,
bu_address3,
bu_city,

bu_postal_code,
bu_province_id,

-- bill to information
to_location_name,
to_address1,
to_address2,
to_address3,

to_city,
to_postal_code,
to_first_name,
to_middle_name,

to_last_name,
to_title,
to_province_id

FROM vwBILLING_INVOICE_INFO_FOR_LEASE_PAYMENTS
ORDER BY dbo.vwBILLING_LEASE_PAYMENTS_DUE.customer_id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-19 : 17:28:46

When you say hangs, what do you mean? Is it in an infinite loop, is it actually processing but is very slow, etc...? How many rows of data are we talking about in your select statement?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-19 : 17:29:43

There was a post here a few months ago asking for help on how to improve the speed of his cursor. His cursor was taking 7 days to execute. After we were done with it, it executed in 21 minutes. We got rid of the cursor. I suspect that yours is just going to take a bit to execute. Add PRINT statements to determine if it is moving or not.

Tara

00kevin
Yak Posting Veteran

78 Posts

Posted - 2004-04-19 : 18:07:01

yes it appears to be in an infinite loop.

00kevin
Yak Posting Veteran

78 Posts

Posted - 2004-04-19 : 18:09:20

also there are only 16 records in the view

00kevin
Yak Posting Veteran

78 Posts

Posted - 2004-04-19 : 18:16:31

it is really strange. I tried again to remove the order by clause from the view and added it to the select statement of the Cursor and it still hangs(ie nothing happens it just says "executing query batch.." and I have to stop it. when I remove the order by clause it works perfecty.
the only way I can get it to work is to add the STATIC option to the declare statment.

I also tried making a scaled down test example, but I can't duplicate this issue. So it must have something to do with the code I wrote or that the view i'm using is just too complex for the cursor

00kevin
Yak Posting Veteran

78 Posts

Posted - 2004-04-19 : 18:18:41

I wouldn't mind removing the curor from this sp, but I'm not sure how to do the tax calculations.
-- calculate taxes
if (@cust_is_tax1_exempt !=1)
set @tax1_amount = @tax1_taxable_amount * @tax1_rate
else
set @tax1_amount = 0

if (@cust_is_tax2_exempt !=1)

if(@is_compound !=1)
set @tax2_amount = @tax2_taxable_amount * @tax2_rate
else
set @tax2_amount = @tax2_taxable_amount + @tax1_amount * @tax2_rate
else
set @tax2_amount = 0

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-20 : 02:37:39

You can certainly do this using...ummmm...slightly ugly looking derived tables and case statements, but it'll fly, certainly when compared to what you've got now, especially when you have thousands of records!



SELECT col1, col2, Tax1_Amount, 
	CASE WHEN cust_is_tax2_exempt = 1 THEN 0
		ELSE
		CASE WHEN is_compound = 1 THEN ((tax2_taxable_amount + Tax1_Amount) * tax2_rate) 
			ELSE (tax2_taxable_amount * tax2_rate) END
		END
	END AS Tax2_Amount
FROM
(
	SELECT col1, col2,
	 CASE WHEN cust_is_tax1_exempt = 1 THEN 0 ELSE (tax1_taxable_amount * tax1_rate) END AS Tax1_Amount,
	 tax2_taxable_amount, is_compound...
) A

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources