Home » SQL & PL/SQL » SQL & PL/SQL » issue with quotes
issue with quotes [message #256072] Thu, 02 August 2007 21:52 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member

declare
l_Whr_Clause varchar2(31000);
n_Org_type number:=1;
n_Org_SName varchar2(20):='INTFI73';
l_SqlStmt       VARCHAR2(32767);

 n_User_Ident     ECUS_USER.user_ident%TYPE;

 n_Last_Name           ECUS_User.Last_Name%TYPE;
    n_First_Name          ECUS_User.First_Name%TYPE;
begin

  l_Whr_Clause := ' where user_org_type ='||n_Org_type||' and  Org_Short_Name = UPPER('||(n_Org_SName)||')';
       
       

l_SqlStmt:='SELECT count(1) FROM AuditedUsers ';

dbms_output.put_line(l_Whr_Clause);

l_Whr_Clause := l_Whr_Clause||' AND User_Ident LIKE '||(n_User_Ident);

l_SqlStmt:=l_SqlStmt||l_Whr_Clause;

dbms_output.put_line('final query: '||l_SqlStmt);

 l_Whr_Clause := l_Whr_Clause||' AND USER_LAST_NAME  LIKE '||quoted_string(upper(n_Last_Name));
 l_Whr_Clause := l_Whr_Clause||' AND USER_FIRST_NAME  LIKE '||quoted_string(upper(n_First_Name));

 l_SqlStmt := 'SELECT User_ID, user_last_name||'',''||user_first_name user_name,org_short_name,user_org_type,user_ident FROM 

AuditedUsers';
    

    l_SqlStmt:=l_SqlStmt||l_Whr_Clause;

     dbms_output.put_line('second query: '||l_SqlStmt);
end;




its getting

where user_org_type =1 and  Org_Short_Name = UPPER(INTFI73)
final query: SELECT count(1) FROM AuditedUsers  where user_org_type =1 and
Org_Short_Name = UPPER(INTFI73) AND User_Ident LIKE
second query: SELECT User_ID, user_last_name||','||user_first_name
user_name,org_short_name,user_org_type,user_ident FROM
AuditedUsers where
user_org_type =1 and  Org_Short_Name = UPPER(INTFI73) AND User_Ident LIKE  AND
USER_LAST_NAME  LIKE '' AND USER_FIRST_NAME  LIKE ''




i want it as upper('INTFI73') can anyone help me with this?
Re: issue with quotes [message #256073 is a reply to message #256072] Thu, 02 August 2007 21:54 Go to previous message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
got it!!!!!!!!!



l_Whr_Clause := ' where user_org_type ='||n_Org_type||' and Org_Short_Name = UPPER('''||(n_Org_SName)||''')';
Previous Topic: View: duplicate record
Next Topic: SQL STATEMENT FOR CALCULATION
Goto Forum:
  


Current Time: Fri Dec 09 17:21:43 CST 2016

Total time taken to generate the page: 0.25022 seconds