DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: MSSQL TO oracle

  1. #1
    Join Date
    Jan 2005
    Posts
    221

    MSSQL TO oracle

    How can I convert the below SQL from MSSQL to Oracle.

    select * from TEST_USER
    where (UPPER(USER_ID) = UPPER(CAST(@USER_ID as varchar)) OR @USER_ID is null)


    the @USER_ID is I want to have all of the records if it's NULL and matching records if not NUL.

    thanks

  2. #2
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Is user_id a primary key?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #3
    Join Date
    Jan 2005
    Posts
    221
    kEN,

    it's not PK.

    thanks,

  4. #4
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Is it indexed? What's it's datatype?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  5. #5
    Join Date
    Jan 2005
    Posts
    221
    kEN,

    SO SORRY , IT'S PRIMARY KEY AND VARCHAR

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Does it actually contain alpha-numeric values?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #7
    Join Date
    Jan 2005
    Posts
    221
    NO, it's parameter

    I have the parameter @user_id. I want all records if the parameter has a NULL value. I only want records that match the parameter if the parameter is not null. Because the parameter can be null, I have to cast it to a varchar before I send it into the UPPER function in MSSQL
    Last edited by hannah00; 07-05-2005 at 03:23 PM.

  8. #8
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    user_id like decode(@user_id,null,'%',@user_id);

    Here's the test case:
    create table test_user (user_id number);
    desc test_user;
    --Name Null? Type
    --USER_ID NUMBER
    insert into test_user values (1);
    insert into test_user values (2);
    select * from test_user where user_id like decode(null,null,'%',null);
    --returns all records because @user_id is null
    select * from test_user where user_id like decode(1,null,'%',1);
    --returns only user_id 1
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

Posting Permissions

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


Click Here to Expand Forum to Full Width