| How to Query an LOV Form with UNION query [message #85417] |
Wed, 23 June 2004 22:51 |
M Ravindra
Messages: 3
Registered: June 2004
|
Junior Member |
|
|
The Scenario is....
There is an LOV form in which.. emp_id and emp name(PEMP_EMP_ID,PEMP_NAME) are populted from the block (table) PT_GL_POL_EMPLOYEE
So originally in the Form, in block PT_GL_POL_EMPLOYEE, block properties in the WHERE Clause.. it is set like this
WHERE Clause : PEMP_POLGM_SYS_ID IN (SELECT POLGM_SYS_ID FROM PT_GM_POLICY WHERE POLGM_NO = :PARAMETER.M_PARA_1)
M_PARA_1 is the policy number passed from calling form
As per the requirement I changed the query like 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
When I query(F9 - F8) on this, it's working fine...
The problem is with, if I query with Emp Id, i.e F7 and emp id = 100 and F8..
giving an error
FRM-40505: ORACLE error. unable to perform query
Dispaly Error.. then
Error is
ORA-00904: invalid column name
SQL Statement in error:
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 = :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 = :2) AND :3 BETWEEN PEMPH_O_ENTRY_DT AND PEMPH_O_EXIT_DT and (PEMP_EMP_ID=:4)
The table that is used (PH_GM_POL_EMPLOYEE) in second query of the UNION is not a data block in this form
the problem is how do I get my condition given at run time..PEMP_EMP_ID=100, that is shown above by the form as (PEMP_EMP_ID=:4)..works on both the queries of the UNION
Any possible solution/Fix....
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
