| Outer Joins in Forms. Possibility ? [message #481987] |
Mon, 08 November 2010 21:45  |
PrasanthVishnu
Messages: 14
Registered: September 2006
|
Junior Member |
|
|
Hi,
I am having two forms and two blocks.
these are master child records relationship.
In data , I have master but which does have all columns except one new column which was there in master, recently added to child.
Now..is that possible to use full outer join or left outer join kind of stuff in forms..
I heards its not possible..
as i am able to do that sql plus effortlessly..
I tried to change the property of 'relation' which default created by forms between these two blocks.
Its only allowing to add = (equi join) but not allowing use nvl or join function..there..
can any one share some ideas..
Prasanth
|
|
|
|
|
|
| Re: Outer Joins in Forms. Possibility ? [message #482090 is a reply to message #482010] |
Tue, 09 November 2010 08:05  |
PrasanthVishnu
Messages: 14
Registered: September 2006
|
Junior Member |
|
|
Hi Littlefoot,
First really sorry..I did'nt put whole scenario here..
Thanks for your time..
Sorry for long post..but just trying to explain scenario.
Here I am explaining the actual issue as below:
Here I will try to explain with a scenario from database level... and then to form level.
Table1:
select *
from table1
where control = '19950927'
and po = '739409'
and factor = 'SPP'
/
So here if you look below same control + PO having multiple amend values*
CONTROL PO AMEND FACTOR ID
------------------- ----------------- --------------- ---------------- ------------
19950927 739409 00 SPP 002
19950927 739409 01 SPP 002
19950927 739409 02 SPP 002
19950927 739409 03 SPP 002
19950927 739409 04 SPP 002
19950927 739409 05 SPP 002
19950927 739409 06 SPP 002
another example:
CONTROL PO AMEND FACTOR ID
------------------- ----------------- --------------- ---------------- ------------
19950928 739419 07 SPP 002
19950928 739419 08 SPP 002
19950928 739419 09 SPP 002
19950928 739419 10 SPP 002
19950928 739419 11 SPP 002
19950928 739419 12 SPP 002
19950928 739419 13 SPP 002
Before update in Table 2(Payments):
See below amend is NULL in table 2 for older records
select *
from table2
where control = '19950927'
and po = '739409'
and factor = 'SPP'
/
CONTROL PO FACTOR PAYMENTDATE TYPE AMOUNT TS_LAST_UPDATE AMEND
--------- ------ ------ ------- -------------- ------ -------- --------------
19950927 739409 SPP 01-JAN-96 T 88225 19-JUN-98
19950927 739409 SPP 01-JAN-96 T 99593 19-JUN-98
19950927 739409 SPP 01-JAN-97 T 7801 19-JUN-98
19950927 739409 SPP 01-JAN-97 T 29895 19-JUN-98
19950927 739409 SPP 01-JAN-97 T 33065 19-JUN-98
19950927 739409 SPP 01-JAN-97 T 63822 19-JUN-98
select *
from table2
where control = '19950928'
and po = '739419'
and factor = 'SPP'
/
CONTROL PO FACTOR PAYMENTDATE TYPE AMOUNT TS_LAST_UPDATE AMEND
--------- ------ ------ ------- -------------- ------ -------- --------------
19950928 739419 SPP 01-JAN-96 T 84544 19-JUN-98
19950928 739419 SPP 01-JAN-96 T 91783 19-JUN-98
19950928 739419 SPP 01-JAN-97 T 7801 19-JUN-98
19950928 739419 SPP 01-JAN-97 T 2947 19-JUN-98
19950928 739419 SPP 01-JAN-97 T 3345 19-JUN-98
19950928 739419 SPP 01-JAN-97 T 6782 19-JUN-98
So seeing above we understand that, we cannot really pick which amount is for which Amend since there is no DATA for amend
is available
for future records we didnt really bother as we added the column newly making it not null all future will have specific
amount
for each amend..that is not the issue here
Update Statement:
so we are issuing below update Table2 to set minimum of amend from Table1 will be updated for coresponding records
in Table2 so Now we will able able to build a mandatory relationship between these two. Mandatory here means..
Table1.control=Table2.control
Table1.PO=Table2.PO
Table1.factor=Table2.factor
Table1.amend=Table2.amend *
Update Table2 set po=(select min(amend)from Table1
where Table2.control=Table1.control
AND
Table2.PO=Table1.PO
AND
Table2.factor=Table1.factor)
WHERE
Table2.control= '19950927'
AND
Table2.PO = '739409'
AND Table2.factor = 'SPP'
/
Update Table2 set po=(select min(amend)from Table1
where Table2.control=Table1.control
AND
Table2.PO=Table1.PO
AND
Table2.factor=Table1.factor)
WHERE
Table2.control= '19950928'
AND
Table2.PO = '739419'
AND Table2.factor = 'SPP'
After Update(Table2-Payments):
select *
from Table2
where control = '19950927'
and PO ='739409'
and factor = 'SPP'
/
CONTROL PO FACTOR PAYMENTDATE TYPE AMOUNT TS_LAST_UPDATE AMEND
----------- ------ ------- ----------- ------ -------- ------------------ --------------
19950927 739409 SPP 01-JAN-96 T 88225 19-JUN-98 00
19950927 739409 SPP 01-JAN-96 T 99593 19-JUN-98 00
19950927 739409 SPP 01-JAN-97 T 7801 19-JUN-98 00
19950927 739409 SPP 01-JAN-97 T 29895 19-JUN-98 00
19950927 739409 SPP 01-JAN-97 T 33065 19-JUN-98 00
19950927 739409 SPP 01-JAN-97 T 63822 19-JUN-98 00
select *
from Table2
where control = '19950928'
and PO ='739419'
and factor = 'SPP'
/
CONTROL PO FACTOR PAYMENTDATE TYPE AMOUNT TS_LAST_UPDATE AMEND
--------- ------ ------ ------- -------------- ------ -------- --------------
19950928 739419 SPP 01-JAN-96 T 84544 19-JUN-98 07
19950928 739419 SPP 01-JAN-96 T 91783 19-JUN-98 07
19950928 739419 SPP 01-JAN-97 T 7801 19-JUN-98 07
19950928 739419 SPP 01-JAN-97 T 2947 19-JUN-98 07
19950928 739419 SPP 01-JAN-97 T 3345 19-JUN-98 07
19950928 739419 SPP 01-JAN-97 T 6782 19-JUN-98 07
Existing code is like follows:
current code in form A push button:
DECLARE
pl_id ParamList;
pl_name VARCHAR2(15) := 'ls_parm_list';
BEGIN
if :TableA.Control > ' ' and
:TableA.po > ' ' and
:TableA.amend > ' ' and
:TableA.factor > ' ' then
pl_id := Get_Parameter_List(pl_name);
if NOT Id_Null(pl_id) then
Destroy_Parameter_List(pl_id);
end if;
pl_id := Create_Parameter_List(pl_name);
Add_Parameter(pl_id,'A1Col1',TEXT_PARAMETER,:TableA.control);
Add_Parameter(pl_id,'A1Col2',TEXT_PARAMETER,:TableA.po);
Add_Parameter(pl_id,'A1Col3',TEXT_PARAMETER,:TableA.amend);
Add_Parameter(pl_id,'A1Col4',TEXT_PARAMETER,:TableA.factor);
Add_Parameter(pl_id,'A1Col5',TEXT_PARAMETER,:PARAMETER.UID);
NEW_FORM('FormB',TO_SAVEPOINT,NO_QUERY_ONLY,pl_id);
end if;
END;
Code for New form instance in FormB:
DECLARE
W_WHERE VARCHAR2(100);
BEGIN
if :PARAMETER.control > ' ' and
:PARAMETER.po > ' ' and
:PARAMETER.AMEND > ' ' and
:PARAMETER.FACTOR > ' ' then
W_WHERE := 'B1col1=''' || :PARAMETER.control
|| ''' AND B1col2=''' || :PARAMETER.po
|| ''' AND B1col3 =''' || :PARAMETER.amend
|| ''' AND B1col4=''' || :PARAMETER.factor || '''';
Set_Block_Property('TableB',DEFAULT_WHERE,W_WHERE);
execute_query;
Set_Block_Property('TableB',DEFAULT_WHERE,'');
end if;
:SYSTEM.MESSAGE_LEVEL := 25;
END;
Interpretation of above output:
1. see for second example CONTROL 19950928 updated with '07' as it is minimum amend number in Table 1 for this combination
of Control(19950928)+PO(739419)
2. Same way see for second example CONTROL 19950928 updated with '07' as it is minimum amend number in Table 1 for this
combination
of Control(19950928)+PO(739419)
NOW COMING TO ACTUAL FORMS:
1. User passes Control + PO + Amend + Factor from Form1 to form2(which is based on Table1)
In above example... if user passes combination of 19950927 (control) + 739409 (its PO)+ SPP (factor) + 00 (amend, this
is the value after update in Table2..i.e the minimum amend
Now Payment block in Form 2 display all records as all payment records mapped to amend '00'
2. another example if user passes combination of 19950928 (control) + 739419 (its PO)+ SPP (factor) + 07 (amend, this
is the value after update in Table2..i.e the minimum amend
Till this pefectly fine...
NOW LIMITATION IS:
User can have choice of selecting any amend from Table1 as it is there in Table1 already...
Now example..
In above example... if user passes
combination of 19950927 (control) + 739409 (its PO)+ SPP (factor)+ 02 (which is not minimum
for this control and not in table2 after update)
NOW IT IS DISPLAYING BLANK RECORDS IN PAYMENT SECTION IN FORM 2 as there is mapping is failed
Passed parameter is:
19950927(control) + 739409 (its PO)+ SPP (factor)+ 02 (amend) (match failed)
OR
19950928(control) + 739419 (its PO)+ SPP (factor)+ 09 (amend) (match failed)
But It should be
19950927(control) + 739409 (its PO)+ SPP (factor)+ 00 (amend)
(match passed as we updated all payment rec with amend as minimum amend 00
In this case it will fecth all the payment records.
OR
19950928(control) + 739419 (its PO)+ SPP (factor)+ 07 (amend)
(match passed as we updated all payment rec with amend as minimum amend 77
In this case it will fecth all the payment records.
NOW WHAT CUSTOMER WANTS
So what I need to do is : even if customer passes the below combination..
19950927(control) + 739409 (its PO)+ SPP (factor)+ 02 (amend) from Form 1
or
19950928(control) + 739419 (its PO)+ SPP (factor)+ 09 (amend)
IT SHOULD GO TO FORM 2 as 19950927(control) + 739409 (its PO)+ SPP (factor)+ 00 (amend)-- since table2 has 00(amend) for
all records after update
19950928(control) + 739419 (its PO)+ SPP (factor)+ 07 (amend)-- since table2 has 07(amend) for all records
SO THAT IT WILL FOUND DATA MATCH AND PULL UP ALL RECORDS OF PAYMENTS AS IT PASSED THE AMEND WHICH IS ACTUALLY IN
TABLE2
Customer is agreed that for older records, if he can see all payments amounts irrespective of amend which he passes to
form2
But User does not really know which is minimum amend for this combination..BUT As programmer, I need to do programatically
what ever amend he selects on form1 it should be passed as value which is in table2.. i mean 00 and 07 in above cases
even if user passes other than minimum amend, payment section in Form2 should pull all records which are with amend value 00 in above example
If i remove condition of table1.amend=table2.amend then it wont work for future records
If i put condition of table1.amend=table2.amend then it wont work for old records then the problem is like above scenario.
So we convinced customer to update all amend in payments table to min value of amend in table1. Now problem form is only displayihg all the records if USER SUPPLIES ONLY THAT MIN amend but not other amend..
so just thinking of how to override default relationship built by forms.. not sure really hmm..so looking for some outerjoin concepts in forms..whether in pre-query can dynamically override the master child relationship..?
any ideas to deal with above problem..
I agree that existing datamodel is ugly..but customer insist to work on this with further re-design ...
Thanks in advance
Prasanth
[Updated on: Tue, 09 November 2010 08:20] Report message to a moderator |
|
|
|
| Re: Outer Joins in Forms. Possibility ? [message #482099 is a reply to message #482090] |
Tue, 09 November 2010 09:14 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) Is there a way of telling future records from old records? Amend is null?
2) What master detail relationship? Your examples talk about two seperate forms. Master-detail relationship in forms can only be implemented between two blocks in the same form.
3) Stop writing whole paragraphs in bold - makes me want to not read it.
4) Can this big long description be boiled down to the following:
Link table1 to table2 on control, PO and factor. Also link on amend if it's not null in table2 (new records), otherwise ignore it and just use the first three columns.
5) Quote:
but customer insist to work on this with further re-design
I assume that should have read without?
|
|
|
|
| Re: Outer Joins in Forms. Possibility ? [message #482100 is a reply to message #482099] |
Tue, 09 November 2010 09:54 |
PrasanthVishnu
Messages: 14
Registered: September 2006
|
Junior Member |
|
|
Hi there,
sorry..again..I will improve my posting skills for sure..
1) Is there a way of telling future records from old records? Amend is null?
yeah..since there is no scope for adding a one more column for flag kind of thing (no more ddl permitted) for separation of old and new. so in form we wrote some code for control < 2010999 and there ts_update_date in payment table <=31-12-2010 and count >0 in payment table for given control,po,amend. If all these conditions is true then its old record. If any of above condition fails then its new record.
2) What master detail relationship? Your examples talk about two seperate forms. Master-detail relationship in forms can only be implemented between two blocks in the same form.
Yeah, you are right. what to do existing design is like that. Hmm. Form 1 consits of Table1 alone and form2 consists of Table1 and Table 2 . Currently,Both are joined by forms master-child relationship (default by forms). But I have designed this form initally ( I would have done it better than this and more simple)..
3) Stop writing whole paragraphs in bold - makes me want to not read it.
sorry for this.. I thought of over emphasizing my problem..thats why too bold.. 
4)Can this big long description be boiled down to the following:
Link table1 to table2 on control, PO and factor. Also link on amend if it's not null in table2 (new records), otherwise ignore it and just use the first three columns.
-- Yes, Exactly
Yeah, But since people look for some samples and requirement I explained in such long post..
"Link table1 to table2 on control, PO and factor. Also link on amend if it's not null in table2 (new records), otherwise ignore it and just use the first three columns"
-- I am facing problem since blocks on these forms linked via deafult relation in form2. Any way to override ?
5)
but customer insist to work on this with further re-design
--Yes sorry, it type mistake..customer do not want to do any re-design..
|
|
|
|
| Re: Outer Joins in Forms. Possibility ? [message #482175 is a reply to message #482100] |
Wed, 10 November 2010 04:20 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
First off what you are asking for is not an outer-join.
If you have table2 outer-joined to table1 then it'll retrieve everything from table1 even when there are no matches in table2.
You basically want a join with an OR condition in it.
There is no way to do this in a master-detail relationship in forms.
Two approaches I can think of:
1) Do the master-detail relationship manually. Delete the relationship in the form. Change the default where clause on table2 to do the OR condition you need. If data can be inserted into table2 from the form add a pre-insert trigger to copy the relevant values from table1.
2) Fix the data so you can use a standard master-detail relationship. Update all the null values of amend in table2 to some unused value. Create new records in table1 to match the updated records in table2.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
