Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Unanswered: conversion sql from oracle to SQL Server

    Hi Guys, I have this statement that I am converting from Oracle to SQL. Help pls:-) PP_PRICEPOINT_ID is a decimal. What is the appropriate usage..

    Oracle
    -------------
    update pricepoint set pp_type = decode(substr(pp_pricepoint_id,1,1),7,0,2),
    pp_qtybreakindex =substr(pp_pricepoint_id,3,1) where pp_type is null and pp_qtybreakIndex is null;

    Here is its SQL
    -----------------
    UPDATE pricepoint
    SET pp_type =
    CASE SUBSTRING(pp_pricepoint_id, 1, 1)
    WHEN 7 THEN 0
    ELSE 2
    END,
    pp_qtybreakindex = SUBSTRING(pp_pricepoint_id, 3, 1)
    WHERE pp_type is null
    AND pp_qtybreakIndex is null
    ----------------
    I am getting the error
    The data type decimal is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.
    Kishore

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    UPDATE pricepoint 
       SET pp_type =  CASE SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 1, 1) 
    		  WHEN 7 THEN 0 ELSE 2 
    		  END
    -- What's with this?
    --		, pp_qtybreakindex = SUBSTRING(pp_pricepoint_id, 3, 1) 
     WHERE pp_type is null
       AND pp_qtybreakIndex is null
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2004
    Posts
    3
    Try converting your pp_pricepoint variable to varchar before using the substring function

    Change :

    CASE SUBSTRING(pp_pricepoint_id, 1, 1)

    For :

    CASE SUBSTRING(Cast(pp_pricepoint_id As VarChar) , 1, 1)

    Pls Note i didnt check ur substring use for the sintax.

    Hope it can help

  4. #4
    Join Date
    Mar 2004
    Posts
    84
    Thanks Brett, this worked..
    UPDATE pricepoint
    SET pp_type = CASE SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 1, 1)
    WHEN 7 THEN 0 ELSE 2
    END
    , pp_qtybreakindex = SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 3, 1)
    WHERE pp_type is null
    AND pp_qtybreakIndex is null

    I am updating 2 values here..
    Kishore

Posting Permissions

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