Translate following SQL query from SQL Server syntax to Oracle syntax.
SELECT ID,
[LMT(MTR)] = MAX(case when TYPE = 'LMT' then VALUE end),
[AAD(KGM)] = MAX(case when TYPE = 'AAD' then VALUE end),
[VOL(MTQ)] = MAX(case when TYPE = 'VOL' then VALUE end)
FROM yourtable
GROUP BY ID
This is my working query in ms access...
UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));
This doesn't work in oracle, I Googled and read that update doesn't work with inner join in oracle.
I want to take an export of table MESSAGE, and filter it for the day of 17 JUL 2013 (just to limit the size). i used the following expdp command but its not working.
expdp SYSTEM directory=DATA_PUMP_DIR dumpfile=DB_16_08_2013.dmp logfile=FA0001P_BG_16_08_2013.log TABLES=schema.MESSAGE QUERY=schema.MESSAGE:where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS')
But with select query i am able to retrieve the rows for the specific date.
select * from MESSAGE where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS')
Here is the command with syntax error.
[oracle@orcl log]$ expdp SYSTEM directory=DATA_PUMP_DIR dumpfile=DB_16_08_2013.dmp logfile= DB_16_08_2013.log TABLES=schema.MESSAGE QUERY=schema.MESSAGE:where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS')
-bash: syntax error near unexpected token `('
I would need to translate the Oracle PL/SQL procedure to SQL Server 2000 SP4.
This is a package (body) that needs to be translated into a form that SQL server 2000 can unserstand it.
AS
FUNCTION object_name_fix (sobjectstring IN VARCHAR2)
RETURN VARCHAR2
AS
[Code].....
am new to SQL server. I have below statement what is ON [PRIMARY]. I want to write a similar create script to be written for Oracle & need to understand the importance of ON [PRIMARY]
Create table [emp]
(
[e_name] [varchar] (255) NOT NULL,
[e_sal] [decimal] (20,0) NOT NULL
) ON [PRIMARY]
The link from where I can get syntax of all the commands available in oracle?
View 6 Replies View RelatedWhat is the syntax for exporting only the procedures of a particular user.
View 1 Replies View RelatedThis is my working query in ms access...
UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));
This doesn't work in oracle, I googled and read that update doesnt work with inner join in oracle..translate this query to work on oracle?
I am trying to pass an Optional 'ALL' Parameter to Oracle in the SQL statement below...
USER.STATUS can be either 0 or 1 in the Source-Data (Inactive or Active). However, the :P_STATUS Parameter can be either 0, 1 or 2 (ALL).
I tried an IF/THEN/ELSE statement in the SQL below - but it doesn't work as is.
Gives and "ORA-00920: invalid relational operator" error...
-------------------
SELECT
i.LAST_NAME SURNAME,
i.FIRST_NAME GIVEN_NAME,
DECODE(u.STATUS, 1, 'Active', 0, 'Inactive') STATUS,
u.STATUS STATUS_CODE
[code].......
I am new to oracle and sql in general, I received an oracle create schema that needs to be converted using non-oracle syntax. I have never seen this syntax before.
What does the following syntax mean?
CODE,line_status(1:20) char(2) null
CODE,file_line(1:6) char(40) null
I ran the following control file in sql loader:
LOAD DATA
INFILE "gateway.csv"
truncate
INTO TABLE GATEWAY
Fields terminated by ","
Optionally enclosed by '"'
trailing nullcols
[code]....
and I got the following error:
zcyds891:/opt/oracle> sqlldr gwcem/gwcem@pfs control=gateway.ctl log=/tmp/ldr.log bad=/tmp/bad.log
SQL*Loader: Release 9.2.0.8.0 - Production on Tue Dec 7 05:07:59 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL*Loader-350: Syntax error at line 12.
Expecting "," or ")", found "INTERGER".
GATEWAYPROTOCOL INTERGER,
^
The current OS is Solaris, SunOS 5.10.
I have a package,
PACKAGE PK_EXCEL_TO_DB IS
TYPE tKeyValue IS RECORD (
CROUTE VARCHAR2(255),
VROUTE VARCHAR2(1000),
[code]...
Package body is
PACKAGE BODY PK_EXCEL_TO_DB IS
PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
vcInsert VARCHAR2(3500);
BEGIN
[code]...
what i want is: 'output vcInsert to a forms-item and check the syntax of the resulting insert'. How can I do this
I created an externally authenticated user in database. And can login without password with below syntax.
SQL> connect / @TESTDB
Connected.
SQL> show user;
USER is "SCOTT"
This scott user has a proxy permission to another DBuser PROXY_USER. Previously I used to login using below syntax.
connect scott[proxy_user]/password_for_scott@TESTDB
So now, what syntax should be used for this "ExternallY Authenticated" user to login as a proxy user?
I have a query optimized as to it indexes and others runs immediately when the answer is few records in SQL Server such as Oracle, however when the result is large eg 20,000 records all data access times are very diferent. The query returns many fields (about 20) and some of them are of type Varchar 250 and some of 2000 I understand here may be the problem, but not is because for similar results (20,000 records) sql run in 2 seconds and Oracle but it responds little to have full data takes around 30 seconds. The problem is really in bringing information to all these fields since if the inquiry it also but only returning a numeric field is done in 2 seconds. Tests I've done them both through ODBC, in the Toad as in the own Oracle console on the server, so it is not problem Driver or flow of data through the network, I would like to think that this is some of the settings I think there is as much difference between Oracle and Sql. The databases are ORACLE 10 and SQL Server 2008.
View 1 Replies View RelatedDECLARE @MainTable TABLE (UniqueID INTEGER, Category VARCHAR(200), WeekDate DATETIME, VALUE INTEGER)
INSERT INTO @MainTable VALUES(123, 'Shirts', '10/07/2011', 5000)
INSERT INTO @MainTable VALUES(123, 'Shirts', '10/14/2011', 8000)
INSERT INTO @MainTable VALUES(124, 'Pants', '10/07/2011', 4000)
INSERT INTO @MainTable VALUES(125, 'Shorts', '10/14/2011', 8000)
INSERT INTO @MainTable VALUES(126, 'Shoes', '10/21/2011', 9000);
--select * from @MainTable;
[code]...
The query works with all the CTEs up to the last select statement. Oracle does not support the OUTER APPLY statement, how should the last piece be written to make it work in Oracle?
How I can find the hostname for an oracle session id?
View 4 Replies View RelatedIs there such a thing as:
NOT LIKE IN (record%, %record, etc)
What I need is NOT IN with the ability to query by wild card. NOT LIKE IN doesn't seem to work for me. Is there another way to do this without having to write out each statement (WHERE NOT LIKE record% AND NOT LIKE %record, etc.)?
We have just migrated from DB version 10 to version 11.2.0.3.We have found out, that we have to do a revision of old queries, because there are probably differencies in the syntax. where are these differencies described? Here is an example, what google didn't told me.... I have some condition where table_1.id = table_2.id ( +) What doesn't mean ( +)?
View 14 Replies View RelatedI am familiar with using tnsping with the standard sqlnet.ora/tnsnames.ora or other oracle connection definition methods. Can I specify the actual connection definition on the tnsping command line? Something like:
tnsping server:port:instance
DECLARE
T_PASSWORD VARCHAR2(40) :=null;
BEGIN
T_PASSWORD := select MAX(TPASSWORD) from APEXPWD;
if :p101_PASSWORD = T_PASSWORD then
I'm not getting the syntax to LOAD the value of APEXPWD.TPASSWORD from the Database into the VARIABLE T_PASSWORD in APEX so that I can compare it against the users PASSWORD. This allows me to compare if the user has a temp password and then redirects them to the correct change password page. Everything is working except getting the T_PASSWORD variable set correctly.
I have this ORACLE SQL and just about understand join syntax in the From clause, i.e.
SELECT *
FROM TABLE 1 LEFT OUTER JOIN TABLE 2
ON TABLE1.FIELD_X = TABLE2.FIELD_X
However, I've inherited the sql below.
SELECT
RELOCATION.START_DATE,
STUDENT.SURNAME,
STUDENT.FORENAME
CURRENT_SCHOOL.BASE_ID
RELOCATIONS.STUD_ID
[code]......
I particularly don't understand this part
' FROM (MYDATABASE.STUDENT STUDENT
LEFT OUTER JOIN MYDATABASE.BASES CURRENT_SCHOOL '
why the table name student is referenced twice?And again for ' MYDATABASE.BASES CURRENT SCHOOL '?
When I put this into SSRS it shows only links between the tables STUDENT, RELCOATIONS and CURRENT_SCHOOL. Bases isn't mentioned in the tables diagram. it is still referred to in the raw SQL.
The above SQL works fine, i just don't understand what it's doing!
Can u explain the syntax of materialized view??????
View 1 Replies View RelatedIs there some way to validate the syntax of sql sentence without actually executing it? I dont want to check if the objects exist or not, just want to check the syntax.
View 6 Replies View RelatedI got a variale which passing by other problem to pl/sql colBuf="USER,NAME,LOC,DATE"
need to make it like this
tempcolBuf=||'^B'||bfary(i).user||'^B'||bfary(i).n ame||'^B'||bfary(i).loc||'^B'||bfary(i).date||'^B'
any command can use? or loop? get the solution by syntax...
why this code work in 8i but not in 9i, 10g, 11g
declare
type typ_curseur is ref cursor;
l_cursor typ_curseur;
[Code]....
When running in 9,10 or 11 I get the error ORA-22806: not an object or REF. After investigation I found that the problem is the bind variable ":bind.variable". Notice the dot between bind and variable. If i remove the dot or replace it by underscore everything work fine.
I am just looking for some documentation about this problem to know if it was some new restriction starting in 9i and if there is something to set to make it work like in 8i or maybe it will be better to change all our program that are using this kind of syntax. Actually i did not find anything on metalink.
I am trying to truncate a partition using syntax .ALTER TABLE SALES6 TRUNCATE PARTITION FOR(DATE '02-03-07')
but when i query back to table i can still see the data it is not truncating.
create table sales6
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
(
[code]....
I have 2 procedure defined in a package.Procedure is defined so that, it has 3 varchar2, which is used to store the insert statement.
ex:
CREATE OR REPLACE PACKAGE PK_LOGIC AS
PROCEDURE MOVE_table1_TO_table2;
PROCEDURE MOVE_table2_TO_table3;
END PK_LOGIC ;
/
[code]....
My problem is how can we execute this procedure in SQL DEVELOPER tooL?I tried using,
CALL PK_LOGIC .MOVE_table1_TO_table2(); or EXEC PK_LOGIC .MOVE_table1_TO_table2;
SQL>
create table oldemp8
(
fname varchar2(30),
salary number(6),
job_id varchar2(20)
)
[code].......
Table created.
SQL> select * from oldemp8;
select * from oldemp8
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "(": expecting one of: "comma, defaultif,
nullif, )"
KUP-01007: at line 7 column 16
ORA-06512: at "SYS.ORACLE_LOADER", line 19
SQL>
what is the syntax error in the above command. I place the notepad file properly.i create external table before many time but cant find any this type of error.
os windows xp 2000. oracle 10g (10.2.0.1.0)
I am trying to use the following case statement in my where clause. My problem here is, I get no rows.
tab1.col1 =
case
when (tab1.col1 = 'VAR') and (tab1.col2 is null or tab1.col2 >= tab2.datecol) then
tab1.col1
else
null
end
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?