Home » Developer & Programmer » Forms » Record group problem (Oracle 10g,Dev 10g)
Record group problem [message #428992] Sat, 31 October 2009 12:15 Go to next message
maxis
Messages: 17
Registered: September 2008
Junior Member
I try to make sql statement, from two form text item for record group that only generate where clause, it execute
good but results in no datafound

statment for record group
sql_S varchar2(500):=(case when :SEARCH_BLK.searchcol is not null and :SEARCH_BLK.search is not null then
       >>>> 'SELECT DISTINCT ID AS ID1,ID ,DESCRIPTION,STATUS  FROM TBLACCOUNT '
|| ' where ' ||:SEARCH_BLK.searchcol||'like '
||'''%'||:SEARCH_BLK.search||'%'''
||'ORDER BY ID1'
   else
        'SELECT DISTINCT ID AS ID1,ID ,DESCRIPTION,STATUS  FROM  TBLACCOUNT ORDER BY ID1'
   end );


table contain values

SQL> select id,description,status from tblaccount where id like '%001%';

ID
---------------
DESCRIPTION
--------------------------------------------------------------------------------
STATUS
--------------------------------------------------------------------------------
001
Assets
Group

00101
Fixed Assets
Group

00102
Current Assets
Group

0010101
Free Hold Assets
Group

0010101001
Land and building
Detail

0010102
Leased Hold Assets
Group

0010102001
Machinery
Group

0010201
Current Assets
Group

0010201001
Stocks
Detail

0010201002
Debitors
Detail

0010201003
Current Portion of lease
Detail

0010201004
Prepaid Expenses
Detail

0010201005
Cash
Detail

0030101001
Cummulative Debentures
Detail

0030201001
Creditors
Detail

0040201001
Income From Sale Of Land
Detail

0050201001
Lose From Sale Of Land
Detail


17 rows selected.

SQL>


Please correct me where i am wrong
[EDITED by DJM: split overly long lines - it won't format]

[Updated on: Wed, 18 November 2009 23:19] by Moderator

Report message to a moderator

Re: Record group problem [message #429043 is a reply to message #428992] Sun, 01 November 2009 10:52 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Display result of the SQL_S variable (using the MESSAGE built-in or put it into an item so that you could see how it looks like). Then execute it in SQL*Plus - you'll probably see what's wrong about it.

Report message to a moderator

Re: Record group problem [message #429045 is a reply to message #428992] Sun, 01 November 2009 11:27 Go to previous messageGo to next message
maxis
Messages: 17
Registered: September 2008
Junior Member
ohhh.. i it would be strange. Result from text item and put into sqlplus.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT DISTINCT ID AS ID1,ID ,DESCRIPTION,STATUS  FROM  TBLACCOUNT  where TBLACCOUNT.ID like '%
001%' ORDER BY ID1;

ID1             ID
--------------- ---------------
DESCRIPTION
--------------------------------------------------------------------------------
STATUS
--------------------------------------------------------------------------------
001             001
Assets
Group

00101           00101
Fixed Assets
Group

ID1             ID
--------------- ---------------
DESCRIPTION
--------------------------------------------------------------------------------
STATUS
--------------------------------------------------------------------------------

0010101         0010101
Free Hold Assets
Group

0010101001      0010101001
Land and building

ID1             ID
--------------- ---------------
DESCRIPTION
--------------------------------------------------------------------------------
STATUS
--------------------------------------------------------------------------------
Detail

0010102         0010102
Leased Hold Assets
Group

0010102001      0010102001

ID1             ID

Report message to a moderator

Re: Record group problem [message #429049 is a reply to message #429045] Sun, 01 November 2009 15:48 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does it mean that the same SELECT works fine in SQL*Plus but returns nothing in Forms?
Did you connect to the same schema?

Report message to a moderator

Re: Record group problem [message #429176 is a reply to message #428992] Mon, 02 November 2009 07:46 Go to previous messageGo to next message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you sure you are copying and pasting the queries correctly?
Because your very first query appears to be missing a space before the like.

Report message to a moderator

icon14.gif  Re: Record group problem [message #429208 is a reply to message #428992] Mon, 02 November 2009 09:40 Go to previous messageGo to next message
maxis
Messages: 17
Registered: September 2008
Junior Member
Sorry for late replay

sir the problem was that area but what it is i dont know
--
-- go and clear block  for new values
--
GO_BLOCK('TBLACCOUNT_LOOK');
 
 IF :TBLACCOUNT_LOOK.ID IS NOT NULL THEN
   clear_block('TBLACCOUNT_LOOK');
>>>> i replace with clear_block; and works fine
   clear_message;
 END IF;

Thanx for assistance

Report message to a moderator

Re: Record group problem [message #429319 is a reply to message #429208] Tue, 03 November 2009 03:53 Go to previous message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
CLEAR_BLOCK doesn't accept that kind of an argument (i.e. block name) - only commit mode.

I didn't quite get the point: are you saying that invalid CLEAR_BLOCK caused SELECT statement to return nothing? Is the problem solved now?

Report message to a moderator

Previous Topic: how to add calendar option to a text item in forms 6i
Next Topic: Oracle Form print directly to printer
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 09:57:49 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.