Home » Other » Client Tools » user from dual doesn't evaluate against table column
user from dual doesn't evaluate against table column [message #38707] Tue, 07 May 2002 13:22 Go to next message
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 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: user from dual doesn't evaluate against table column [message #38708 is a reply to message #38707] Tue, 07 May 2002 13:51 Go to previous message
Roy
Messages: 30
Registered: May 2002
Member
Todd Barry replied in another forum. Thanks Todd....

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_RightsWHERE Username = user;
There is no need to select that value from dual - just refer to it directly.
Previous Topic: Lookup Parent Value and Generate a String
Next Topic: Inserting a row into different table than supposed
Goto Forum:
  


Current Time: Thu Mar 28 14:32:40 CDT 2024