Equivalent Of Oracle's INSTR( With 4 Parameters) In SQL Server

Feb 2, 2005

The syntax for Oracle's INSTR function is

instr (string1, string2, [start_position], [nth_appearance])

string1 is the string to search.

string2 is the substring to search for in string1.

start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.

nth_appearance is the nth appearance of string2. This is optional. If omiited, it defaults to 1.

In SQL Server, we are having CHARINDEX and PATINDEX functions. But they will not accept the fourth paremeter (nth_appearance)

Do anybody know the solution for this ????

View 1 Replies


ADVERTISEMENT

Instr() Equivalent In SQL Server

Jul 20, 2005

I am trying to convert a complex function from Oracle to SQL Serverand have come across Oracle's Instr() function. I see SQL Server hasCHARINDEX() which is similar, however it does not provide some keyfunctionality I need. Here is an example of the Oracle code:if Instr( sTg , cDelim, 1, 3 ) > 0 thensd := SubStr( sTg, Instr( sTg , cDelim, 1, 1 ) + 1, Instr( sTg,cDelim, 1, 2 ) - Instr( sTg , cDelim, 1, 1 ) - 1)end if;Has anybody converted anything similar to this within SQL Server? Anyhelp is GREATLY appreciated!Thanks.

View 5 Replies View Related

MS-SQL Server Equivalent To Oracle 9i?

Jul 20, 2005

All,Oracle 9i provides a "USING" clause option for inner joins, thatallows me to say:SELECT * FROM TBL1 JOIN TBL2 USING KeyColumnassuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirablefor our software make use of, but we also support SQL Server. Thereis no USING option available, andSELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumncauses an ambiguous column error on KeyColumn.Is there any equivalent to this Oracle functionality on SQL Server?KingGreg

View 7 Replies View Related

What Is The SQL Server Equivalent Of Oracle's ROWNUM

Jan 15, 2001

Is there an equivalent to Oracle's ROWNUM in SQL Server. ROWNUM, when added to a select statement as a column - the query would return an automatic counter, numbering each row returned.

View 1 Replies View Related

Oracle MINUS Equivalent In Sql Server

Apr 22, 2003

Hi,
Is there something equivalent to the MINUS in ORacle ?
Or a workaround ?
thanks

View 11 Replies View Related

Is There A SQL Server Equivalent For Oracle Synonyms?

Jul 9, 2004

I have a SQL Server database which has one user (UserA) which owns some tables. I've added an additional user (UserB) to the database such that it has access to the tables owned by UserA. What is happening is that when I log on as UserB I have to fully qualify table names and fields in my SQL statements when I deal with tables owned by UserA. Is there a way make the tables accessible without specifying the owner? In Oracle you could create a public synonym for the table eg. <table_name>. Wherever that synonym is referenced the DBMS would know thats its refering to UserA.<table_name>. Is such functionality available in SQL Server? Thanks.

View 2 Replies View Related

Oracle's ROWNUM Equivalent In SQL Server

Sep 2, 2004

Hi,

Can any one tell me is there anything in SQL Server thats equivalent
to Oracle's ROWNUM.

Note that the Identity Property or TOP n will not solve my problem.

I want to asign a sequence no. to each row when its being fetched.

For example if in the emp table there are 2000 rows and I write
the following query in Oracle ,

SELECT rownum , empno, empname FROM emp Where rownum < =3

I get the result like this

Rownum----Empno--------------Empname
------------------------------------------
1-----------2345---------------ABCD
2-----------3334---------------EFGH
3-----------4484---------------IJKL


I know I can limit the output rows in SQL Server by using TOP n. But
I also want to generate a sequence no. The identity property of SQL Server
will not be usefull here because my actaul WHERE clause will be more
complex like WHERE resigndate = '01-jan-2004'

Thanks

Asim Naveed

3

View 2 Replies View Related

ORACLE BEFORE INSERT EQUIVALENT IN SQL SERVER

Jul 20, 2005

