Forms :: How To Base A Block On Left Join
Feb 5, 2012
I am trying to develop an application of cars. The car have marques example :
TOYOTA,HUNDAI,CHEVROLET
each mark have families example: TOYOTA have Hilux, yaris corola, CHEVROLET have opra,...etc. Each family have a lot of models example: hilux have h2kn-clim,.. etc. And finally there are some options witch are generally in all cars example Radio-k7,air-conditioner ... etc.
option 1..n-----------------1..n model the relation call(opt_mod)
i did develop the block of marques (master) and the block of families (detail) in a form 1. i did develop the bock of models(master) in form 2 and the is no problem. but i want to add to form 2 the block of (opt_mod) but the user did tell me that he want to to see all options with check boxes .
As a solution of this problem i want to build a block on LEFT JOIN between table :option and table :opt_mod
View 4 Replies
ADVERTISEMENT
Oct 15, 2012
How do I change the status of a block to change?
View 7 Replies
View Related
Apr 9, 2013
I am creating a query where I am trying to take phone call lengths and put them into buckets of length ranges 0:00 - 0:59, 1:00 - 1:59 etc. Even if there are no calls in the call table I need to return the range with a zero (hence the left join and nvl). When I do this the left join acts like an equal join, I suspect there is some reason left joins only work if there is an equal condition in the join (instead of >= and < that I use, or similarly I could use BETWEEN). I also have a question about performance (below).
The create table script for the lookup is like this:
CREATE TABLE DURATION_RANGES
(
RANGE_TEXT varchar2(20),
RANGE_LBOUND decimal(22),
RANGE_UBOUND decimal(22)
)
Sample inserts are:
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0,59);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('01:00 - 01:59',60,119);
etc.
The query is:
select
r.range_text as duration_range,
nvl(count(*),0) as calls,
nvl(SUM(call_duration),0) as total_duration
from
[code]...
As I say, it is not returning all ranges in the duration_ranges table, so acting like an inner join. I realize one solution would be to populate duration ranges with every value possible (instead of ranges) so join is an equal join, but that would make the duration_range table larger.
My questions:
1. Is it possible to get the left join to work with the duration range values as they currently are?
2. Even if 1 is possible, would it be better performance to have exact values (but a larger lookup table) and do an equals join instead of >=, < or BETWEEN? Performance now is not bad.
What I mean is (with only one time value and not lbound and ubound:
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',1);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',2);
View 4 Replies
View Related
Nov 29, 2011
Using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
Here's a simplified version of the query I am having problems with:
SELECT
assoc.association_id
FROM mentor_initiative mi
LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1
LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND
NVL(assoc_prog.program_id, -1) = NVL(assoc.program_id, -1)
Note that there is no program with program id -1. So the assoc_prog left join will come up with nothing. I was thinking that since assoc_prog.program_id will be null, the second assoc left join would pick the row where assoc.program_id is null. However, the second left join doesn't join to any row.
In this query, it does join to an assoc row (I changed assoc_prog.program_id to NULL)
SELECT
assoc.association_id
FROM mentor_initiative mi
LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1
LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND NVL(NULL, -1) = NVL(assoc.program_id, -1)
I was thinking it would join to an assoc row in the first query though. How can I change the first query to have the desired effect of left joining to a row where assoc.program_id is null if assoc_prog.program_id is null?
View 7 Replies
View Related
Oct 13, 2010
I have two tables employee_master and employee_time. There could be situation that record from employee_master does not exist in employee_time.
create table employee_master(employee_id number, employee_name varchar2(100));
insert into employee_master values(1, 'employee 1');
insert into employee_master values(2, 'employee 2');
insert into employee_master values (3, 'employee 3');
create table employee_time(employee_id number, project_id number, project_type varchar2(20), time_date date, hours number)
insert into employee_time values(1, 100, 'Billable', '01-Oct-2010', 10);
insert into employee_time values(1, 200, 'Billable', '02-Oct-2010', 9);
insert into employee_time values(1, 210, 'Non Billable', '03-Oct-2010', 10);
insert into employee_time values(2, 100, 'Billable', '01-Oct-2010', 10);
insert into employee_time values(2, 200, 'Billable', '02-Oct-2010', 9);
The requirement is to show all the employees from employee_master and with total billable hours and non billable hours, if not exist, show zero.The output will be:
Employee_ID Employee_Name Total_Billable_Hours Total_Non_Billable
1 Employee1 19 10
2 Employee2 19 0
3 Employee3 0 0
The question is to write a Left outer join query or to write a PL/SQL function which can return total rows if Employee_ID is supplied to it as a parameter
Query 1:
Select Employee_ID, Employee_name, sum(Billable), sum(Non_Billable)
From
(
Select a.Employee_ID, a.employee_name,
decode(b.project_type, 'Billable', hours, 0) as Billable,
decode(b.project_type, 'Non Billable', Hours, 0) as Non_Billable
from employee_master a
left outer join employee_time b on a.Employee_ID=b.Employee_ID
)
Group by Employee_ID, Employee_Name
Query 2:
Select Employee_ID, Employee_Name, func_billable(Employee_ID) as Billable, func_non_billable(Employee_ID) as Non_Billable
From Employee_Master
Which query is good from the performance perspective? In real situation the employee_time is a very huge table.
View 2 Replies
View Related
Jan 20, 2011
joining this query instead of using the left join. Reason is want to show the score column in a different place and also do not want to show the second IPS column that is used in the joined query.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
select * from
(
select i.ips,
p.project_name,
p.project_segment,p.location,p.project_exec_model,
p.project_exec_model||' - '||p.project_config pmodel,
one.score schedule,two.score cost,three.score execution,four.score commercial,
nvl(one.score,0)+nvl(two.score,0)+nvl(three.score,0)+nvl(four.score,0) as total,
[code]....
View 6 Replies
View Related
Sep 21, 2012
I have the following query but it is taking too much time because of the LEFT OUTER JOIN on HST table which is a huge table , is there an alternative to LEFT OUTER JOIN that can be used to optimize the code:
SELECT HST.COMP_CODE,
HST.BRANCH_CODE,
HST.CURRENCY_CODE,
HST.GL_CODE,
HST.CIF_SUB_NO,
HST.SL_NO,
SUM(CV_AMOUNT) CV_AMOUNT,
[code].....
View 10 Replies
View Related
Jun 17, 2010
I have the following 2 SQLs ; one return 1 row, another one is no row returned.
select v.value
from v$parameter v
where v.name = 'cpu_count';
return value "1"
select o.value
from v$osstat o
where o.stat_name = 'NUM_CPU_CORES';
No row returned.
combine the above two in to 1 SQL, and return 1 , null or 1, 1. I assume we can get it with left join for the condition "o.stat_name (+) = 'NUM_CPU_CORES'" , but no row returned for the following SQL. How could we get the result for 1, null for this case?
select v.value, o.value -- or NVL(o.value, 1)
from v$parameter v, v$osstat o
where v.name = 'cpu_count'
and o.stat_name (+) = 'NUM_CPU_CORES';
no row returned
View 5 Replies
View Related
Jul 27, 2012
What is the fundamental difference between MINUS keyword and LEFT outer join in Oracle.
I can achieve same results using either one of them.
View 8 Replies
View Related
Jul 20, 2011
We just upgraded to 11g and have run into incorrect results for some of our LEFT JOINs. If the table, view, subquery, or WITH clause that is being LEFT JOINed to contains any constants, the results are not correct.
For example, a test (nonsensical) view such as the following is created:
create or replace view fyvtst1 as
select spriden_pidm as fyvtst1_pidm,
'Sch' as fyvtst1_test
from spriden
where spriden_last_name like 'Sch%' ;
When I run the following query, I get correct results; that is, only those with "Sch" starting their last name are listed.
select spriden_pidm, spriden_last_name, fyvtst1_pidm, fyvtst1_test
from spriden
join fyvtst1 on fyvtst1_pidm = spriden_pidm ;
However, when I change the JOIN to a LEFT JOIN, the last column contains "Sch" for all rows, instead of NULL:
select spriden_pidm, spriden_last_name, fyvtst1_pidm, fyvtst1_test
from spriden
left join fyvtst1 on fyvtst1_pidm = spriden_pidm ;
We've discovered other quirky things related to this. A WITH clause with similar logic as the above view, when LEFT JOINed to a table will also cause the constant to appear in each row, instead of NULL (and only the value where there is a join). But when additional columns are added to the WITH, it behaves correctly.
This is easy enough to rewrite - but we have WITHs and views containing constants in numerous places, and cannot hope to track down every single one successfully before the incorrect results are used.
Finally, the NO_QUERY_TRANSFORMATION hint will force the query to work correctly. Unfortunately, it has a huge negative performance impact (one query ran for an hour, vs. 1 second in 10g).
View 10 Replies
View Related
Jun 13, 2012
i want to update record that is fetched based on join condition on form
1. made a block manually :::: EMPSAL
2. Query DATA SOURCE NAME :::: EMP a, Sal b
3. Where Clause :::: a.empid = b.empid
4. DML DATA Target Type :::: Table
5. DML DATA Target Name :::: EMP a, Sal b
6. All Columns are marked a.empid, a.empname, b.sal, b.date etc
It does not allow me to update record.
View 1 Replies
View Related
Oct 16, 2012
I am using left outer join to fetch PRSN_KEY .I need to find null values in B.PRSN_KEY. I am using below query but its giving me 0 count.
select count(*) from (
Select A.PRSN_KEY AS AKEY,B.PRSN_KEY AS BKEY from CD03955P.H_CM_EEST_EEOR A LEFT JOIN CD03955P.H_CM_EEST_EEOR B
ON
A.PRSN_KEY =B.PRSN_KEY
where
A.CAT_ID=111
AND
A.DATA_SOURCE='PEN_CO'
AND
B.CAT_ID = 1 and B.DATA_SOURCE ='PEN_EEST'
AND B.CAT_CD IN ('ACTIVE','LOA','LOAWP','LOAMLP','LOAMLN')
AND B.EFBEGDT < A.EFBEGDT
)
where BKEY IS NULL
View 8 Replies
View Related
May 27, 2013
I have already done auto increment by making sequence and trigger. but now the problem is when i am trying to retrieve data from that table it returns all data perfectly but the primary key that is my auto increment value shows blank.I am facing this problem with join query, there 4 table left joined in my query. But when I remove join from my query then it shows that value.
But i need that value in my join query.So, what is the problem and what can I do?And other thing is when I apply this query in Oracle SQL Developer, it works perfect.
My Query:
return $this->db->query("select * from TBL_EMPLOYEE_BASIC left join TBL_EMPLOYEE_DETAILS on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_DETAILS.EMPL_ID left join TBL_EMPLOYEE_EDUCATION on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EDUCATION.EMPL_ID left join TBL_EMPLOYEE_EXPERIENCE on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EXPERIENCE.EMPL_ID where
[code]...
View 2 Replies
View Related
Oct 16, 2012
What I am trying to do is get my report to list every room in the table even if there is nothing scheduled in the room for the selected date. I add a command to the report to force the left outer join but I keep running into errors. This is how I have it worded:
SELECT
"ROOM"."ROOM_ID",
"PATIENT_CARE_EVENT"."OR_NUM"
FROM
"ROOM"."ROOM" LEFT OUTER JOIN "PATIENT_CARE_EVENT"."PATIENT_CARE_EVENT"
ON "PATIENT_CARE_EVENT"."OR_NUM"="ROOM"."ROOM_ID" AND "PATIENT_CARE_EVENT"."PROCEDURE_DATE_DT" IN {?Start Date} TO {?End Date}
Someone else suggested that I change the IN/TO wording in the last line to BETWEEN/AND. When I do that it gives me an error stating that the table or view does not exist.
View 18 Replies
View Related
Aug 14, 2009
i want to know the difference between Left outer join Vs. Right outer join? Its like which join is safer to use or is there any recommendations to use any join?
View 6 Replies
View Related
Mar 22, 2012
these are the sample data :
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);
CREATE TABLE department (
courses CourseList)
NESTED TABLE courses STORE AS courses_tab;
INSERT INTO department (courses)VALUES (CourseList('1','2','3'));
[code]....
The query returns the correct data, CourseList that are not subset of any other CourseList of the table.
I am trying to convert this not exists in a left outer join query to check if the performance is better, but I don't know how to do it.
I was making some variations of this code :
select d1.courses c_1, d2.courses c_2
from department d1,department d2
where d1.courses<>d2.courses(+);
but it is now working.
View 3 Replies
View Related
Jul 30, 2012
I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).
i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).
Oracle version information
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]...
I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".
I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.
View 10 Replies
View Related
May 4, 2010
can I add a session (php code) to base.htm file?
if yes, what i have to do?
View 1 Replies
View Related
Feb 29, 2012
how to change base html page for Oracle Forms. I use Weblogic Server. Base html page - I mean this common for all forms as a background html page with "Oracle Fusion Middleware" text. I was looking something about this and I found that it is theoretically possible, but all traces reached me to the <Oracle_Home_path>forms/java directory which have only jars, with .class files.
View 1 Replies
View Related
Apr 1, 2011
I am using forms 6i and oracle 10g. i want to display a char from database. that means a database string 'Bangladesh' is display from by one char to one char.
and display view right to left first display "B" then "BA" then 'BAN' etc.
View 2 Replies
View Related
Feb 16, 2013
Is there any possibility of creating a selection interface similar to our report or forms wizard where user can select single fields or multiple fields together and when he clicks on the arrow those fields data to be inserted into another table.Please refer attachment.
--this is the main table
create table batch_item (batch_no varchar2(12),item_batch varchar2(12),total_batch_qty number);
insert into batch_item ('0001','a',300);
insert into batch_item ('0002','b',200);
insert into batch_item ('0003','a',102);
--after inserting the data i should be able to move this data to corresponding batches by manually selection and pushing them to a batch.create table ct_item (item varchar2(12),item_nm varchar2(20),item_qty number);
insert into om_item values ('a','alpha',2);
insert into om_item values ('b','beta',3);
insert into om_item values ('c','gama',4);
--left side is om_item and right will have batch items where batch qty will be accumulated upon choose arrows.
View 3 Replies
View Related
Nov 19, 2010
i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field
e.g
item11 item21 item31 chkbox1
item12 item22 item32 chkbox2
Scenario like this :
My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field
when i checked chkbox1 , my cursor goes to item31...not item32
View 12 Replies
View Related
May 5, 2012
i have a master detail form, In Master block we have one field cheque amount and in Detail block we have field receiveable amount invoice wise. if company paid us a cheque amount we will enter this amount in Master block field Cheque amount and in detail block there will be invoice wise receivable amounts. i want to distribute the cheque amount in detail block invoice wise for example
Cheque amount in master block = 291
Invoice wise receiveable amount is as follows
Invoice No , receivable amount , Received amount
10, 196 , 0
20 , 95 , 0
30 , 54 , 0
Result should be as follows:
Invoice No , receivable amount , Received amount
10, 196, 196
20 , 95 , 95
30 , 54, 0
Received amount field should be distributed according to the receivable amount when recevied amount = cheque amount then remaining will be 0.
View 2 Replies
View Related
Jul 24, 2010
I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.
I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".
View 3 Replies
View Related
Mar 2, 2010
I created Main Menu form and in this form I attached popup menu in picture's property problem is that when i right click on this picture then show the popup menu .I want when I click left click then it should show the popup menu.
View 2 Replies
View Related
May 20, 2010
I want to load XML into base table using PL/SQL procedure.For that I have wrote procedure but that does not work well .
View 4 Replies
View Related
Sep 30, 2010
select code,inv_date,inv_co_code,inv_co_name,inv_fnd_code,inv_fnd_name,inv_amount,inv_nofu
from retreport order by inv_co_code, inv_fnd_Code,inv_date
STAT INV_DATE CODE NV_CO_CODE INV_CO_NAME INV_FND_CODE INV_FND_NAME INV_AMOUNT INV_NOFU
----- ------ -------- ----------- ------------ -------------- ---------------------- ----------------------
I 28/06/2010 117 13 CAD 1 MCB 15104708.75 148375.7308
I 01/07/2010 13 CAD 1 MCB 2804.7464
I 30/08/2010 117 13 CAD 1 MCB 700000.00 6882.2380
I 30/08/2010 117 13 CAD 1 MCB 9500000.00 93401.8018
R 31/08/2010 117 13 CAD 1 MCB 39315.8646
R 08/09/2010 117 13 CAD 1 MCB 24515.9089
6 rows selected
STAT I = IN STOCK
STAT R = OUT STOCK
I need FIFO Query First in Fist out (INV_NOFU)
STAT CODE INV_CO_CODE INV_CO_NAME INV_FND_CODE INV_FND_NAME INV_AMOUNT INV_NOFU BALACNE
----- ------ ----------- ----------- ------------ -------------- ---------------------- ---------------------- ------------
I 117 13 CAD 1 MCB 15104708.75 148375.7308 148375.7308
R 117 13 CAD 1 MCB 39315.8646 109059.8662
R 117 13 CAD 1 MCB 24515.9089 84543.8773
I 13 CAD 1 MCB 2804.7464 87348.6237
I 117 13 CAD 1 MCB 700000.00 6882.2380 94230.8617
I 117 13 CAD 1 MCB 9500000.00 93401.8018 187632.6635
View 2 Replies
View Related
Feb 14, 2012
I'd like to join tables in a cursor.
I tried coding this way...
CURSOR studgrade_cur IS
SELECT g.stud_id, g.grade, subj_code, s.description
FROM studgrades g JOIN subjects s
ON(g.subj_code = s.subj_code)
WHERE stud_id = :Studentprofile.student_id;
but i got an error, saying:
Quote:encountered the symbol JOIN when expecting one of the following:
,; for group having intersect minus order start union where
connect
Is it not allowed to use JOIN statement in a cursor?
View 4 Replies
View Related
Sep 16, 2010
I want to create Non-Equi relation between EMP and Salgrade Table .I am writing following Query Under Relation Tab, Its giving me error message .
emp.sal between salgrade.losal and salgrade.hisal
View 3 Replies
View Related
Nov 30, 2012
I am creating a form with 3 blocks.
Control block,
Header Block,
Detail Block.
First i have entered a record in the control block.
Then i navigate to header block,entered the first record for Header Block.
Then i navigate to Detail block,entered the first record for detail Block.
then i again navigate to Header Block..
Now the Problem comes....As i am Trying to enter second record for header block,the pop up comes as "Do you want to save the changes".
Condition 1:i dont want that message to appear for every header record i enter.
Condition 2:For every header record i enter,Corresponding detail record would have been entered.i want that detail records to be retained if i am clicking that header record as i have already created relationship for both header block and detail block.
View 1 Replies
View Related
