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?
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.
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;
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
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
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.
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.
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
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
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...
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.
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))) ..
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
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 ....
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
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:
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.
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
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.
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.
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?
