Hi everyone. I am having difficulty with the following stored procedure:

CREATE procedure A_sp_UpdatePricing_R_parts_test as


/* Declare variables */

Begin
Declare @SellingPrice decimal(9),
@StockCode varchar(30)


/* Declare cursor and open for processing */

Declare System_Cursor Cursor for Select StockCode from InvMaster where StockCode in ('03-18320-00')

Open System_Cursor

/* Fetch value into cursor */

Fetch Next from System_Cursor into @StockCode
While @@Fetch_Status = 0
Begin

Set @SellingPrice = Null




Select @SellingPrice = SellingPrice from InvPrice where StockCode = @StockCode

If @SellingPrice >0


Update InvPrice set SellingPrice = 222
Where (StockCode = @StockCode and StockCode like '%R')





Fetch Next from System_Cursor into @StockCode
End

/* Close cursor */

Close System_Cursor
Deallocate System_Cursor
End
GO


What it should do is we have part numbers that end with -R or R for refurbished and most of these parts have regular part numbers that don't end in R. I am supposed to get the price for the regular one, discount it by 15% and use that to set the price for the refurbished part.

I don't know how to set the price i get for the part that is equal to the part in question but has an R in the end, which stands for refurbished.

Any help would be much appreciated.

is this pl/sql?

Well, regardless of your database programming language, your
question is, I believe, somewhat mentioned in your last paragraph...

"I don't know how to set the price i get for the part that is equal to the part in question but has an R in the end, which stands for refurbished."

"the part that is equal to the part in question" is what is throwing me.

Are you wanting to perform a formula based upon the results of
rows coming from the database that end with the letter "R"??? If
so, what are the variables associated with this? If your only asking
if the row in question ends with an "R", that's a string function.

Ok, here's the deal. Say we have a robot in stock. Now, say its' part number is 03-0111-25. There's also that same robot but it's refurbished and is assigned the same part number but with -R in the end(03-0111-25-R). What I need to do is get the price of the regular part, discount it by 15% and assign that price to the refurbished robot. The trick is how do I represent the @StockCode value with -R in the end? Is there some other way?

so you want to display the discounted price with an 'R' appended to the end?

Just do a to_char on the price and concatenate the 'R' onto the end


1* select to_char(1000.22) || 'R' as price from dual
SQL> /

PRICE
--------
1000.22R


Is this what u mean

Not quite, I am trying to set the price for the part that ends with -R.

To be exact, I am trying to make SQL look for the part number that has the value that of in the @StockCode but that also has -R in the end and set the calculated price for that item.

I think were dealing with something like this..

Lets say you retriev two rows:
Row1 has a stock number of 17-2222
Row 2 has a stock number of 17-2222-R

Both, I guess will have the same price and only Row 2 gets
the discount of 15%.

So, analyze each row and perform a string function to determine
if your dealing with a refurbished item (such as Row2). If so,
take the amount and multiply by .85.

If Row2 will not have a price, temp obtain the actual stock number
by re-visiting the table with a "where" clause that strips-off the "-R"
text to obtain the stock price for (in this case) Row1's price and then
calculate the discount.

Is this what your talking about???

That's exactly what I am talking about. But I don't quite know how to strip=off or add -R to the value @StockCode. Also, the -R parts don't have prices currently, so I have to obtain the price and then populate the fields.

Thanks a lot for your help.

You could acheve the result with the following two update statements.

You could add more conditions or join other tables as need be.

--to update sellingprice for stockcode ending with -R

update invprice invp
set invp.sellingprice =
(select (invp1.sellingprice * .85) from invprice invp1 where invp1.stockcode = substr(invp.stockcode,1, length(invp.stockcode)-2)
where invp.stockcode like '%-R';

--to update sellingprice for stockcode ending with R
update invprice invp
set invp.sellingprice =
(select (invp1.sellingprice * .85) from invprice invp1 where invp1.stockcode = substr(invp.stockcode,1, length(invp.stockcode)-1)
where invp.stockcode like '%R'
and invp.stockcode not like '%-R';


Thanks,

Okay - I'm gonna assume your using PL/SQL.

If you've retrieved into a variable a value from the database, the
string function to determine if it ends with a "-R" is


IF SUBSTR(vStockNbr, LENGTH(vStockNbr)-1 = '-R' THEN
vTemp := SUBSTR(vStockNbr,1,LENGTH(vStockNbr)-2);
END IF;

Where vTemp is a VARCHAR2(2).

Now, requery the database to get the price of the item that is its
equivalent. I take it if there's a Stock # of 222-R, there must also be
its equivalent non-reburished row, assigned as 222, otherwise handle
the exception appropriately.

BEGIN
SELECT price
INTO vTempPrice
FROM myStockTable
WHERE stock_number = vTemp;

EXCEPTION
WHEN OTHERS THEN
vTempPrice := -1;
END;

Now, simply multiply vTempPrice by .85 to get the discount if
vTempPrice > 0.

OK

Sorry, vTemp is not a VARCHAR2(2). I'd type it the same as whatever
your typing the "StockNumber" variable.

Thanks for your fast response. The language in question is MS SQL. All I basically need is to see if there's a way to concatenate -R value to the whatever value is in @StockCode. If I get something like:

update InvPrice set SellingPrice = @SellingPrice * 0.85 where StockCode = @StockCode + '-R' or 'R' basically meaning look for the row where StockCode begins with the value in the @StockCode variable and ends with R. Is there a string or an operator I can do it with?