| username from dual doesn't evaluate against table [message #1513] |
Tue, 07 May 2002 13:07  |
Roy
Messages: 30
Registered: May 2002
|
Member |
|
|
Hi, I created a simple script to demonstrate my problem below. Does anyone know why the select that creates this view doesn't ever find a match even though the types seem to match in the Where clause? I would very much appreciate any help, as I'm sure it is something easy that my newbie eyes aren't seeing.
Thanks in advance,
-Roy
create table User_Rights
(Username varchar2(15),
OrgCode varchar2(5),
CONSTRAINT UserRights_pk PRIMARY KEY(Username, OrgCode));
insert into user_rights values('JSchmoe','51310');
create or replace view vw_Auth_Emp_Orgcodes as
SELECT User_Rights.orgcode
FROM User_Rights, (SELECT user Username FROM dual) User_Info
WHERE User_Rights.Username = User_Info.Username;
|
|
|
|
| Re: username from dual doesn't evaluate against table [message #1514 is a reply to message #1513] |
Tue, 07 May 2002 13:20  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
It is not finding a match because USER comes back in uppercase and your username was inserted in mixed case. So, either insert your usernames in uppercase or change the query to upper(username).
Also, your view should just be:
create or replace view vw_Auth_Emp_Orgcodes as
SELECT orgcode
FROM User_Rights
WHERE Username = user;
There is no need to select that value from dual - just refer to it directly.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
