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

Transfer Data To SQL Server 2008 / Arabic Data Showing

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

SQL & PL/SQL :: Write Query To Show Monthly Salary?

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

SQL & PL/SQL :: Monthly Running Total With Opening Balance

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

SQL & PL/SQL :: Create Procedure Run For Both Monthly As Well As Yearly Report

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

Reports & Discoverer :: View Not Showing All Data?

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

Forms :: Multiline Data / Showing In One Text Field

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

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 View Related

Client Tools :: DBMS-PROFILER Not Showing Any Data?

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

Reports & Discoverer :: Showing The Number Sequence Of Displayed Data

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

Forms :: Data Entering Form Not Showing Any ERROR Messages / SAVE RECORD Message

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

SQL & PL/SQL :: Time Not Showing

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

SQL & PL/SQL :: Showing Leading Zeroes In XML?

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

SQL & PL/SQL :: Showing Rows As A Matrix

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

SQL & PL/SQL :: Trigger Is Showing Bad Variable?

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

Reg - Handle Not Showing Alias Name?

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

PL/SQL :: Number Not Showing Zero After Decimal

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

Sysdate Showing Wrong Timezone?

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

SQL View Showing Non-related Records?

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

Forms :: Icons Not Showing On Buttons?

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

Why Is OEM SQL Monitoring Showing Parallel On Almost Every Statement

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

Forms :: Display Item Not Showing Result?

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

Forms :: Showing Message When Canvas Changing?

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

Security :: Select Columns From View - Showing No Value?

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

Forms :: Vertical Scroll Bar Is Not Showing Properly?

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

Enterprise Manager :: Not Showing Listener Detail

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

Application Express :: Showing A Message From A Button?

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

Mview Always Showing Status Needing Compile?

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

SQL & PL/SQL :: Linking Table Challenge - Showing A Value Only Once Not For Each Match?

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

Backup & Recovery :: Clone Database Name Not Showing In Dbca

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







Copyrights 2005-15 www.BigResource.com, All rights reserved