PL/SQL :: Checking Correct Data Format Using Sql Query?
Aug 30, 2013
1) I got column date of joining which accepts date in below format DD-MON-YYYYDD-MON-YYMON-DD-YYYYMON-DD-YYMonth DD,YYYY Question:-
how do i check whether all dates in Date of joining column are in above format or not using sql query? 2) I got one more date column which accepts date in below formatMMDDYYYYYYYYMMDDMM/DD/YYYYMM/DD/YYYYYY/DD/MM Question:-
how do i check whether all dates in date column are in above format or not using sql query?
View 26 Replies
ADVERTISEMENT
Jan 30, 2011
CREATE OR REPLACE VIEW V_CATALOGUER_REPORT
(COUNT, EVENT_USER, TO_DATE, SHORT_NAME)
AS
SELECT COUNT (DISTINCT A.PART_REF),A.EVENT_USER, TO_DATE(A.EVENT_DATE, 'DD-MON-YY'),B.SHORT_NAME FROM J_SUPPLY_CHAIN_HIST A, J_SOURCE B
[code]...
i created one view,i link two tables ,i can see the correct data in this view,but the result from front end application is not correct.i cant see the dates are correct.
View 1 Replies
View Related
May 15, 2012
I have made below query :
select job,case when deptno=10 then ename ELSE 'null' end e10
,case when deptno=20 then ename ELSE 'null' end e20
,case when deptno=30 then ename ELSE 'null' end e30
from emp
order by 1;
the output is:
JOB E10E20E30
ANALYSTnullSCOTTnull
ANALYSTnullFORDnull
CLERKnullSMITHnull
[code]...
but I want data in one row, i mean for example in case of JOB as 'CLERK' the output should be : i.e.
JOB E1 E2 E3
CLERK MILLER ADAMS JAMES
CLERK SMITH
the complete correct output which I want is:
JOB E1 E2 E3
ANALYST SCOTT
ANALYST FORD
CLERK MILLER ADAMS JAMES
[code]....
View 3 Replies
View Related
May 29, 2012
CREATE TABLE DATA1
(
ID NUMBER(6),
DAT_ID NUMBER(6),
RNK NUMBER(2)
);
Insert into DATA1
(ID, DAT_ID, RNK)
[code]....
now after having this data from "data1" table , we need to get the row from "data2" table.in "data2" table there are total 6 combination on basis of "POS,ORDER" [ there are only 2 "ORDER" i.e. 'F' and 'S' , where as POS value can be changed, BUT THE "POS,ORDER" COMBINATION WILL HAVE ONLY 6 UNIQUE COMBINATION. ] so, for "POS and DAT_ID" combination we need to get the lowest rank data first, if that is not present then get the other rank given in "DATA1" table and so on and if no rank is present then select the NULL row row data from "DATA2" table for ex: in DATA1 table for count(*)>1 and id=1, we have data as
--------------
IDDAT_IDRNK
11231
11242
11253
--------------
so, in "DATA2" table, first we will see for "POS and ORDR" combination which DAT_ID is present, i.e. in case od POS=11 and ordr=F, we will select
111231FD1
as it is having lowest rank in "DATA1" table and it is present in "DATA2" table,
for POS=12 and ordr=F, we will select
121242FD1
as we don't have "123 and 1" in "DATA2" table so we will select the next rank given in "DATA1" ( i.e. 124 and 2 ), similarly , for POs=31 and ordr=S, we will select, as this is the next available rank and DAT_ID present in "DATA2" table
1D131S1253
and if there is no rank present from "DATA1" table in "DATA2" table then we will select the NULL row, i.e. for POS=21 and ordr=F, we need to select :
1D121F
"there will be 6 row for each id"
the output we want is :
-----------------------------------------
IDNAMEPOSORDER DAT_IDRNK
1D111F1231
1D112F1242
1D121F
1D12321S1231
1D2322S1242
[code]....
View 4 Replies
View Related
Feb 20, 2012
I don't know what column name would there for my new view. so can i use dynamic SQL in View so that I can get correct column name?
View 2 Replies
View Related
Dec 12, 2012
i like to display a Table data like the below format,
Output:
EMPNO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
00094 122 153 145 224 245 545 114 544 444 111 555 222
00095 233 123 145 224 205 545 114 444 444 111 555 222
00096 163 123 145 224 215 545 114 551 444 111 555 222
00097 163 0 145 224 215 545 114 551 444 111 555 222
conditions:
where condition:
where year = 2007
Table Structure:
create table HR_PAYSLIP
(
EMP_NO VARCHAR2(6) not null,
YEAR NUMBER(4) not null,
MONTH NUMBER(2) not null,
BASIC_PAY NUMBER(9,2),
)
Insert Command;
INSERT INTO PAYSLIP (EMP_NO, YEAR,MONTH,BASIC_PAY)
VALUES(00046, 2007, 1, 2314);
Pls Note: The above table data i have mentioned is an example with employee numbers and the basic_pay for all months in the particular year 2007, the employee no may be more and that must be displayed only one time like above for year 2007, and if the basic salary is zero for a month then it should be displayed as zero for a particular month
So how to write a Query for that?
View 9 Replies
View Related
Jun 11, 2012
How can check that which query is taking how much load or time in execution on server in oracle.I want the soluation as like that, run the query and get the detail in desc order and identify that this query is required to tune or kill the session.
View 1 Replies
View Related
Sep 22, 2009
In my table ,data type of one among 10 columns is defined as varchar2(10).I need to check that column should accept only numeric value(0 to 99) or alphabetic value(a to z or A to Z) .It should not accept Alpha-numeric values.I tried like this
select c3 from demotab where to_number(c3) not between ascii('a') and ascii('z') ;
but I got error like 'Invalid Number'.how to implement this thro sql query.
View 3 Replies
View Related
Dec 6, 2012
I have a table and data as below.I need to select all the records if value have both 'M' and 'D'.If there is only 'M' or 'D' then select should not pull any records.
WITH data as (
Select '1' id, 'M' value from dual union all
Select '1' id, 'M' value from dual union all
Select '1' id, 'D' value from dual union all
Select '1' id, 'D' value from dual )
select value from data group by value
I tried below query but it is not working.
WITH data as (
Select '1' id, 'M' value from dual union all
Select '1' id, 'M' value from dual union all
Select '1' id, 'D' value from dual union all
Select '1' id, 'D' value from dual )
select * from data group by value having sum(count(distinct(value))) > 1
View 3 Replies
View Related
Jun 12, 2012
Our production DB version is 11.2.0.1 and we do use DBArtisian. Everyday morning we check table space usage using that tool and it generates the below listed query. It used to run in 15 secs. But since one week it is running for 5/6/8 mins. I have updated the statistics on sys objects and tuning advisor created the execution plan. So, now it is running in 2 mins. Nothing has been changed in the DB configuration. I see the same query running in secs in dev environment. understand what might be the issue and how could I improve the performance of this query.
SELECT SUB.TABLESPACE_NAME, SUB.STATUS, SUB.EXTENT_MANAGEMENT, SUB.SEGMENT_SPACE_MANAGEMENT, SUB.TOTAL_SPACE_MB, SUB.USED_SPACE_MB, SUB.FREE_SPACE_MB, SUB.PERCENT_FREE_SPACE, SUB.CONTENTS, SUB.TABLESPACE_GROUP, CASE WHEN SUB.TABLESPACE_NAME = P.VALUE AND SUB.CONTENTS = 'UNDO' THEN 'YES' ELSE 'NO' END
[code]...
View 5 Replies
View Related
Dec 26, 2011
i used sql loader to import data from csv file to my db.but every time the columns places are changed.o i need dynamic way to insert data into correct column in the table.
in csv file contains column name and i insert this data to temp table, after that i want to read data over column name.also i read the column names from (All_Tab_Columns) to make combination of column name between temp table and All_Tab_Columns table to insert data to right place...
View 39 Replies
View Related
Jul 8, 2012
I have a table
select * from testing
Quote:
ID NAME REDATA
-------------------- -------------------- --------------------
1 ABC,DEF,GHI,LKJ 000001
2 MNC,GHI,CTF 000002
2 rows selected
select * from testing where name like ('GHI,TCF');
now my output should be as above because GHI is common in both the names.if any of the where condition string matches the Name field then I need that record to be retrieved.
View 9 Replies
View Related
Oct 28, 2013
I want to delete the records which are less than or equal to this date
MIN(TRUNC(sale_week_date)-(1/(24*60*60)))
For that I have created the following procedure.
CREATE OR REPLACE PROCEDURE DELETE_DATA_WEEKISMIN_DATE DATE;SELECT MIN(TRUNC(sale_week_date)-(1/(24*60*60))) INTO MIN_DATE
FROM sales_week; DBMS_OUTPUT.PUT_LINE(MIN_DATE);
DELETE FROM sales WHERE sale_date <= MIN_DATEAND sales_creation_date <= MIN_DATEAND sales_update_date <= MIN_DATE; END;
If declare as MIN_DATE Date;
it's not taking time. If declare as MIN_DATE Timestamp; It is taking milli seconds also. 18-OCT-13 11.59.59.000000 PM SALE_DATE ,SALES_CREATION_DATE and SALES_UPDATE_DATE in this format 18-OCT-2013 11.59.59 AM. How to delete the records which are less than or equal to this date MIN(TRUNC(sale_week_date)-(1/(24*60*60))) ..
View 9 Replies
View Related
Nov 3, 2012
i have two tablespaces dictionary managed (SYSTEM,APPLSYSX) i tried to change to locally cause it will cause problem in future when trying to run OATM migration.i did it successfully on APPLSYSX,when i did it on system upon oracle procedure.i have to change all tablespaces to read only when i did that with tablespace APPLSYSD(alter tablespace APPLSYSD read only) i received errors
SQL> alter tablespace APPLSYSD READ ONLY;
alter tablespace APPLSYSD READ ONLY
*
ERROR at line 1:
ORA-01230: cannot make read only - file 636 is offline
ORA-01111: name for data file 636 is unknown - rename to correct file
ORA-01110: data file 636: '/vol5u/oracle/prddb/9.2.0/dbs/MISSING00636'
i have not this file on the OS
View 1 Replies
View Related
Mar 8, 2010
I have attached an SQL script.I would like to have the output in the fashion given below -
PERIOD_START_DATEPERIOD_END_DATEEMPLOYEE_NUMBERFULL_NAMELoan AmountLoan TypePay Value Outstanding Amount
1-Jul-0931-Jul-0924200Jonathan48000048004000
1-Jul-0931-Jul-0924200Jonathan32600022702990
the one in bold and underlined is the Column heading.
View 5 Replies
View Related
Feb 21, 2011
I just want to calculate difference between two dates in YY MM DD HH:MI:SS format through a SQL Query (not function).
Sample data is as follows-
to_timestamp('11-Feb-2008 12:23:00','DD-MON-YYYY HH24:MI:SS')
to_timestamp('2-Dec-2010 04:23:22','DD-MON-YYYY HH24:MI:SS')
or
how to calculate age in YY MM Day HH:MI:SS format. Suppose my DOB is '2-Feb-1988 11:10:15 AM'
View 4 Replies
View Related
Aug 4, 2010
Is it possible to print the output of a sql query in Excel format without using third party tools like Toad, Hora or any front end Application.
View 10 Replies
View Related
Apr 14, 2011
I want to convert a number to this format using below query but i'm getting error. how to correct the below query.
SELECT TO_CHAR(12345678, '99G9999D99') FROM dual;
error:-###########
View 2 Replies
View Related
Oct 2, 2012
I have following shell script :-
In our testing DB local Server , we are using following script ... We are connecting Via putty ...
CODEexport ORACLE_SID=testdb
sqlplus /nolog <<eof
conn sys/sys as sysdba
[Code].....
QUOTE 1. How can i set pagesize to get good format Our problem is output format is not good .. we set set pagesize from 0 to 120 .. no improvement .. " Again Re-Installed VMware tools also ... no improvbement ....
2. We are planning to set alert message if "Archive destination crossed 60 % Script automatically will generate alert message via mobile or mail.. JUst we will configure crontab ....
View 6 Replies
View Related
May 28, 2012
I need to view the rows of the result of a select query in table format in GUI application.I have used XMLELEMENT to somewhat match the scenario to display as ','(comma) separate values as b belwo
SELECT RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
',')
AS empid,
RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),
[code]...
But the case is I need to display the value in table format Horizontally as below
EMPIDemployee nameDEPID
778
278CLARK10
397MILLER
934KING
[code]...
View 14 Replies
View Related
Jul 4, 2013
I have data in bellow format...
NAMEPROMPT CHARACTER1 CHARACTER2 CHARACTER3
MOAAPPROVER15100160 5100165 MUNGAI
MOAAPPROVER25100160 5100165 MUNGAI
MOAFINAL APPROVER5100160 5100165 MUNGAI
so all values which are in CHARACTER1 column belongs to APPROVER1,CHARACTER2 belongs to APPROVER2,and CHARACTER3 belongs to FINAL APPROVER.
Now i want to display records in bellow format
NAMEPROMPT CHARACTER1 CHARACTER2 CHARACTER3
MOAAPPROVER1 5100160 5100160 5100160
MOAAPPROVER2 5100165 5100165 5100165
MOAFINAL APPROVER MUNGAI MUNGAI MUNGAI
View 6 Replies
View Related
Sep 17, 2013
I have a table of below structure:
CREATE TABLE Emp_addrs
(
EMP_ID NUMBER(15) NOT NULL,
ADDRESS_ID NUMBER(15) NOT NULL,
SITE_USE_ID NUMBER(15) NOT NULL,
SITE_USE_STATUS VARCHAR2(1 BYTE) NOT NULL,
SITE_USE_CODE VARCHAR2(30 BYTE) NOT NULL)
Insert Script code :
insert into Emp_addrs values ( '1207' , '1846', '2342','A');
insert into Emp_addrs values ( '1207' , '1846', '2343','I');
insert into Emp_addrs values ( '61618' , '165200', '261449','A');
[Code]...
A combination of emp_id & address_id can have multiple site_use_id's. I want to select the max(site_use_id) where site_use_status ='A'.
Now Site_use_status can have either = 'I' or 'A' value.
For a combination of emp_id and address_id , there could be cases when there is no record with site_use_status ='A'. In such cases I need to select the max(site_use_id) (and obviously site_use_status ='I').
Just to clear my requirements, out of the above records I want the following records:
'1207' , '1846', '2342','A'
'61618' , '165200', '261449','A'
'1003' , '1004', '1007','A'
'1005' , '1010', '2002','I'
'2005' , '2010', '3002','A'
View 9 Replies
View Related
Oct 22, 2012
i am using one query but not getting correct minutes.
here is my query:
v_Interval:= to_timestamp(v_temphrs,'HH24:MI:SS')-to_timestamp(v_outpunch1,'HH24:MI:SS');
v_TotalHrsMin1 := extract(hour from v_interval) * 60 + extract(minute from v_interval);
here v_interval datatype is "interval day to second" and v_temphrs datatype is varchar2 and value is : 12:00:00 and v_outpunch1 datatype is varchar2 and value is: 06:10:00
and v_totalHrsMin1 datatype is number.
here i should get value 370.
but i am getting value 350.
View 3 Replies
View Related
Feb 7, 2013
1. I have 6 data bases, one process is running slowly, how do you find which data base that process belongs to?
2. I have 6 data bases, only one data base we have ASM instance, how do you find which data base has ASM?
View 1 Replies
View Related
Nov 22, 2012
formulating sql query
Basically what I want is that I need to get desired result in such a way that, whenever Transaction type is Sales Order Issue, I want last TRANSACTION_COSTED_DATE of 'Intransit Shipment'
INVENTORY_ITEM_ID TRANSACTION_COSTED_DATE TRANSACTION_TYPE R
123 28-06-2012 21:36 Intransit Shipment
123 23-07-2012 01:25 Sales order issue 28-06-2012 21:36
123 30-07-2012 05:20 Sales order issue 28-06-2012 21:36
[Code]...
Lag with offset 1 doesn’t work as it will only go to previous row, What I want is that it should go to row above where transaction type is Intransit Shipment
Sample data and query I tried
with sampl_rownum_reset as
(select '123' inventory_item_id,
to_date ('28-Jun-2012 9:36:23 PM ', 'DD-MON-RRRR HH:MI:SS AM')
transaction_costed_date,
to_date ('28-Jun-2012 9:35:23 PM ', 'DD-MON-RRRR HH:MI:SS AM')
[Code]....
View 2 Replies
View Related
Oct 13, 2010
I have a query on displaying data as per my requirement. I have created a table called sales it has four columns
create table sales(country,state,district,sales);
and am inserting some same data
insert into sales('india','TN','Chennai',100);
insert into sales('india','TN','KPURAM',120);
insert into sales('india','TN','Bangalore',35);
insert into sales('india','ANDR','Guinder',100);
insert into sales('india','ANDR','Nellai',76);
insert into sales('london','city-a','xstreet',89);
insert into sales('london','city-a','binroad',100);
select * from sales;
country state district sales
india TN chennai 100
india TN KPURAM 120
india TN Bangalore 35
india ANDR Guinder 100
india ANDR Nellai 76
london city-a xstreet 89
london city-a binroad 100
the data is displayed in this format. How i am trying to display data.
View 5 Replies
View Related
Jun 1, 2012
I want to get the following format of data in row format using PLSQL. I want to do that in using a shell script also.
Suppose I have the data like this
123
45
2
789
how to write it in PLSQL as follows:
1427
25 8
3 9
View 3 Replies
View Related
Dec 2, 2012
I have one table storing all the information about employye, i have to develop three different reports based on the same table with different groupings , is there a easy way to call each format differently using single interface ,something like ref cursor.I dont want to create two or 3 different reports , instead choose the format.
CREATE TABLE OT_JOB_DET
(
JT_TXN VARCHAR2(12 BYTE),
JT_NO NUMBER,
JT_DT DATE,
[Code].....
View 4 Replies
View Related
Dec 26, 2011
is it possible convert data from forms 6i to pdf format?
View 4 Replies
View Related
Sep 11, 2013
I want to print data from a clob in XML format and use the following
PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr varchar2(32767);
line varchar2(32767);
cnt NUMBER;
[Code]...
However the length of the clob is 13832630 which is too large for a VARCHAR2. Thus my output of line is cut off at 4000 characters. How can i increase this... Do i overlook something here in my code?
View 13 Replies
View Related
