Home » SQL & PL/SQL » SQL & PL/SQL » how to use the & symbol in text (merged)
how to use the & symbol in text (merged) [message #327402] Mon, 16 June 2008 05:25 Go to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Frines,
in this query where i should use the escape character to use the & symbol.

select * from XX_Exit_reasons
where acc_name='F&F Closed - Payable'

Thanks
Mano
Re: how to use the & symbol in text [message #327406 is a reply to message #327402] Mon, 16 June 2008 05:34 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL> set define off
SQL> select * from dept
  2  where dname = 'F&F Closed - Payable';

no rows selected

SQL>
Re: how to use the & symbol in text [message #327407 is a reply to message #327402] Mon, 16 June 2008 05:41 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
try to use \ to escape
eg: \&
Re: how to use the & symbol in text [message #327408 is a reply to message #327402] Mon, 16 June 2008 05:42 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Littlefoot,
want to use in view.
Re: how to use the & symbol in text [message #327410 is a reply to message #327408] Mon, 16 June 2008 05:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The answer is still to do an "set define off" before doing whatever you are doing.

Try that, and if it doesn't work post exactly what you are trying to do as a formatted SQL*Plus session, like Littlefoot did.
Re: how to use the & symbol in text [message #327411 is a reply to message #327402] Mon, 16 June 2008 05:50 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Something like this?
SQL> set define on
SQL> select object_id from user_objects
  2  where object_name = 'F&F Closed - Payable';
Enter value for f: 
old   2: where object_name = 'F&F Closed - Payable'
new   2: where object_name = 'F Closed - Payable'

no rows selected

SQL> set define off
SQL> create or replace view v1 as
  2    select object_id from user_objects
  3    where object_name = 'F&F Closed - Payable';

View created.

SQL> set define on
SQL> select * from v1;

no rows selected

SQL> 
Re: how to use the & symbol in text [message #327412 is a reply to message #327402] Mon, 16 June 2008 05:53 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
i want to create view by using this query
select max(PAC.creation_date) 
from per_analysis_criteria PAC
    ,per_person_analyses PPA
	,fnd_id_flex_structures FIFS
where
    PPA.person_id=700589
and FIFS.id_flex_structure_code='XX_EXIT_INDIA_CASE_STATUS'
and FIFS.id_flex_num=PPA.id_flex_num
and PPA.id_flex_num=PAC.id_flex_num
and PPA.analysis_criteria_id=PAC.analysis_criteria_id
and PAC.segment1 like 'F&F Closed - Payable'


How to achieve this?

not getting solution by \& also


Re: how to use the & symbol in text [message #327415 is a reply to message #327412] Mon, 16 June 2008 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
not getting solution by \& also

And did you follow and try the other solution?

Regards
Michel
Re: how to use the & symbol in text [message #327416 is a reply to message #327402] Mon, 16 June 2008 06:03 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> i want to create view by using this query
Maybe it would be nice to mention the tool you are using (SQL*Plus, TOAD, SQL Developer, ... ).

> not getting solution by \& also
And the result is?
Does it ask for substitution variable?
Is the view invalid?
Do queries on this view return wrong result?
Re: how to use the & symbol in text [message #327436 is a reply to message #327416] Mon, 16 June 2008 07:07 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

17:34:09 SQL> [B]set define off[/B]
17:34:13 SQL> create or replace view v2 as select * from dept
17:34:17   2  where dname = 'F&F Closed - Payable';

View created.

Elapsed: 00:00:00.00
17:34:18 SQL> select * from v2;

no rows selected

Elapsed: 00:00:00.00
17:34:24 SQL> [B]set define on[/B]
17:34:35 SQL> select * from v2;

no rows selected

Elapsed: 00:00:00.00
17:34:38 SQL> select * from dept
17:34:59   2  where dname = 'F&F Closed - Payable';
Enter value for f: 
old   2: where dname = 'F&F Closed - Payable'
new   2: where dname = 'F Closed - Payable'

no rows selected

Elapsed: 00:00:00.00
17:35:03 SQL> select * from v2;

no rows selected

Elapsed: 00:00:00.00
17:35:14 SQL> 



Hope this would help you clear your concepts..
Query [message #328192 is a reply to message #327402] Thu, 19 June 2008 05:49 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Friends

OHRID Status New_Amount Old _Amount

303027055 F&F Closed - Payable 100
303027055 5000

How can we get

OHRID Status New_Amount Old _Amount

303027055 F&F Closed - Payable 100 5000

Thanks
Re: Query [message #328193 is a reply to message #328192] Thu, 19 June 2008 05:50 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Friends

OHRID         Status               New_Amount      Old _Amount

303027055     F&F Closed - Payable 100

303027055                                          5000

How can we get

OHRID         Status               New_Amount      Old _Amount

303027055     F&F Closed - Payable 100             5000

Thanks
Re: Query [message #328195 is a reply to message #328192] Thu, 19 June 2008 05:51 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
frinds

god solution.. sorry.
Re: Query [message #328197 is a reply to message #328192] Thu, 19 June 2008 05:59 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
hadn't you posted the same problem some 3 days ago?

http://www.orafaq.com/forum/?t=rview&goto=327402#msg_327402
Re: how to use the & symbol in text [message #328200 is a reply to message #327412] Thu, 19 June 2008 06:12 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
If you are still searching for a solution, then you can replace

and PAC.segment1 like 'F&F Closed - Payable'

with
and PAC.segment1 like 'F'||'&'||'F Closed - Payable'

Regards,

MSMallya
Re: how to use the & symbol in text (merged) [message #328472 is a reply to message #327402] Fri, 20 June 2008 05:21 Go to previous message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
hi msmallya,
Good solution. thanks
Previous Topic: Dynamic SQL in REF Cursor
Next Topic: Multiple accounts
Goto Forum:
  


Current Time: Mon Dec 05 10:56:54 CST 2016

Total time taken to generate the page: 0.16256 seconds