I am having trouble creating an INSTEAD OF trigger in SQL Server toreplicate a BEFORE UPDATE trigger from ORACLE.Here is a sample of the ORACLE BEFORE UPDATE trigger:CREATE TRIGGER myTRIGGER ON MYTABLEbegin:new.DT := SYSDATE;if :new.NM is NULL then:new.NM := USER;end if;end myTRIGGER;It seems as though I have to jump through hoops in SQL Server AND Icannot come up with correct results.Here is a snippet from SQL SERVER (this is what I figured I needed todo after reading various articles,questions):CREATE TRIGGER myTRIGGER on THETABLEINSTEAD OF UPDATEASSELECT * INTO #MYTABLE FROM INSERTEDUPDATE #MYTABLE SET DT = GETDATE()UPDATE #MYTABLE SET NM = USER WHERE NM IS NULLUPDATE THETABLESETDT = (SELECT DT FROM #MYTABLE),NM = (SELECT NM FROM #MYTABLE)WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)Can anyone please shed some light on this? Thanks in advance.

View 4 Replies View Related

SQL Server Equivalent For The Oracle Join Query

Nov 30, 2004

Hi,

Kindly give the SQL Server equivalent for the below Oracle query :

select *from t1, t2, t3 where
t1.t1col1 (+) = t2.t2col1 and
t2.t2col1 (+) = t3.t3col1

Thanks,
Sam

View 14 Replies View Related

Oracle Translate Function Equivalent In SQL Server

May 5, 2006

Hi

I want to know the equivalent of the Oracle translate function in SQL Server.

eg : select translate('entertain', 'et', 'ab') from dual.

I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'.

Please let me know if there is some other equivalent function in SQL Server

thanks.

View 14 Replies View Related

There Is No Direct Equivalent To Oracle's Rownum Or Row Id In SQL Server

Sep 23, 2006

hi,

There is no direct equivalent to Oracle's rownum or row id in SQL Server

there is any possiblity are alternate thing is exist then please reply



regards

santosh

View 10 Replies View Related

Oracle Translate Function Equivalent In SQL Server

May 5, 2006

Hi



I want to know the equivalent of the Oracle translate function in SQL Server.



eg : select translate('entertain', 'et', 'ab') from dual.



I tried the SQL Server Replace function , but it replaces only
one character or a sequence of character and not each occurrence of
each of the specified characters given in the second argument i.e 'et'.



Please let me know if there is some other equivalent function in SQL Server



thanks.

View 4 Replies View Related

Sql Server Time Format In HH:mm AM, Oracle HH:mm AM Equivalent In SQL Sever

Jul 31, 2006

Hi All,

I need time in the formate HH:mm AM (ex: 06:25 AM, 08:30 PM)

I have tryed with the following query
"SELECT right(CONVERT( varchar, getDate(), 100),7)"
it is giving time like 6:25 AM, But I need in the format 06:25 AM.

I need exaclty equivalent of following oracle query. TO_CHAR(sysdate,'HH:mm AM')

Please reply me.

Thanks & Regards
Dutt

View 4 Replies View Related

How To Convert Pl/sql Code Of Oracle To Something Equivalent Which Works On Sql Server 7.0?

Dec 21, 2000

i had worked on oracle 8i and i am planning to work on sql server 2000,i am requested by a company to help in converting there pl/sql code of oracle 8.0 to something equivalent which works on sql server 7.0 as they want to have similar code on both..i had not worked on sql server 7.0 ,but as pl/sql code works only on oracle stuff..so could kindly anyone guide me in this as to whether there is any product which coverts pl/code (the existing pl/code runs into thousands of line) automatically..i will be very grateful if anyone can enlighten me with such a product(software) or script.. along with its information and site address..any resources and any guidance as to how to go about about this conversion will be very invaluable..hope to hear soon from you guys...early response....will be appreciated..

with regards,

vijay.

sql server 7.0 on winnt
pl/sql code on oracle 8.0

View 2 Replies View Related

SQL Server Equivalent For Oracle System Tables/Views

Sep 12, 2006

We are in the process of supporting two databases (Oracle 10g, SQL Server 2005) for our application.

I want to know what is the equivalent Tables/Views in SQL Server for the Oracle System tables dba_tab_comments, dba_tab_cols

Thanks in advance

View 4 Replies View Related

Ms Sql Equivalent Of This Oracle

Feb 4, 2006

