-
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
-
Is user_id a primary key?
-
kEN,
it's not PK.
thanks,
-
Is it indexed? What's it's datatype?
-
kEN,
SO SORRY , IT'S PRIMARY KEY AND VARCHAR
-
Does it actually contain alpha-numeric values?
-
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.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|