| How to handle (supress) ROWID in Forms [message #85352] |
Thu, 17 June 2004 22:03  |
Ravindra
Messages: 11
Registered: January 2002
|
Junior Member |
|
|
The Scenario is...
In a Form (LOV Form that is called from another Form using CALLFORM), For a particular Data
Block(TABLE 1)....
In the Block properties ..under Database section, I have given the 'WHERE Clause' like this..
column IN (Select.....subquery....)
UNION ALL
select ROWID,C,D from TABLE2
where column IN (Select.....subquery....)
and <....some expression...>
Forms will replace this internally like this
<<< select ROWID,A,B from TABLE 1 where >>>
column IN (Select.....subquery....)
UNION ALL
select ROWID,C,D from TABLE 2
where column IN (Select.....subquery....)
and <....some expression...>
TABLE 1(Transaction Table) is a Data Block in a Form whereas TABLE 2(History Table) is not a Data Block in this Form.
In the query after UNION ALL if I dont give ROWID, then Form at runtime throws the following error
Unable to perform query.. ERROR Displayed is ...
ORA-01789: query block has incorrect number of result columns
Now the Problem is..............
The above query in normal case gives/populates say some 500 records..
with ROWID in the query, it is duplicating on Columns A and C.. and giving/populating some 15000
records.. which is time consuming..
Is there any way to supress the ROWID in the forms..
Forms adds ROWID by default...
What is the Fix or other possible Solutions for the above problem..
|
|
|
|
|
|
| Re: How to handle (supress) ROWID in Forms [message #85357 is a reply to message #85354] |
Fri, 18 June 2004 00:03  |
Ravindra
Messages: 11
Registered: January 2002
|
Junior Member |
|
|
Hi
Thanks for u r reply, my problem rectified.
But we r facing problem with query . we r using union in our query but it is displaying duplicate records
Only those records that are common in both the tables Table 1 (transaction) and Table 2 (History) are duplicated.. such common records are displayed twice
Now actually as per my query I should get 351 records now I’m getting 650..
I don’t understand why Form populating duplicate records.. for the query which is fetching unique records in Toad
|
|
|
|
|
|
| Re: How to handle (supress) ROWID in Forms [message #85372 is a reply to message #85360] |
Fri, 18 June 2004 23:17 |
M Ravindra
Messages: 3
Registered: June 2004
|
Junior Member |
|
|
My query is this
EXISTS (SELECT 1 FROM PT_GM_POLICY WHERE POLGM_SYS_ID = PT_GM_POL_EMPLOYEE.PEMP_POLGM_SYS_ID AND POLGM_NO = :PARAMETER.M_PARA_1)
UNION
SELECT PEMPH_O_EMP_ID PEMP_EMP_ID, PEMPH_O_NAME PEMP_NAME
FROM PH_GM_POL_EMPLOYEE Hist
WHERE EXISTS (SELECT 1 FROM PT_GM_POLICY WHERE POLGM_SYS_ID = Hist.PEMPH_POLGM_SYS_ID AND POLGM_NO =
:PARAMETER.M_PARA_1) AND :PARAMETER.M_PARA_2 BETWEEN PEMPH_O_ENTRY_DT AND PEMPH_O_EXIT_DT
In toad I'll run it like this
SELECT PEMP_EMP_ID, PEMP_NAME
FROM PT_GM_POL_EMPLOYEE
WHERE EXISTS (SELECT 1 FROM PT_GM_POLICY WHERE POLGM_SYS_ID = PT_GM_POL_EMPLOYEE.PEMP_POLGM_SYS_ID AND POLGM_NO = '01/951/2003/5')
UNION
SELECT PEMPH_O_EMP_ID PEMP_EMP_ID, PEMPH_O_NAME PEMP_NAME
FROM PH_GM_POL_EMPLOYEE Hist
WHERE EXISTS (SELECT 1 FROM PT_GM_POLICY WHERE POLGM_SYS_ID = Hist.PEMPH_POLGM_SYS_ID AND POLGM_NO =
'01/951/2003/5') AND '24-APR-03' BETWEEN PEMPH_O_ENTRY_DT AND PEMPH_O_EXIT_DT
Data displayed in Toad is like this...
PEMP_EMP_ID,PEMP_NAME
1,MUNA HASSAN ALI SALEH
10,K SAMUEL
100,JULIE THOMPSON
101,NICOLENE VAN DER MERWE
102,SAMEEA WICOMB
103,FROOZANDEH AFSHAR
104,MEDHAT MUSA HAMED ABUKHATER
105,HELEN CULL
106,FREDRIKA STOFFELINA SAUER
107,LORAINE ALLISON GALLACHER
108,AHMAD TOUFIC CHAKER
109,BASSAM MOHAMMAD ABED BASHTAWI
11,JAINULABUDIN SHANAVAS KHAN
110,MUSTAFA MAHDI NAJI
The form is dispalying the data fo0r same query, like this..
1,MUNA HASSAN ALI SALEH <--
10,K SAMUEL
10,K SAMUEL
100,JULIE THOMPSON
101,NICOLENE VAN DER MERWE
101,NICOLENE VAN DER MERWE
102,SAMEEA WICOMB
102,SAMEEA WICOMB
103,FROOZANDEH AFSHAR
103,FROOZANDEH AFSHAR
104,MEDHAT MUSA HAMED ABUKHATER <---
105,HELEN CULL
105,HELEN CULL
From above data what I observed is
Form is duplicating those records that are there in both the tables (PT_GM_POL_EMPLOYEE (Transaction) & PH_GM_POL_EMPLOYEE(History) )
The record with Emp Id - 1,104 is there only in History table.. and it is not duplicated
what is the problem..
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