Hi.I'm a casual sql user. I have found a situation where I need to convert anoracle statement to tsql, one I can just fire off in any sql tool against anms sql server database.I studied the exists statement and I think I understand it somewhat, however Iwas not sure how to get it quite right. If you have an idea and a minute ortwo I'd appreciate any insight or tutorial.insert into authorization (program, optiontitle, usergroup, authorizationid)select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextvalfrom allgroups where exists (select * from authorizationwhere authorization.USERGROUP = allgroups.USERGROUP andauthorization.optiontitle = 'READ' and authorization.program = 'EVERYWHERE')I believe that because in my data, three values of usergroup from allgroupsreturn true from the exists, that this is supposed to insert three rows intoauthorization.But I can't figure out what to do about the authorization.nextval.. I triedvarious max(authorization)+1etc but nothing seemed to compile/workthanksJeff Kish

View 6 Replies View Related

SQL Equivalent Of Oracle SET TIMING ON ?

Dec 18, 2001

Hi Guys,

Anyone knows if there is a SQL equivalent of Oracle SET TIMING ON to give the excution time in milli/centi Seconds?

Cheers,

xiaobing

View 1 Replies View Related

Oracle Rowid Equivalent In MS SQL

Jul 1, 2004

Hi all,

I'ld like to get the last record inserted into my DB. In oracle I use:
select * from <tabella>
where rowid = (select max(rowid) from <tabella>.

It is an equivalent of rowid in MS SQL?
Thanx

TT

View 2 Replies View Related

Any Equivalent For NEXT_DAY Fn Of Oracle

Nov 8, 2004

Haii Friends,
Is there any equivalent for Oracle's NEXT_DAY function in sqlserver.I need it for the following query...

SELECT NEXT_DAY( SYSDATE ,'THURSDAY') FROM DUAL;

ur Help in this regard is really appreciated....

Regards,
Vicky

View 1 Replies View Related

Equivalent For A Oracle Query

May 7, 2004

I need to know the SQL Server equivalent for the below Oracle query :

select t.* from table(cast(c1.table1 as create_type_of_table))

The above query will be used in PL/SQLs in ORACLE.

What is the SQL Server equivalent for the above query ?

View 2 Replies View Related

SQLServer Equivalent For Oracle Query.

Nov 30, 2004

Hi,

Could anyone tell me what is the MSSQLServer equivalent of the following Oracle query?.

SELECT v1.cat, v1.gnr, TA1.desccde, v1.type, v1.cde
FROM view1 v1, table1 TA,
table1 TA1, table1 TA2, table1 TA3
WHERE
TA.txtcde (+) = TRIM(v1.cat)
ANDTA1.txtcde (+) = TRIM(v1.gnr)
ANDTA2.txtcde (+) = v1.cde
ANDTA3.txtcde (+) = TRIM(v1.type)

Thanks,
Gopi.
Follow your DREAMS...

View 3 Replies View Related

Equivalent In Oracle, Needed Urgently

Sep 20, 2006

CREATE TABLE [omx].[UserAuthorization]( [MemberAuthorizationRecordIdentifier] [varchar](50) NOT NULL, CONSTRAINT [PK_UserAuthorization] PRIMARY KEY CLUSTERED( [MemberAuthorizationRecordIdentifier] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

View 4 Replies View Related

Equivalent Oracle Rownum In SQLServer

Jul 23, 2005

Hello,I would like to know if the equivalent Oracle rownum exist inSQLServer. Here is a sample SQL code to explain what I want to do :selectjobs.name,jobs.job_id,jobs.description,hist.message,hist.step_name,hist.step_id,hist.run_status,hist.run_date,hist.run_time,hist.run_durationfrommsdb.dbo.sysjobs jobs,msdb.dbo.sysjobhistory histwherejobs.job_id=hist.job_idand hist.job_id='E71CCB97-81C3-46E2-83FA-BFFCB66B47F8'order byrun_date, run_timeI just want the first or second row returned by this query. In Oracle Ican simply add rownum=1 or rownum=2 in the where clause to obtain thedesired answer. I don't know how to do in SQLServer.Thank in advance,Pierig.

View 4 Replies View Related

What Is The SQLCMD Equivalent Of Oracle Set Echo On

Mar 11, 2008

I know you can start SQLCMD with the -e option to have the SQL code echoed in the output when running. Is there a way to turn this on or off within a script file?
Example:
File1.sql = select 'hi' as HI
go
File2.sql = select 'by' as BY_
go

Runfile.sql =
set (like echo on, -e sign on parm) ? what command
:r File1.sql
set (like echo off, -e sign on parm not used) ? what command
:r File2.sql

want to get output like
select 'hi' as HI (echoed the select statement)
HI
--
hi
(1 rows affected)
BY_ ( did not echo the select statment)
---
by

View 1 Replies View Related

Equivalent Of UTL_FILE Of Oracle In MSSQL

Apr 25, 2008

Hi all,

I am currently migrating my database from oracle to MSSQL. My problem is that i have encountered the UTL_FILE function in Oracle and i am not finding its equivalent for MSSQL.

Can anyone help me with this plz? Is there any other way of reading and writting OS files in MSSQL?

Regards
Dhiraj
Software Engineer.

View 3 Replies View Related

Is There An Equivalent Table Copy Command In Ms Sql A La Oracle?

Apr 15, 2004

is there a command in ms sql server 2000 equivalent to this oracle table copy command?

create table myTable_bak as select * from myTable;

View 1 Replies View Related

Loading Sql Files From A Master File Oracle Equivalent

May 19, 2006

Hi.I need to give my customer an sql file that they can run in query analyzer.All the stuff they need to run is in a set of existing files.I'd like to just tell them to load this file (this is oracle syntax):@file1.sql@file2.sql@file3.sqlis there some way of calling these files (that are in the same dir) from amaster sql file?ThanksJeff Kish

View 2 Replies View Related

SQL Link Server To Oracle NOT Generating Parameters Using Procedure

Nov 29, 2007

I have created a link server in SQL Server 2005 to connect to Oracle 7.3. The driver in link server is MS-OLEDB. I have created a dynamic procedure in SQL Server 2005 using OpenQuery method to connect to Oracle. When I execute this procedure in SQL this gives me the results I want from Orcale.

In SSRS 2005, I am calling this procedure with the schema name.When I do Refresh,this should automatically list me the Parameters I am using in procedure. It is NOT listing me the AUTOMATIC generation of parameters.

Please help..

Deepak

View 3 Replies View Related

Lookup Error With Oracle Parameters

Jan 31, 2008

I've been searching around for a while now and slowly been making progress but I've finally hit a road block and I'm wondering if anyone else has ever gotten this to work. I'm using SS SP2 and the Microsoft OLE DB Provider for Oracle.

I have a lookup task in the data flow. The lookup table is in Oracle and it works fine as long as I don't check the "Enable Memory Restriction" box on the Advanced tab. As soon as that box is checked, the task will throw an error when I try to run it. I need to check it though to get to the Modify SQL Statement.
Here is what I do:
Create new Lookup task
Set the Oracle OLE DB connection
Use the following SQL for the reference table source:
SELECT COST_CENTER_ID, COST_CENTER_NB, start_dt, end_dt,
decode(SIGN(TO_NUMBER(TO_CHAR(START_DT,'MM'))-9),-1, TO_CHAR(START_DT,'YYYY'),
0, TO_CHAR(START_DT,'YYYY'),
1, TO_CHAR(START_DT + 365,'YYYY')
) START_FY,
decode(SIGN(TO_NUMBER(TO_CHAR(END_DT,'MM'))-9), NULL,
decode(SIGN(TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-9),
-1, TO_CHAR(SYSDATE,'YYYY'),
0, TO_CHAR(SYSDATE,'YYYY'),
1, TO_CHAR(SYSDATE + 365,'YYYY')
),
-1, TO_CHAR(END_DT,'YYYY'),
0, TO_CHAR(END_DT,'YYYY'),
1, TO_CHAR(END_DT + 365,'YYYY')
) END_FY
FROM DIM_COST_CENTER

Then I go to the columns page and connect 1 field from the input column to the lookup column. Then click ok and it runs fine.
However, now I go to the advanced page and click the Enable Memory Restriction (at this point is where the problem occurs). As soon as the memory restriction is checked, the thing throws errors:
[Lookup [4732]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E14 Description: "ORA-00933: SQL command not properly ended ".
Then if I go in and Modify the SQL Statement into the Oracle syntax by removing the word AS and the [ ]'s it will get a new error:
select * from
(SELECT COST_CENTER_ID, COST_CENTER_NB, start_dt, end_dt,
decode(SIGN(TO_NUMBER(TO_CHAR(START_DT,'MM'))-9),-1, TO_CHAR(START_DT,'YYYY'),
0, TO_CHAR(START_DT,'YYYY'),
1, TO_CHAR(START_DT + 365,'YYYY')
) START_FY,
decode(SIGN(TO_NUMBER(TO_CHAR(END_DT,'MM'))-9), NULL,
decode(SIGN(TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-9),
-1, TO_CHAR(SYSDATE,'YYYY'),
0, TO_CHAR(SYSDATE,'YYYY'),
1, TO_CHAR(SYSDATE + 365,'YYYY')
),
-1, TO_CHAR(END_DT,'YYYY'),
0, TO_CHAR(END_DT,'YYYY'),
1, TO_CHAR(END_DT + 365,'YYYY')
) END_FY
FROM DIM_COST_CENTER) refTable
where refTable.COST_CENTER_NB = ?


Now when running I get the error:

[Lookup [4732]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E5D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E5D Description: "Parameter name is unrecognized.".
Followed by:
[Lookup [4732]] Error: OLE DB error occurred while binding parameters. Check SQLCommand and SqlCommandParam properties.

This is where I get stuck. I've gone into the XML and looked through everything and it all seems to match up in terms of variables lineage ID's and such, but I can't see any place to set the parameter name, which should be 0 since it is OLE DB. When I click the Enable Memory Restriction, the only difference I can notice in the XML is that the cachetype line changes from 0 to 2.

<property id="4738" name="CacheType" dataType="System.Int32" state="default" isArray="false" description="Specifies the cache type of the lookup table." typeConverter="CacheType" UITypeEditor="" containsID="false" expressionType="None">2</property>


Has anyone ever got parameters to work with Oracle and a lookup? Any work arounds? I have used a Merge Join with Conditional Split successfully, but I have about 5 other lookups that have to be done and it will be a killer and lots of work to try and re-sort for each merge join and conditional splits for each of them. Looking for any help with making the lookup work or some nicer work arounds.

Thanks.

View 2 Replies View Related

Using SQL Results As Parameters For Oracle Query Through SSIS

Apr 20, 2006

Is there a way to use the results of a query as parameters of a WHERE statement in Oracle?

I have a list in a SQL table that I would like to use as criteria for a query through Oracle? Basically I have two data sources; one Oracle and the other SQL, I'm currently querying the SQL one, then using a Merge Join object in SSIS to combine the data. The SQL query has roughly 2000 rows which is fine, however the Oracle one had several million... I've tried to limit the oracle one as much as I can however its still returning far too much data...

If anyone has any suggestions on how to do this I'd greatly appreciate it. I've looked into Linked servers, however this isn't an option with my set up due to account restictions on the Oracle server. Thanks and let me know if you require any other details

View 3 Replies View Related

Parameters For OLEDB Oracle Source Query

Jul 5, 2006

Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)


I am getting the above error while opening the parameter box at OLEDB source for Oracle using SQL command option at Data Access Mode?? Can you any one please help me in this regard and trouble shoot this problem..

View 8 Replies View Related

Executing Oracle Stored Procedure With Output Parameters Using ADO.NET Connection

Apr 11, 2007

I am a bit confused by an issue that I am having with executing an Oracle stored procedure (with an output parameter) using an ADO.NET connection object. I am able to get this working using an OLEDB connection, but I have no idea why the ADO.NET connection doesn't work. (Bug, by design, or my ignorance?) Actually, I can even get this to work if I use the .NET Providers for OLE DBMicrosoft OLE DB Provider for ORACLE if we set the connectionType to ADO.NET. This is the error that I am receiving:



[Execute SQL Task] Error: Executing the query "pkg_utility_read.test_out_var " failed with the following error: "The OracleParameterCollection only accepts non-null OracleParameter type objects, not SqlParameter objects.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.



It is also worth mentioning that the ORACLE stored procedure has an out parameter with a NUMBER datatype which I think maps to the ADO.NET Int32 datatype. I guess OLE DB datatypes are more closely mapped to ORACLE datatypes. In OLE DB you can set the parameter to double and the ORACLE stored procedure to NUMBER and it works.



Any help on this would be most appriciated.

View 3 Replies View Related

Parameters In Data Flow Task With Oracle Database Source

Jul 9, 2006

In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.

Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.

I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.

Am I missing an obvious workaround?

View 7 Replies View Related







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