SQL & PL/SQL :: Showing Monthly Data
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
ADVERTISEMENT
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
Jul 12, 2012
i have table structure (emp_no, emp_sal,emp_startwork)
i want query to show the monthly salary such as jan month salary Feb month salary
View 2 Replies
View Related
May 9, 2012
I want to generate the gapless montly running balance for the whole year.Data in the table "A" is as below.
code Date amount remarks
0101 30-SEP-10 1000 Opening
0101 01-OCT-10 1500
0102 25-OCT-10 500
0102 10-DEC-10 750
0101 26-JAN-11 450
0102 03-MAR-11 3000
0101 30-SEP-11 6000
Required output is
Code Date amount Balance
0101 31-OCT-10 1500 2500
0101 30-NOV-10 0 2500
0101 31-DEC-10 0 2500
0101 31-JAN-11 450 2950
-- -- -- continue
0101 30-SEP-11 6000 8950
Same output for code 0102. I have taken only 02 code for excample only. There are about 250 code in table.
View 14 Replies
View Related
Aug 3, 2010
I want to create procedure who should run for both montly as well as yearly report for monthly report i will pass Year and Month as argument for year i will pass Year only
for annual report i a counting all the user, new entry for item, new enty for customer, new enty for admin for montly report i am counting new item, new group.
View 16 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
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
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
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
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
Jan 24, 2012
I have a field " Tran_date with data type Date . This field contains date as well as time both . while I run this query :
select to_char(tran_date,'DD-MON-YYYY HH24:MI:SS') from ABC
WHERE lOC='1' ;
It is showing result :
21-JAN-2012 00:00:00
Why it is showing 00:00:00 although there is time like 08:25:12 Where is the problem ? Why I can'y see time . I need to change anything ,anywhere ?
View 9 Replies
View Related
Sep 15, 2011
I am generating an XML file from plsql using utl_file.There is a problem in number field.The value in the table is 0.18 and it is showing up in xml as .18. The leading zero is not showing up.
COLUMN_NAME is number (15);
<TAG>' || A.COLUMN_NAME || '</TAG>
I have tried using to_char(), but it is still showing up as .18 in XML. I want it as 0.18 in XML as well.
View 1 Replies
View Related
Sep 15, 2010
I want a sql query to transform my output..Also number of rows are not limited
Current Output
Col_A
1
2
3
4
5
6
7
8
9
10
however i need data as
COL_A COL_BCOL_C COL_D
1 2 3 4
5 6 7 8
View 6 Replies
View Related
Nov 23, 2012
When i am compiling trigger with only "after update", trigger is running fast. But when i am running with "After insert or update or delete" event on trigger, trigger comes with compilation error.
SQL> drop trigger trg_stk_upd_pur;
Trigger dropped.
SQL> create or replace trigger trg_stk_upd_pur
2 after update on O_STOCK_EFFECTS
3 REFERENCING NEW AS new OLD AS old
4 FOR EACH ROW
5 begin
6 prc_stk_upd_pur(:old.seff_comp_suid, :old.seff_area_suid, :old.seff_tran_suid);
7 end;
8 /
[code].....
Warning: Trigger created with compilation errors.
SQL> sho err
Errors for TRIGGER TRG_STK_UPD_PUR:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/23 PLS-00049: bad bind variable 'OLD.SEFF_COMP_SUID'
3/44 PLS-00049: bad bind variable 'OLD.SEFF_AREA_SUID'
3/65 PLS-00049: bad bind variable 'OLD.SEFF_TRAN_SUID'
[code].....
View 2 Replies
View Related
Oct 1, 2012
My OTN account handle shows the number but till yesterday it was showing the Alias Name "ranit B".
Handle: 912545
Email:
Status Level: Newbie (20)
Registered: Feb 6, 2012
[code]...
View 13 Replies
View Related
Feb 21, 2013
i have a column
NUMBER(13,4)
when i insert 234.000
in show only 234
where as when i insert 234.12
i shows 234.12
is there any setting by which i can show 234.0000
View 13 Replies
View Related
May 24, 2012
I have one issue My server is in france and it is in french timezone but when I query for sysdate it returns US time.
In '/etc/sysconfig/clock/'
Zone= europe/paris
UTC= true
echo $TZ variable is returning nothing.
sysdate = us time
systimestamp= us time
current_timestamp = french time
current_date = french time
dbtimezone= europe/warsove, sessiontimezone=+2.00( which is also europe timezone offset)
tz_offset(dbtimezone)=+2.00, tz_offset(sessiontimezone)= +2.00 i.e europe
os timezone= europe/paris.
This command "./emctl config agent getTZ" is also returning timezone as europe/paris
Also in "emd.properties" file "agentTZRegion" parameter is set to europe/paris
Oracle version= 11.2.0
Now I don't understand why this sysdate and systimestamp is returning "US time zone" while everything else is returning french time zone.
View 9 Replies
View Related
Jun 27, 2011
I'm trying to get a list of values from a table, and I am just not seeing the answer -I have a table with 4 records (say A, B, C, and D), each record has a GUID to uniquely identify it.I have a 2nd table that holds the relationships (parent-child).
At this point, I have 2 rows:
A>B
A>C
So, A is a parent to B, and A is a parent to C (D is not related to anything at this point)
I would like to be able to go into the D record and see that it is NOT related to A (A would be the parent in this instance) - the trick however is I only want 1 instance of A to show up.attempted to use some inner/outer join qualifications and I just cannot seem to get the data to display properly.
View 1 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
Jan 28, 2013
I'm running Oracle EE 11.2.0.2 and when I look in OEM SQL Monitoring, it shows nearly every sql statement running with a degree of parallelism of *2*.
I've checked dba_tables and the 'degree' for all tables is only 1. I look at the actual sql statement, and there are no hints to tell it to use parallelism. So why and how is the database using parallelism?
I do see that parallel_threads_per_cpu is set to 2, but this is default for our Solaris 10 operating system.
REF: (for 11.2)
===========
PARALLEL_THREADS_PER_CPU specifies the default degree of parallelism for the instance and determines the parallel adaptive and load balancing algorithms. The parameter describes the number of parallel execution processes or threads that a CPU can handle during parallel execution.
The default is platform-dependent and is adequate in most cases. You should decrease the value of this parameter if the machine appears to be overloaded when a representative parallel query is executed. You should increase the value if the system is I/O bound.
how to tell if my database is actually IO bound or not?
View 6 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
Aug 16, 2013
I created a user and granted connect, resource to the user. but trying to select columns from the view USER_HISTORY$,it's showing no value, i tried to login the user several times, but it's displaying no value, any particular reason or any minor mistake?
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
Nov 4, 2010
we just have create one database server, after installation we change tns port 1521 to 1700, after that we have facing problem in em, so we reconfigure emca and now everything is working fine except one thing, in EM before changing the port it shows LISTENER DETAILS at first page.
But now it doesn't show listener details. see the attach picture. what is the resolution for that problem, how can i get this information on my EM PAGE.
View 1 Replies
View Related
May 22, 2013
I have a button with an dynamic action , with sql:
declare
errors varchar2(100);
begin
apex_application.g_print_success_message := 'test message';
errors:=package.function_some_process_returning_varchar100_with_errors_or_sucess;
HTP.p ('<script language="JavaScript"> alert('''||errors||'''); </script>');
end;
The first "test message" is not shown.and after the function (it is executed fine) i get the next error:
Error:Parsererror - SysntaxError: Unexpected token <
i i only leve the first line (g_print_sucess) nothing occurs clicking the button.
I only want to show on a popup the result from the function, but g_print_sucess seems not to work for some reason.
View 1 Replies
View Related
Sep 14, 2012
we have a routine which refreshes matrerilized view every day
dbms_mview.refresh('MVIEW_NAME>,'C') refresh ok but in user_ mviews always has status needing compile afterwarsd when compile isfine.
using 11.2.0.3
View 2 Replies
View Related
May 23, 2012
TABLE 1 : COSTS
I have a table of Costs. We have Jobs that run and there will be a cost associated with a particular machine.So JobNo 1 may run on Machine A and have a cost of 50 dollars. Although its now shown below JobNO 1 could run on MachineB and so on.
JOBNO MACHINE COST
1 MachineA 50
2 MachineA 70
3 MachineA 100
TABLE 2: OPERATIONS
We have operators (PERSONCODE) run the jobs on the machines. So Job 1 may be run by PERSONCODE 8 (e.g. Tony) and it may run on MachineA or MachineB.Multipe people may run a particular job. The PERSONCODE will be unique to the Job and it is actually unique to the list. A person never works on more than one job.
JOBNO MACHINE PERSONCODE
1 MachineA8
1 MachineA7
1 MachineB6
2 MachineA3
2 MachineA2
2 MachineA1
3 MachineA4
DESIRED RESULT:
I need to assign the COSTs to the OPERATIONS table but only want it to show next to one person (next to the appropriate machine).
DESIRED RESULT:
JOBNO MACHINE PERSONCODE COST
1 MachineA8 50
1 MachineA7
1 MachineB6
2 MachineA3 70
2 MachineA2
2 MachineA1
3 MachineA4 100
[code]....
View 5 Replies
View Related
Aug 20, 2012
i have just create a clone database. it work very well.but when i open the dbca to delete this database.it not show in dbca prompt. why ?
View 4 Replies
View Related