September 17, 2008 at 11:43 am
I wrote a cursor that copies one row to the next row with updated values. The problem is for the majority of the records everything worked fine. However, some rows have no logical reason to have incorrect record values. How would one troubleshoot that?
Sudo Script
Start Cursor
Copy Existing Active Row
create new row with a few updated rows....
how would i troubleshoot or debug this?
September 17, 2008 at 11:55 am
you'll get better responses if you can post what you are actually doing...theoretical questions get loosy goosy answers...the more detailed your question, the more detailed an answer you can receive.
you said "copying to the next row"...that could be a logical error, where you are thinking of the data as an excel spreadsheet, instead of a bunch of data...if you don't have an ORDER BY in your cursor, you could get incorrect values for your process.
well, from what you posted so far, i see two things...
chances are , if you need to insert rows that are related to an existing row, but have different values, you could skip the cursor and use a simple insert...select statement:
lame example below:
INSERT INTO MYTABLE(val1,val2)
--join on the same table and only insert values that do not exist.
SELECT val1, (val2 + 2) FROM MYTABLE
LEFT OUTER JOIN MYTABLE X ON MYTABLE.val2 + 2 = X.val2
WHERE X.val1 IS NULL
if you continue with the cursor, it sounds like you are selecting ALL rows in the table, and not filtering it with a WHERE statement.
If you do that, then INSIDe the cursor, you need a logical IF statement to determine whether a row really needs to be processed...
ie
IF EXISTS(SELECT 1 FROM MYTABLE WHERE VAL1 = @cursorVariable)
BEGIN
..doo an insert
END
finally, you said "some rows have no logical reason to have incorrect record values" if you can identify which rows should not be processed, that is the WHERE statement you need to filter your data with...
Lowell
September 17, 2008 at 12:37 pm
the team i work for makes using cursors the standard for doing any data manipulation when a lot of simple statements would do. I know I'm complaining. Blah blah.
Here is the script
DECLARE @ChargeCodeDE int
DECLARE @RVU real
DECLARE @NewRVU real
DECLARE @EffectiveDT datetime
DECLARE @EntryMnemonic varchar(10)
DECLARE @IPTech money
DECLARE @IPProf money
DECLARE @OPTech money
DECLARE @OPProf money
DECLARE @DefAmt smallmoney
DECLARE @WLU real
DECLARE @ChargeCodeType char(1)
DECLARE @CanEditInBillingFLAG char(1)
DECLARE @ServiceDE int
DECLARE @DepartmentDE int
DECLARE @IsInactiveFLAG char(1)
DECLARE @ApplyModsDefaultFLAG char(1)
DECLARE @EffectiveEndDT datetime
DECLARE SwapRVU CURSOR FOR
SELECT * FROM mergeddata
/* TempRVU needs to have ChargeCodeDE, NewRVU */
OPEN SwapRVU
FETCH NEXT FROM SwapRVU INTO @ChargeCodeDE, @NewRVU
WHILE @@FETCH_STATUS = 0
BEGIN
IF @NewRVU IS NOT NULL
BEGIN
-- Run a copy statement for existing row w/new rvu and new effective (now)
SELECT
@EffectiveDT=EffectiveDT,
@EntryMnemonic=EntryMnemonic,
@IPTech=IPTechnicalCharge,
@IPProf=IPProfessionalCharge,
@OPTech=OPTechnicalCharge,
@OPProf=OPProfessionalCharge,
@DefAmt=DefaultChargeAmount,
@RVU=RelativeValueUnit,
@WLU=WorkloadUnits,
@ChargeCodeType=ChargeCodeType,
@CanEditInBillingFLAG=CanEditInBillingFLAG,
@ServiceDE=ServiceDE,
@DepartmentDE=DepartmentDE,
@IsInactiveFLAG=IsInactiveFLAG,
@ApplyModsDefaultFLAG=ApplyModifiersByDefaultFLAG,
@EffectiveEndDT=EffectiveEndDT
FROM Charge_Code_Fee_Schedule
WHERE ChargeCodeDE = @ChargeCodeDE and EffectiveEndDT IS NOT NULL
Some how @IPTech is getting incorrect data for rows. Unsure why it does not happen for all rows.
September 17, 2008 at 1:36 pm
Well, you didn't include the insert portion of the procedure - but I am not sure that is needed in this case.
My guess is that for some Charge Codes - you can have multiple entries in the Charge code table. Most likely, you should only have a single row for each charge code that does not have an effective date. It is possible that there are multiple rows being returned, and in that case - you don't have any idea which one is going to be returned.
The other possibility is that there is no match on the charge code. When there is no match, the previously set values will carry over in the variables and you would insert a new row with values that are incorrect.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 17, 2008 at 1:51 pm
The record should only return 1 result because only one record can have an effectiveenddt null at any given time otherwise there would be lots of errors generated in the app itself.
how can i add error handling to say
if @copiedIPTech is different from @newrowIPTech
print values this way i can see when the data problem starts occurring
September 17, 2008 at 2:20 pm
Try running the following query:
SELECT ChargeCodeDE, count(*)
FROM Charge_Code_Fee_Schedule
WHERE EffectiveEndDT IS NOT NULL;
For every row that has a count > 1 - you will have a problem. Now, change the query to:
SELECT ChargeCodeDE, count(*)
FROM Charge_Code_Fee_Schedule
WHERE EffectiveEndDT IS NULL
HAVING count(*) > 1;
If you get any of these returned - those are also going to cause you problems when you fix your query.
In your original statement:
IF @NewRVU IS NOT NULL
BEGIN
-- Run a copy statement for existing row w/new rvu and new effective (now)
SELECT
@EffectiveDT=EffectiveDT,
@EntryMnemonic=EntryMnemonic,
@IPTech=IPTechnicalCharge,
@IPProf=IPProfessionalCharge,
@OPTech=OPTechnicalCharge,
@OPProf=OPProfessionalCharge,
@DefAmt=DefaultChargeAmount,
@RVU=RelativeValueUnit,
@WLU=WorkloadUnits,
@ChargeCodeType=ChargeCodeType,
@CanEditInBillingFLAG=CanEditInBillingFLAG,
@ServiceDE=ServiceDE,
@DepartmentDE=DepartmentDE,
@IsInactiveFLAG=IsInactiveFLAG,
@ApplyModsDefaultFLAG=ApplyModifiersByDefaultFLAG,
@EffectiveEndDT=EffectiveEndDT
FROM Charge_Code_Fee_Schedule
WHERE ChargeCodeDE = @ChargeCodeDE and EffectiveEndDT IS NOT NULL
Notice that you are looking for all matches where the EffectiveEndDT IS NOT NULL. This will match up with the first query above. When you fix this (change it to EffectiveEndDT IS NULL), then if there are any rows returned from the second query - you will have a problem with those.
And finally, what happens if the variable @ChargeCodeDE does not match a ChargeCodeDE in the Charge_Code_Fee_Schedule table? The query returns a null set and does not set any of the variables since the variables will only be set if the query returns data.
You can add a check after the above statement to check the number of rows returned, and do something if the number of rows <> 0. Example:
IF @@rowcount = 0
BEGIN;
raiserror('No matches found', 16, 1);
END;
IF @@rowcount > 1
BEGIN;
raiserror('Too many rows returned', 16, 1);
END;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 17, 2008 at 2:57 pm
wow i'm an idiot. thanks for your help. to bad there is not some gift card service to give to bulletin board members for good help.
September 17, 2008 at 3:03 pm
I wouldn't say that - I didn't see it the first time through either. 😀
BTW - with that condition I would be verifying the table for DRI and/or triggers that would insure that I could not have multiple rows where the effective date is null.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 17, 2008 at 7:13 pm
joshua.a.anderson (9/17/2008)
wow i'm an idiot. thanks for your help. to bad there is not some gift card service to give to bulletin board members for good help.
LOL! PayPal always works... 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2008 at 7:25 pm
Jeff,
I don't understand. The table does allow multiple records to have null values for effectiveEndDT. I am sure there has to be triggers on the table as it is related to billing. What is DRI?
September 17, 2008 at 7:33 pm
How could I check for both?
September 17, 2008 at 7:42 pm
Declarative Referential Integrity (DRI)
From:
http://www.cvalde.net/document/declaRefIntegVsTrig.htm
This is a metadata declaration that enforces a dependency relationship between two tables through one or more fields. So, the engine itself is in charge of verifying consistency across tables; you can think that one table "refers" to other for validation and hence the name "referential integrity".
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply