SQL & PL/SQL :: ANSI To NON-ANSI SQL Conversion
Dec 10, 2010
A very simple question yet so difficult...
I have a ansi query...
select * from
tz1
left outer join tz2 on tz1.aa=tz2.cc and tz1.bb is null;
What is the most efficient non-ansi way of writing it?
-----------------script-----------------
create table tz1(aa int, bb int);
create table tz2(cc int, dd int);
insert into tz1 values (1, 100);
insert into tz1 values (2, 200);
insert into tz1 values (3, 300);
[Code]......
View 6 Replies
ADVERTISEMENT
Jun 17, 2011
To rewrite this sql which is in ANSI 92 standard to ANSI 89 standard.
SELECT "PROJECT"."X_SAMPLED_DATE", SAMPLE"."SAMPLE_NUMBER"
FROM "SHIRE_PRD"."LimsUser"."SAMPLE" "SAMPLE" INNER JOIN "SHIRE_PRD"."LimsUser"."PROJECT" "PROJECT" ON"SAMPLE"."PROJECT"="PROJECT"."NAME"
WHERE ("SAMPLE"."SAMPLE_TYPE"='EM' OR "SAMPLE"."SAMPLE_TYPE"='WATER') AND "SAMPLE"."STATUS"<>'X' AND("PROJECT"."X_SAMPLED_DATE">={ts '2011-05-01 00:00:00'} AND "PROJECT"."X_SAMPLED_DATE"<{ts '2011-06-01 00:00:00'})
ORDER BY "SAMPLE"."PRODUCT"
View 4 Replies
View Related
Nov 25, 2011
I wondered the possibility of converting a content file in the format ansi to utf-8 directly from Oracle.
View 1 Replies
View Related
Apr 11, 2011
the below merge statements has outer join.
merge into merge_st t
using (select * from merge_st1) src
on (t.id=src.id(+))
when matched then update set name=src.name;
I need to convert oracle joins to ANSI joins. I have tried below
update (select t1.name as t1_name,t2.name as t2_name
from merge_st t1 lef outer join merge_st t2
on(t1.id=t2.id))
set t1_name=t2_name;
It statements shows error like cannot modify the non key preserved tables.I have cheked these table has contains whether primary key or not.there is no constraints for these tables.Our application, constaints handle in front end. so we cannot create any constraints in oracel database.how to convert oracle joins to ansi join?
View 4 Replies
View Related
Oct 9, 2013
Use ANSI standard JOIN syntax for example i have this code.
SELECT resv_num, unit_date
FROM p_resv_unit ru, p_pm_unit_night pun
WHERE pun.property_id = in_property_id
AND pun.pm_unit_num = cvUnitNum
AND pun.unit_date BETWEEN start_date AND end_date
AND pun.resv_unit_id = ru.resv_unit_id;
[code]....
and is it a good idea to change it, because both ways it works?
View 15 Replies
View Related
Jun 10, 2013
which of the statements TRUNCATE and TRUNC are SQL ISO ANSI compliance ?is Truncate a function or a statement ?
View 3 Replies
View Related
Dec 9, 2010
I want to know about ANSI JOIN in Oracle 9i and how to use this ANSI JOINS and where we have to implement this ANSI JOIN in real time.
View 2 Replies
View Related
May 25, 2011
...continued from "Problems with full outer join"I think that ANSI joins in Oracle doesn't work correctly. Or am I doing something wrong? The query looks like this:
select nvl(k.id_pers,t.id_pers) id_pers, k.dat_avst
, id_trans, dat_trans
from
( select ka.id_pers, ka.dat_avst, ka.dat_nasta_avst
[code]...
It's a full outer join between one table (with a subquery) and an inline view with two tables You can see the returned rows in the listing below. The query returns one row where there is a match between t_trans and t_kontoavst.It also returns two rows from table t_kontoavst with no correspondence in t_trans.Finally it returns 26 rows from the table t_trans with no correspondence in t_kontoavst. But among them there are many rows in contradiction to the conditions:
and trunc(dat_trans) >= to_date('20040101','yyyymmdd')
and trunc(dat_trans) <= to_date('20050604','yyyymmdd')
Actually it seems to return all the 27 rows in t_trans (one of them joined to t_kontoavst).These conditions are actually not part of the join so I changed it to:
where trunc(dat_trans) >= to_date('20040101','yyyymmdd')
and trunc(dat_trans) <= to_date('20050604','yyyymmdd')
It's not clear to me if this where condition belongs to the joined result or just to the right joined inline view.With this change the correct rows from t_trans where returned but unfortunately the two rows from t_kontoavst with no correspondence in t_transdisappeared. I thought maybe that is because dat_trans is null for these two rows after the join. Therfore I also includeddat_trans to be null. This can only happen when t_trans is missing
where dat_trans is null
or (trunc(dat_trans) >= to_date('20040101','yyyymmdd')
and trunc(dat_trans) <= to_date('20050604','yyyymmdd'))
But that didn't change the result I have also tried right and left joins and it produces similar errors.One other thing i tried was to replace the inline view with one of the underlying tables t_trans. But the result was the same.Data returned from the original query (see query above):
ID_PERSDAT_AVSTID_TRANSDAT_TRANS
1945050505022005-05-011721642005-05-16
194505050502null1723722005-06-16
194505050502null1723732005-07-16
[code]...
Data in the tables.SQL Statement which produced this data:
select id_trans, id_pers, dat_trans
from t_trans
order by id_pers, dat_trans, id_trans
ID_TRANSID_PERSDAT_TRANS
1721641945050505022005-05-16
1723721945050505022005-06-16
1723731945050505022005-07-16
[code]...
SQL Statement which produced this data:
select id_pers, dat_avst
from t_kontoavst
order by id_pers, dat_avst
ID_PERSDAT_AVST
1945050505021997-05-01
1945050505022005-05-01
1958080808071997-05-01
[code]...
View 16 Replies
View Related
Apr 23, 2012
Some app (wich I can't touch) stores a value that I need to modify with a trigger or even with a view.
Original value: 7B751BF0 (2071272432 in decimal)
Stored value: 4028331387 (F01B757B in hexa)
To make things short, I'm getting 4028331387 and I need to convert it to 2071272432.
View 6 Replies
View Related
Mar 12, 2013
I need to move one of my LMT tablespace to DMT, Can I do it , I know that the DMT's are depreciated since Oracle 9i but still need to know this.I am trying the below mentioned method to achieve the same.
SQL> exec DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS');But I am facing the below mentioned error:
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS'); END;
*
ERROR at line 1:
ORA-10616: Operation not allowed on this tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 216
ORA-06512: at line 1
View 10 Replies
View Related
Jan 12, 2012
I have an issue with date conversion. In my table dates are in this formate:
04-DEC-90 10:46:46
I need to convert it in To_date.
SQL> select to_date('04-DEC-90','dd-mon-yy') from dual;
TO_DATE('04-DEC-90','DD-MON-YY
------------------------------
04/12/2090
SQL>
But the year shoul be 1990 not 2090.
View 3 Replies
View Related
Jan 4, 2013
I have a table datatype number (12,10) that I am reading out of. I am taking the value from this source table and inserting it into a destination table of datatype number (12,15).
I do not have the ability to alter the tables. How can i convert this number so i can insert. I am currently getting the error "ORA-01438: value larger than specified precision allowed for this column"
I am trying to use the to_number, but it not working. How can i format this number field so i can read it from source where i have number (12,10) and insert it successfully in a higher precision table of number(12,15)
View 1 Replies
View Related
Mar 27, 2012
Again i getting confused with conversion function especially Explicit data type conversions. some cases oracle server automatically converts the data to the required type. This is called IMPLICIT CONVERSION. Explicit conversions are done by using the conversion functions.
Oracle Explicit Data Type Conversions are
1 TO_CHAR
2 TO_DATE
3 TO_NUMBER
View 1 Replies
View Related
Jan 13, 2011
SELECT EMPNO ,ESAL,DEPTNO FROM EMP;
O/P
EMPNO ESAL DEPTNO
101 1000 10
I WANT O/P LIKE THIS
REQUIRED O/P
101
1000
10
View 5 Replies
View Related
Jan 21, 2013
My requirement is to converts row to Column information and sum of column in Weight kg in below manner Data in columns are not of same nature they can keep on changing
Item Supplier Consumer Package DWP_NO DWP_ED Quality_code Pakaging Material WEIght kg
80026020 13984
90225217 21385 1 1 3 Not Applicable 22-paper .001
90218863 16578 1 1 1 Not Applicable 04-low density polyethylene (0.002+.002+.002)=.008
90218349 14507 1 1 1 Not Applicable 05-polypropylene,22-paper (.02+.05) =.07
40221108 21519 3,2,1 1 2 IKEA-CB-60 22-paper (.32+0.3+0.45)=1.07
find table structure
CREATE TABLE TEST_ROW_TO_COL
(
ITEM_CODE VARCHAR2(10 CHAR),
SUPPLIER_CODE VARCHAR2(10 CHAR),
"Consumer Package" NUMBER(2),
[code]...
ans insert statement of sample data
--SQL Statement which produced this data:
-- select * from Test_row_to_col where
item_code in ('80026020','90225217','90218863','90218349','40221108')
[code]...
I am using Oracle 11g
View 1 Replies
View Related
Aug 29, 2013
I have one query in SQL server. I want to convert that query to Oracle. Well i am not that good with writing queries in Oracle. Following is the query in SQL server.
DECLARE @StartYear AS INT = 2010;
DECLARE @EndYear AS INT = 2014;
WITH
years
AS (SELECT YYYY = @StartYear
UNION ALL
SELECT yyyy + 1
FROM years
WHERE yyyy < @EndYear)
[code].......
Which Gives output in following format.
id_datedateYearMonthNumberMonthDayOfMonthDayOfWeekNumberDayOfWeekWorkingDay
201001011/1/201020101January16Friday1
201001021/2/201020101January27Saturday0
201001031/3/201020101January31Sunday0
201001041/4/201020101January42Monday1
201001051/5/201020101January53Tuesday1
View 3 Replies
View Related
May 31, 2011
My company uses Remedy which stores date/time as an integer. I use the following formula to convert it to a readable date:
TO_CHAR(TO_DATE('01/01/1970', 'MM/DD/YYYY HH:MI:SS AM')+ ((createdate - 4*3600)/(60*60*24)),'MM/DD/YYYY HH:MI:SS AM') as Create_Date
I have to use the "4*3600" in order to get the date to show up correctly, but even then the date sometimes comes up wrong. If the date occurs in the morning, then the date shows up as the previous day. I am sure this is probably due to the offset I am adding in the above formula. If I don't add the 4 hour offset, then the date shows up 4 hours off.
View 3 Replies
View Related
Aug 11, 2010
I have coluum value like 00000000399. i need to convert the float like i mentioned
00000000399 should be loaded as 3.99
00000001197 should be loaded as 11.97
00000000003 should be loaded as 0.03
wat option i try to change the value
View 1 Replies
View Related
Nov 6, 2011
is there any licensed version tool available to convert objects from sql server to oracle especially procedures? I have tried with open source tools but that tool didn't convert properly.
View 7 Replies
View Related
Feb 1, 2007
This is my query:
DELETE FROM GR_GUEST WHERE GUEST_NAME='Ambassador Jack Binns bring Patrick Clawson' AND RESERVATION_ID_FK=21635
It gives an ORA-01460 which is "unimplemented or unreasonable conversion requested"Removing the " AND RESERVATION..." gives the same error.
The problem is that GUEST_NAME is a VARCHAR (150) and RESERVATION_ID_FK is an INT so I don't see where any conversion is coming in.I had changed the name a little bit as it used to a (, ) and a : in the GUEST_NAME. I thought that might be causing the issue so I removed them with a REPLACE through an UPDATE query. However, the record still needs to be deleted.
It should be a simple query as it happens on this table all the time, so I don't know why this one is different.
View 3 Replies
View Related
Mar 5, 2008
1075329297.572
This is unix time where 1075329297 is seconds 572 is milliseconds.
first il store this time in oracle database.when i am retrieving it i want this date into yyyy-dd-mm format. Is it possible to do it in oracle.(using convert function) or is there some other way?
View 8 Replies
View Related
Feb 7, 2013
converting a string to date, my string looks like '01022013' '04022013', I tried to_date('01022013', 'dd-mm-yyyy') but couldn't work.
View 6 Replies
View Related
May 14, 2011
I have 2 tables.The column in table A is number and Column in table B is a varchar2 datatype.I have to use the Column of table B as a filter to column of Table A.Below is the example.
create table A(Col1 number);
Inert into A values(1);
Inert into A values(2);
Inert into A values(3);
Inert into A values(4);
Create table B(Col1 Varchar2(100));
Insert into b value ('1,2,3');
Select * from A where col1 in (select col1 from b)
Error: Invalid Number
Is there a way to convert the varchar to number.The varchar field have multiple characters (numbers) seperated by commas.
View 7 Replies
View Related
Dec 19, 2010
I have a problem i need to convert a blob column contains pic file to long row
i had many tries but no one succeeded
-----------------------------------
Source table | destination table
id number | id number
img blob | img long raw
------------------------------------
1 - INSERT INTO destination table SELECT id , img FROM Source table WHERE ROWNUM < 2
i have this error ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 146092, maximum: 2000)
2 - INSERT INTO destination table SELECT id , dbms_lob.SUBSTR(img,0,2000) FROM Source table WHERE ROWNUM < 2
now errors but the lengh is 0 (i have no file )
What can i Do
View 7 Replies
View Related
Sep 19, 2011
I want to convert Clob column datatype to NClob datatype. I tried to modify the column by using below method but got the mentioned error:
SQL> alter tab1 TABLE_NAME modify COLUMN_NAME nclob;
*
ERROR at line 1:
ORA-22859: invalid modification of columns
One of the other method with which I am familiar with is :
Quote:
First to Create a new column of the desired type and copy the current column data to the new type using the appropriate type constructor.
how to change the clob to Nclob datatype without using the temp column usage?
View 1 Replies
View Related
May 6, 2010
I am converting sybase to oracle database. during the migration, i have small doubt in triggers.
sybase source
create trigger testss_dt
on test
for delete
as
if @@rowcount = 0
return
insert into test
select id from deleted
return
I have converted oracle code here
create or replace trigger testss_dt
before delete on test
for each row
begin
insert into test
values(:old.test);
end;
But i have ignores @@rowcount concepts in oracle. what @@rowcount indicates in trigger?
View 10 Replies
View Related
May 6, 2010
I want to change a table datatype from date to number where already existing data should get convert.any possibility of doing where i tried like this but no get changing. Even as Julian format is working a bit i want the data to come as GMT format
Scenarios where i tried are like this.
ALTER TABLE Contacts ADD ALERT_DATE1 NUMBER(20,0)
/
UPDATE Contacts SET ALERT_DATE1 = TO_NUMBER(ALERT_DATE)
/
ALTER TABLE Contacts DROP COLUMN ALERT_DATE
/
ALTER TABLE Contacts RENAME COLUMN ALERT_DATE1 TO ALERT_DATE
/
but second statement failing.
Julian fomat like
SELECT sysdate,
TO_CHAR(sysdate, 'J'),
TO_DATE(TO_CHAR(sysdate, 'J'),'J')
FROM dual;
View 10 Replies
View Related
Sep 19, 2011
I have two cases
i . Convert Image to Hex String
ii . Convert the Hex Sting to Image
Case i is done, is Case ii is Possible in Oracle 11g Rel 2..?
View 8 Replies
View Related
Jun 2, 2012
My application spools the data from database into file and file is fixed length. My application working fine when database has single byte characters. But I am getting fields postion misposition and length mismatch in spooled line when data base has Multi byte characters(ê).
I want to convert the multi byte characters into single byte while spooling the data( ê ==> e)
The below query has converted the multi byte to single byte character. But actual value is modified into lower case.
####################################################
select utl_raw.cast_to_varchar2((nlssort(c.Name,'nls_sort=binary_ai'))) sbyte, '|'
c.Name
from EMP c
viva! experiencias | Viva! Experiências
####################################################
View 2 Replies
View Related
Mar 24, 2011
I have the following query
select a.EV_ACTOR_NAME, round((b.total_transfer/(24 * a.Max_Throughput)) * 100) AVG_ThroughPut,round(((b.total_transfer/a.Max_Throughput) * 100 )) MAX_Throughput from
(select distinct EV_ACTOR_NAME,((max(EV_TRANSFER_RATE) over ( partition by EV_ACTOR_NAME)) * 60 * 60) /1024 Max_Throughput from dprt_events where EV_ACTOR_NAME is not null ) a,
(select distinct EV_ACTOR_NAME, (sum(EV_TRANSFER_SIZE) over( partition by EV_ACTOR_NAME)/1024/1024/1024) total_transfer from dprt_events where EV_ED_ID in (40,105,112) and EV_END_TIME between (sysdate - 20) -12/24 and sysdate - 20) b
where a.EV_ACTOR_NAME = b.EV_ACTOR_NAME
This is the output i get :
EV_ACTOR_NAME AVG_THROUGHPUT MAX_THROUGHPUT
------------ ------------- --------------
actor2 1 33
actor3 0 0
actor1 2 53
How do I convert the above out put to following format.
EV_ACTOR_NAME value1 value2
-------------- -------------- --------------
actor2 AVG 1
actor2 MAX 33
actor3 AVG 0
actor3 MAX 0
actor1 AVG 2
actor1 MAX 55
I tried the following
WITH data AS
(select level a from dual connect by level <= 3)
select EE_ACTOR_NAME,decode(a, 1, AVG_ThroughPut, 2, MAX_Throughput) value from
(
[Code]...
but i always get the following error ORA-00904: "A": invalid identifier
View 5 Replies
View Related
