Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2003
    Posts
    6

    Unanswered: Stored Procedure

    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.

  2. #2
    Join Date
    Dec 2003
    Posts
    74
    is this pl/sql?

  3. #3
    Join Date
    Aug 2003
    Posts
    40
    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.

  4. #4
    Join Date
    Dec 2003
    Posts
    6
    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?

  5. #5
    Join Date
    Dec 2003
    Posts
    74
    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

  6. #6
    Join Date
    Dec 2003
    Posts
    6
    Not quite, I am trying to set the price for the part that ends with -R.

  7. #7
    Join Date
    Dec 2003
    Posts
    6
    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.

  8. #8
    Join Date
    Aug 2003
    Posts
    40
    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???

  9. #9
    Join Date
    Dec 2003
    Posts
    6
    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.

  10. #10
    Join Date
    Dec 2003
    Posts
    3
    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,

  11. #11
    Join Date
    Aug 2003
    Posts
    40
    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

  12. #12
    Join Date
    Aug 2003
    Posts
    40
    Sorry, vTemp is not a VARCHAR2(2). I'd type it the same as whatever
    your typing the "StockNumber" variable.

  13. #13
    Join Date
    Dec 2003
    Posts
    6
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •