Forms :: Data Between Dates But Parameter Not Working And Showing All?
May 4, 2011
i am seting runtime where clause on Forms as follow:
set_block_property('FORMC', onetime_where,'where to_char(INVOICE_DATE,'||'DD-MON-YYYY'||') >='||'to_date('||:search.inv_frm||','||'DD-MON-YYYY'||')'||
' and to_char(INVOICE_DATE,'||'DD-MON-YYYY'||') <' || 'to_date('||:search.inv_to||','||'DD-MON-YYYY'||')');
:search.inv_from is from date
:search.inv_to is up to date
i wann data between this dates but date parameter is not working it showing all data...... where synatx get wrong..
View 11 Replies
ADVERTISEMENT
Mar 1, 2011
I have got data in a table like
ID Service
1 A
1 B
1 C
I have written a cursor which is fetching it like A,B,C and assigning it in a display field.
It is working fine.
I want to display it in single text field but in multiple lines.i have set the property multiline to yes in property insepector.
I want to display it like
A
B
C
but it should remain in single field/text box. Infact i am unable to break the line in cursor.
View 2 Replies
View Related
Sep 5, 2012
Is there an Oracle date function that ignores public bank holidays and calculates working days only?
View 4 Replies
View Related
Jan 15, 2013
I developed a form in forms9i, at the time of data entering form is not showing any ERROR messages or SAVE RECORD message and when I press exit button it is asking "DO YOU WANT TO SAVE THE CHANGES YOU HAVE MADE".
I have checked my PRIMARY KEY field and there is no mistake and value is populating at PRE INSERT.
View 2 Replies
View Related
Sep 22, 2011
Any SQL Query
1) Attendance and All type of Leave including Week rest.
2) Parameter that exclude some Dates.
View 2 Replies
View Related
Apr 27, 2012
I have a query in which i am using two parameter start date and end date. Both are of date type. And i want these parameter to treat like a column
FOR e.g.
stdate :1/1/2012
endate :30/3/2012
I want to display report like below:
January February march
View 25 Replies
View Related
Feb 17, 2010
I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.
View 7 Replies
View Related
May 28, 2010
I want to export forms data into Excel sheetfor that i am using Client_Ole2.I have attached Webutil object library and Pl/Sql libraryStill I can not export data from Form to Excel sheet
View 7 Replies
View Related
Jan 15, 2013
I am trying to transfer data from Oracle database table into another table resides in a SQL server 2008. A database link is already set, and data can be selected/queried using
select * from a_table@db_link
the thing is when trying to insert Arabic data from Oracle to a SQL server table
insert into a_table@db_link values(
'N''some data in Arabic'' ,
'11-oct-00',
'some data in English'
);
commit;
its inserted alright, but when we try to display the data it shows like this instead of displaying Arabic, the english data is alright, but also date show as garbage?!!
when trying to insert arabic data directly through SQL server its fine though.I suggested that we transfer data through ODBC to flat files like Acess and then to SQL server but the team rejected it since they're going to do it daily and the data is huge( we are talking more than 28000 records).
View 1 Replies
View Related
Mar 6, 2013
I am having the query
SELECT JOB,
decode(to_char(hiredate,'YY'),80,to_char(hiredate,'MON'),NULL) "80",
decode(to_char(hiredate,'YY'),81,to_char(hiredate,'MON'),NULL) "81",
decode(to_char(hiredate,'YY'),82,to_char(hiredate,'MON'),NULL) "82",
decode(to_char(hiredate,'YY'),83,to_char(hiredate,'MON'),NULL) "83",
count(*) total
FROM emp
WHERE JOB='CLERK'
GROUP BY JOB,
hiredate;
I want result as follows
job 80 81 82 83 TOTAL
CLERK DEC DEC 2
CLERK JAN JAN 2
That means if 80,81,82,83 if having some common month then it should show side by side.
View 20 Replies
View Related
Nov 1, 2011
We have a view that shows the all the data that we expect, so we created a report to show that data, the report worked, then all of a sudden the report stopped showing all the data. (we did have a server move)I checked the view and the all the data is there....
I have created a new report based on the view but it does the same thing, it doesn't bring all the data up (i get very old data), there are no conditions or anything.
other tables and views seem fine. i have recreated the view and i still have the same problem.
View 2 Replies
View Related
Nov 29, 2010
I am using Oracle 10G with Toad & want to use DBMS_PROFILER to identify which part of procedure is taking long time.
I created PLSQL_PROFILER_RUNS,PLSQL_PROFILER_UNITS,PLSQL_PROFILER_DATA tables. and I am running DBMS_PROFILER as below.
DECLARE
x INTEGER;
BEGIN
x := DBMS_PROFILER.start_profiler ('Test Profiler');
[code]...
The procedure run is successfully completed.I gave the following command to see the data,
SELECT runid, run_date, run_total_time, run_comment
FROM plsql_profiler_runs;
SELECT runid, unit_number, unit_type, unit_owner, unit_name, unit_timestamp,
total_time
FROM plsql_profiler_units
SELECT runid, unit_number, line#, total_occur, total_time, min_time, max_time
FROM plsql_profiler_data
No data is displayed. Then I created these tables in SYS and gave grant to public.Yet It's same. No data is coming into the tables.
View 5 Replies
View Related
Oct 21, 2010
I have query in that query i should display the number of row. I have tried to select the row num but when i use it the data will be duplicated. I have found something that related to use temporary table, or subquery but all of them will not work because the query depends on many tables and if i use temporary table filling the table and then reading from it will take too long time.
View 11 Replies
View Related
Sep 26, 2012
We are using ERP based on Oracle 10g forms, there is icons on the buttons those icons are displaying when we uses the ERP in Windows based client and browser Internet Explorer.But when we uses Opera browser we are getting ERP but icons are not displaying.
Same way when we uses Linux client machine using Mozilla Firefox there also the icons are not displaying.
View 3 Replies
View Related
Oct 8, 2010
am facing a Problem in creating display items .First I create a display item and name it ITEM6This Display item will show Department name of employees .I create a Block Level Trigger to invoke this display item .
WHEN-VALIDATE-ITEM
select dname into :ITEM6 FROM DEPT where :EMP.DEPTNO=DEPT.DEPTNO;
In Property Palette I edit following
Data Type : char
canvas :canvas4
Then I save ,compile and Run it successfully .But when i press Execute Query button . It shows me Following error message .
SELECT ROWID,ITEM6,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM scott.emp
ORA-00904: "ITEM6": invalid identifier
View 2 Replies
View Related
Jan 6, 2012
I am facing one confusion with canvas changing and message displaying issue i have one login page. in that there are three fields username,password and department in the department i having 5 departments including salesandmarketing
the requirement once i entered every credentials correctly and i am logging in as salesandmarketing. When the salesandmarketing canvas opens a message should display saying "you are successfully entered into sales and marketing".
i have my home page as content canvas and sales and marketing as tab canvasi need to do the same message when i am entering into 5 departments with corresponding department name in the message end instead of salesandmarketing
is it possible to do with when_new_canvas
View 1 Replies
View Related
Mar 1, 2010
When I first access the form and query for records, it works as per the expectation and with scroll bar to scroll up and down. But when I do "View>Record>Last", it reaches to last record, but not able to use the scroll bar to scroll the records up(I am on last record, so can't do downward scrolling).
The scroll bar is displaying in Full, no room to scroll up down.It happens only when I do "View>Record>Last".
View 2 Replies
View Related
Jul 31, 2010
We are building a custom form extension with our EBS R12 where against certain parameters we are inserting and updating records in a table through a PL/SQL procedure.This PL/SQL doesn't use a commit exclusively, instead the user must click the Save button after verifying the data (new records are inserted, existing ones are updated and after the insert and update routines, execute_query is fired to fetch the latest data)
Everything works fine, however, when the user clicks on the Save button form status bar says "FRM-40401: No changes to save"
We would like to alter this message with our own custom message stating how many records were inserted and hows many were updated (We have counters counting this information)
Under which particular trigger we should write the code to enhance this requirement?
View 2 Replies
View Related
Sep 14, 2011
Vertical scrollbar not showing all the records when I scroll down.
Using Oracle forms 10g , operating system windows.
I have two fields with number of items dispayed = 15.
I have a vertical scroll bar with them. There are 34 records in the table but the scrollbar only shows 15 records.
View 22 Replies
View Related
Jan 9, 2013
In my forms(version 9i), list item values showing with ALT + DOWN key combination but I want to change it to only DOWN key.I search this combination in FMRWEB.RES file but there isn't any entry of ALT + DOWN.
View 1 Replies
View Related
Dec 21, 2011
below is the table and data
create table bday (name varchar2(30),bdate varchar2(10));
insert into bday values('jeffery','0110');
insert into bday values('boss','1231');
insert into bday values('raj','1225');
[Code]...
the BDATE column is in the form "mmdd".
I just want to select the name between 2 dates ( not including years). lets say between sysdate and sysdate+20, i.e
select to_char(sysdate,'dd-mon'),to_char(sysdate+25,'dd-mon') from dual;
TO_CHAR(SYSDATE,'DD-MON') TO_CHAR(SYSDATE+25,'DD-MON')
21-dec 15-jan
but when I run the below query, it is not showing me 'january' data
select name from bday
where to_date(bdate,'mm/dd') between sysdate and sysdate+25;
o/p comes as:
NAME BDATE
boss 1231
raj 1225
but the actual o/p should be:
NAME BDATE
boss 1231
raj 1225
jeffery 0110
it seems to me that because of year change the rows are not displayed.how to handle this in single SQL
View 8 Replies
View Related
Sep 21, 2004
I am wondering how can I select data between two dates (ie from 08/12/2004 to today?)
Name DateS
Bob 08/11/2004
Tom 08/12/2004
Bill 09/21/2004 <-- today
return
Tom 08/12/2004
Bill 09/21/2004
View 4 Replies
View Related
Oct 8, 2012
How to print data between two dates... startdate and enddate inclusive
View 2 Replies
View Related
Nov 3, 2011
I have one table when I am querying like below
select * from timeoffreqitem
where timeoffreqitemid=134
getting data like below
134 144 07-OCT-11 13-OCT-11 134
I need to see this result as below.
134 144 07-OCT-11 13-OCT-11 134
134 144 08-OCT-11 13-OCT-11 134
134 144 09-OCT-11 13-OCT-11 134
134 144 10-OCT-11 13-OCT-11 134
134 144 11-OCT-11 13-OCT-11 134
134 144 12-OCT-11 13-OCT-11 134
134 144 13-OCT-11 13-OCT-11 134
I am looking at multilple optons.
View 1 Replies
View Related
Oct 8, 2012
I have an PL/SQL query which gives the data between the date interval submitted by the user.The problem is that i want all the date irrespective of it has data or not for eg: Let say date parameter is from 1-Jan-2012 to 5-Jan-2012
Now, in the database the available dates are:
1-Jan-2012
2-Jan-2012
5-Jan-2012
So as you can see here that dates 3-Jan-2012 and 4-Jan-2012 is not resulted out by the query. I want all the dates.
So the output should be like this:
1-Jan-2012
2-Jan-2012
3-Jan-2012
4-Jan-2012
5-Jan-2012
View 5 Replies
View Related
Oct 8, 2009
how can i write query to take data from DB, if im entering range of dates :
for example between 20-aug-2009 and 1-aug-2009.
But query must be like, if i enter 21-aug-2009 and second date should the start of the month.
for example if i enter second date like 2-jul-2009, first date should start form begining of that month?
View 1 Replies
View Related
Sep 5, 2012
I have an athletics participation table that only has the relevant emplid and effective date field. There is no term field on the table. I'm trying to only select those emplid's where the max( effdt) for the emplid is between the begin and end date of the current term. I only want to select current athletes. I would much rather it be for the current academic year but it seems impossible. Why can't I use max(effdt) here?
sql Code
AND h.emplid IN(SELECT b.emplid FROM PS_ATHL_PART_STAT b
where max(b.effdt) between (SELECT term_begin_date AND term_end_date
from PS_TERM_TBL
[Code].....
View 2 Replies
View Related
Apr 3, 2013
I am using Oracle 11g version
create table ORG(Name char(20),Datetime char(45),val1 number);
insert into ORGvalues('abc','10/29/2012 13:00','1.5')
insert into ORGvalues('abc','10/29/2012 13:05','1.5')
insert into ORGvalues('abc','10/29/2012 13:10','1.5')
insert into ORGvalues('abc','10/29/2012 13:15','1.5')
insert into ORGvalues('abc','10/29/2012 13:20','0.00')
insert into ORGvalues('abc','10/29/2012 13:25','0.00')
[code]....
while I am retrieving data between two dates. from table ORG. select from ORG where datetime between '29/03/2013' and '30/03/2013' order by datetime asc*
the output is coming like this,
O/P:
abc 29/03/2012 13:00 1.5
abc 29/03/2012 13:05 1.5
'abc 29/03/2012 13:10 1.5
'abc 30/03/2012 13:15 1.5'
'abc 30/03/2012 13:20 0.00
'abc 30/03/2012 13:25 0.00
[code]....
My problem in above out put is Here I am getting previous year data also at same day and month I want data only between the dates which are specified in query
But here needed output is
*'abc 29/03/2013 13:35 0.00*
*'abc 29/03/2013 13:40 2.1*
*'abc 30/03/2013 13:45 2.3*
*'abc 30/03/2013 13:50 2.1*
*'abc 31/03/2013 13:55 2.1*
*'abc 31/03/2013 14:00 2.2*
Note:my datetime datatype is char
My dear friends actually I am getting data like this date as a string(CHAR datatype) from third party tool.Due to this reason only I treat datetime attribute as string.
View 10 Replies
View Related
Jan 26, 2011
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
1220.01.10
1321.01.10
1423.01.10
1526.01.10
1627.01.10
1729.01.10
1831.01.10
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
View 2 Replies
View Related
Mar 10, 2011
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel
(
start_date,
end_date
)
AS
SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------
Result :
X1 : -----------[--]--------------------
X2 : -----------------------[--------]--
Sample 2:
Travel Date : -[--------------------------------]--
BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------
Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--
How can I do it using PL SQL ?
View 5 Replies
View Related
