| Using dual table in oracle form [message #667786] |
Tue, 16 January 2018 01:56  |
 |
NDKA739125
Messages: 33
Registered: January 2018
|
Member |
|
|
Good day, my friends,
I'm trying to use the dual table in oracle forms, but when I execute the query, an error appeared: FRM-40505: ORACLE error: unable to perform query.
What should I do next  ?
Here is my code.
DECLARE
N1 VARCHAR(10);
N2 VARCHAR(10);
MY_QUERY VARCHAR(100);
BEGIN
N1 := ''||''' '''||'';
N2 := ''||''''''||'';
MY_QUERY := 'REPLACE(SUBSTR('''||:MY_LAYOUT.DATA_INPUT||''',1,3)),'||N1||','||N2||') FROM DUAL;
GO_BLOCK('MY_BLOCK');
SET_BLOCK_PROPERTY('MY_BLOCK',DEFAULT_WHERE, MY_QUERY);
EXECUTE_QUERY;
END; |
|
|
|
|
|
|
|
| Re: Using dual table in oracle form [message #667789 is a reply to message #667788] |
Tue, 16 January 2018 02:29  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
As I said: display how MY_QUERY looks like. You use it as a DEFAULT WHERE for a block. I don't know what :MY_LAYOUT.DATA_INPUT contains. It is possible that DUAL is used in a WHERE clause, such as
select ...
from ...
where id in (select 1 from dual)
It seems that you should remove FROM DUAL completely from MY_QUERY; why did you use it, anyway?
And - once again - make sure MY_QUERY is correctly set. There are many single quotes, you have to pay attention so that they match. Otherwise, it won't compile or won't work properly. That's why I said that you should display its value. You should create it step-by-step, adding parts of it one by one - first compose the SUBSTR, then apply REPLACE to it. Test frequently!
|
|
|
|
| Re: Using dual table in oracle form [message #667791 is a reply to message #667789] |
Tue, 16 January 2018 03:17 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That code you originally posted can't possibly give that error because that code can't possibly compile.
You're missing a quote at the end of the line that assigns my_query. Consequently the following go_block, set_block_property, execute_query and END are all part of the string.
If you put in the missing quote then my_query gets assigned to:
REPLACE(SUBSTR('BLAH',1,3)),' ','') FROM DUAL;
which is just non-sense.
What are you actually trying to do to the where clause?
|
|
|
|
| Re: Using dual table in oracle form [message #667793 is a reply to message #667791] |
Tue, 16 January 2018 04:19 |
|
NDKA739125
Messages: 33
Registered: January 2018
|
Member |
|
|
@cookiemonster
@Littlefoot
All I am trying to do is, taking a string from the input, so my fisrt query be like:
SELECT (REPLACE(SUBSTR(:MY_LAYOUT.MY_ORi_STRING),1,3),' ','') FROM DUAL.After that, I will get a string form this query (I called it, RES VARCHAR(100)).
Then, I would like to execute a where-clause query, based on the result that I have got from the 1st query. So the 2nd query be like:
SELECT TNAME FROM EMP WHERE TNAME LIKE 'RES'.
That's all I'm trying to do. Is that possible in Form 6i ? If possible please guide me.
Thanks.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Using dual table in oracle form [message #667803 is a reply to message #667802] |
Wed, 17 January 2018 03:00 |
|
NDKA739125
Messages: 33
Registered: January 2018
|
Member |
|
|
@Littlefoot
Sr, my bad . The query should be:
SELECT TNAME FROM EMP WHERE TNAME LIKE '%'||REPLACE(SUBSTR(:MY_LAYOUT.MY_ORI_STRING,1,3),' ','')||'%'.
But the form still can not perform this query .
*Update. It works, it finally works, thank you all, really appreciate it  .
[Updated on: Wed, 17 January 2018 03:14] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Using dual table in oracle form [message #667809 is a reply to message #667807] |
Wed, 17 January 2018 19:10 |
|
NDKA739125
Messages: 33
Registered: January 2018
|
Member |
|
|
@Littlefoot,
Sorry for my late reply. I've just realized that the SUBSTR should be in the first place. So my final query be like:
SELECT TNAME
FROM EMP
WHERE TNAME LIKE '%'||SUBSTR(REPLACE(:MY_LAYOUT.MY_ORI_STRING,' ',''),1,3)||'%'.
[EDITED by LF: removed [quote] tags and applied [code] ones]
[Updated on: Thu, 18 January 2018 00:15] by Moderator Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
