Equallent To Decode In Oracle?
Hi, Here is my oracle statement. How can I change it to be compatible with SQL Server?
update propertytable set visible =decode(propertyid, 1,0, 2,0, 3,1, 5,1, 6,1, 7,0, 9,1, 10,1, 11,0, 14,1, 30,1, 38,1, 60,0, 232,0, 233,0, 415,1, 605,0) where parentid between 2000006001 and 2000006020
Thanks...
View Complete Forum Thread with Replies
Related Forum Messages:
How To Translate Oracle Decode
Hi, How to translate oracle Decode without changed code I mean: I have one application and instead to change all decode to case when I would like just replace decode for dbo.decode, so I Wrote this function select dbo.fnDecode( 1 , 2 , 3 ) first parameter always int, and the others parameters could be char, int or float. I would like to test first value and return second or third value -> sql_variant for all parameters, ok but I can use + or - I can't do this select dbo.fnDecode( 1 , 2 , 3 ) + dbo.fnDecode( 1 , 2 , 3 ) If I put cast ok, but I is there another way, overload this call? With clr doesn't work, because Sql Server doesn't accept function overload calls from C# Any ideia???????????????????? cheers,
View Replies !
DECODE?
I have to run a query to give a column a value based on a time range. Can Iuse DECODE?select decode(trans_date, trans_date>='01-Jul-2002' andtrans_date<='30-Jun-2003','Fiscal2002', .....) as fiscal,from. . .where. . .
View Replies !
DECODE In MS SQL
In Oracle we have expression SELECT ..... DECODE (T.LANGUAGE, 'E', T.DESC_ENG, T.DESC) FROM TABLE T if acts like that : if T.LANGUAGE = 'E' it returns T.DESC_ENG. If it is different - T.DESC. Is there some way to make the same in MS SQL Server ?
View Replies !
Decode In SQL Server
Hi I have oracle statement and I want to translate it in SQL Server: select DECODE(count(bid_Vendor), 1, NULL, COUNT(BID_VENDOR)) from bid_total I tried it this way: Select case(count(bid_vendor) when 1 then null else count(bid_vendor)end) as cs from bid_total It gave me error i.e. Incorrect syntax near the keyword 'when'
View Replies !
DECODE Function
Is there anyone know a similar function in Sql Server 7 work as the same way that 'decode' function in Oracle does? Thanks is advanced! -Christine
View Replies !
Decode Function
HI, Is there any way to replicate the decode function in SQL Server 7.0? I have a problem where I have to write a query such that if the value in a column is '0' , I need to output it as 'No' and if it is '1', I need to output it as 'Yes'. An example of my problem is as follows: select col_1+","+(value 'No' if col_2='0' and 'Yes' if col_2='1')+ ","+col_3+........... I appreciate your help in advance. thanks, Sravan.
View Replies !
Decode Funktion
Hi! I have a question about the "Decode" funktion. is it only avaiable in Oracle? The reason for my question is that I need to SELECT a colum in a table based on a int value. So if the uservalue is lower then that take that colum, lower then that take colum...etc. Can I use decode? or/and is there better funktion (I'm using MS SQL 2005) Thanks in advance Mark
View Replies !
GROUP BY On DECODE
Hi,I have two questions, firstly.. I am hoping there is a way to GROUP BY on a concatenated output like the following decode etc.. SELECT DECODE(PRIM_REASN,'','Reasons not entered',PRIM_REASN) || ' ' ||SECDY_REASN || ' ' || TERT_REASN) Downtime_Reasons, EVENT_DUR/60 Minutes FROM DOWNTIME_WEB WHERE DOWNTIME_WEB.TIMESTAMP>= (SELECT PRODN_SHIFT.START_DATIME FROM PRODN_SHIFT WHERE PRODN_SHIFT.START_DATIME <=sysdate AND PRODN_SHIFT.END_DATIME > sysdate) AND TRIM(DOWNTIME_WEB.STATUS_TEXT) = 'STOPPED' AND TRIM(DOWNTIME_WEB.WORK_CTR_NAME) = 'PACK2' ORDER BY DOWNTIME_WEB.EVENT_DUR I have tried to put the DECODE etc into the GROUP BY statement but I get an error - " not a valid GROUP BY clause" Secondly, is there a way of modifying value in field A based on the value in field B Erroneous example follows - in an effort to explain what I want to do (from above): DECODE(PRIM_REASN,'',(DECODE(EVENT_DUR,<200,"Low",>200,"High"),PRIM_REASN) ie:If PRIM_REASN is null I want EVENT_DUR to be tested to determine the new value for PRIM_REASN. Except that tthe above won't work and I don't know what the right function/argument should be.. This is my first foray into SQL server, I am trying to migrate some access queries to work with web services and hence Xcelsius. Cheers.
View Replies !
Alternate To Decode
hi i have a view in oracle. in that i am using decode function. same query i want to write it in sqlserver. what it is the alternate to decode. this is a cross tab query SELECT code, SUM(DECODE(field1, 4, Present_Value, 0)) AS c1, SUM(DECODE(field1, 5, Present_Value, 0)) AS c2, SUM(DECODE(field1, 6, Present_Value, 0)) AS c3,SUM(DECODE(field1, 9, Present_Value, 0)) AS c4 FROM (SELECT field1,Code, Present_Value FROM table1) DERIVEDTBL GROUP BY code thanks suji
View Replies !
Case? Decode?
Hello, I need to do the following: Lets say we have the following table. A B C 1 XYZ XYZ 100 Now if the value of a is "100" then i need to do a join and get a value from another table, else I put the value of b a "test" I am trying to do this using case like this select case (when a = 100 then (select col from ....where newtable.id = a.id else "test" end) as xyz from a,b,other tables. But the problem is that the subquery returns more than one row..(it should return one row) How do I do this?
View Replies !
Urgent!!!!!!!! Decode Function In Sql
how can I write decode function in sql such that i have a feild in my table called "file type" based on its content i want to pass a value to sql datasource such that if file type = doc i want to pass "~/Images/ico_filetype_10.gif" an so on
View Replies !
How To Decode In SQL Server 2005
I am a novice to SQL Server, so this is probably a really easy problemto fix. I'm translating an Oracle query and need to change the'decode' to something compatible. Everything I've read points me tousing 'case' but no matter how I write it I can't get it to work andget a syntax error. Suggestions?select SYST CTR, isnull(substring(CD_A, 1, 3), ' ') RESCODE, DES DESCRIPTION, decode (substring(CD_A, 1, 3), CODE,PRICE,0) UNIT_PRICE, count (distinct OR_NO) QTYW
View Replies !
NULLIF Or CASE - Decode
Hi,-- SQL Stmt 1SELECT * FROM Table1WHERE Field1 = 123AND Field2 = 234AND Field3 = 345AND Field4 = 456AND Field5 = 567AND Field6 = 678-- SQL Stmt 2IF @@ROWCOUNT = 0SELECT * FROM Table1WHERE Field1 = 123AND Field2 = 234AND Field3 = 345AND Field4 = 456AND Field5 = 567I want to use a single SQL Stmt instead of 2 SQL Stmts.I am not looking for "(Field1 = 123 ..... and Field6 = 678) or (field1 = 123... and field5 = 567)"I know this can be achieved in Oracle with Decode.I was looking for a similar solution on SQL Server.May be possible with NULLIF and or Case.I am attaching script for creating a tableCREATE TABLE Table1(Field1 INT,Field2 INT,Field3 INT,Field4 INT,Field5 INT,Field6 INT)-- Hareesh
View Replies !
Decode Base64 To Image!
Hi, I have a xml string which is consist of some images encoded in base64; I have to extract these images in a stored procedure and save them in some tables. The problem is that I can't decode this base64s to images. Is there a way to do it? (I use SQL Server 2005 Enterprise) Thanx
View Replies !
Oracle Connection Fail With Microsoft OLEDB Provider For Oracle MSDAORA.1
Hello, On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider. I use one or the other according to my needs. In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev. Almost everything works except Microsoft OLEDB provider for Oracle. ssis packages on the test machine will return an error Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009. Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. [Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.". I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine. Does anyone could point me to the right direction to solve this issue? Thanks, Philippe
View Replies !
Output Column Has A Precision That Is Not Valid (loading From Oracle Using OraOLEDB.Oracle.1)
Hi! I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error: TITLE: Microsoft Visual Studio ------------------------------ Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38. ------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ BUTTONS: OK ------------------------------ For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for: 1) Union I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL" Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above. 2) SQL command from variable The select basically looks like this: "SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad" Again, even if I cast all columns (like in the union), I still get the same error. Any help would be greatly appreciated. Thanks!
View Replies !
Oracle Publication Error:The Permissions Associated With The Administrator Login For Oracle Publisher 'test1' Are Not Sufficient
Hi, I am trying to make an oracle publiching from sql server 2005 enterprise final release, i installed the oracle client 10.2 (10g) on the same server where sql server already installed, i made different connection to oracle database instance and it was ok. from sql server : right click on publication -New oracle publication-Next-Add Oracle Publisher-Add button-Add Oracle Publisher-i entered server insttance test1 and their users and passwords--connect ---> the oracle publisher is displayed in the list of publisher but when press ok i got the following error : TITLE: Distributor Properties ------------------------------ An error occurred applying the changes to the Distributor. For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: SQL Server could not enable 'test1' as a Publisher. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The permissions associated with the administrator login for Oracle publisher 'test1' are not sufficient. Changed database context to 'master'. (Microsoft SQL Server, Error: 21684) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21684&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ Any idea about this error ? Thanks Tarek Ghazali SQL Server MVP.
View Replies !
Inserting Into Oracle Table That Has DATE_HIGH As A Partition And Need Oracle Sequence Used
Hi Everyone, I've been searching for a solution for this for a week-ish, so I thought I would post my quesiton directly. Here is my scenario.. Source: MS SQL Server Destination: Oracle 10g The destination table has a partition set on a column called "DATE_HIGH". How do I populate this date high column in my package? Currently I just have a source object, and a destination object, but I'm unclear how to populate this field in the destination. I've read one blog that states "use OLE DB Command" - but that isn't enough information for me to implement - Can someone be more specific in these steps? Here is an example of what my newb-ness needs to understand OLE DB Source (Select * from Table) ---> OLE DB Command (What query goes here?) --> OLE DB Destination. Second part of my question: There is a second column called "ROW_NUM" and there is an Oracle Sequence provided to me... What objects do I need (Source, Destination, OLE DB Command etc...) and how do I call this sequence to populate on the fly as I'm loading data from my source? If these are simple questions - my appologies, I am new to the product. Best Regards, Steve Collins
View Replies !
Oracle Connectivity Patch For Windows 64 Bit SSIS, Oracle Client 10.0.2
Hi, I see many people had problems with connecting to Oracle while creating package on SSIS 2005 on the machine running Windows 2003 Server 64 bit. I have the same problem, but can anyone tell me the exact patch number from Oracle to fix this issue for Oracle Client v10.0.2 We need to connect to Oracle 10g when Oracle Client 10.0.2 installed on Windows 2003 64 bit machine. I know about copying Visual Studio to another folder than default for 32 bit programs which is Program Files(x86), but has anyone got "cleaner" solution with official patch from Oracle for 10g. Regards Bakhodir Makhamadov
View Replies !
Unable To Connect To Oracle Using Microsoft OLEDB Provider For Oracle
Hi everybody, I have designed a DTS package which will migrate a view from Sqlserver 2000 to Oracle.My package is using Microsoft OLEDB provider for Oracle driver for connecting to oracle.Im able to execute this package on the my system ie on the system where sqlclient is installed(Oracle client is also installed on my machine) .But when im doing it on the server im not able to do it.The Connection to Oracle Fails. I wanted to know in order to connect to Oracle from the server,is it necessary that Oracle has to be installed on the server?.If yes, is it enough if i install oracle client on the server or Oracle Server version has to be installed on Server. Please suggest me wht should i do know? Thanks in advance Regards Arvind L
View Replies !
Trouble With: Linked Server To Oracle Using OraOLEDB.ORacle Provider
Hi-- I am running SQL Server 2005 on Win2k3: Microsoft SQL Server Management Studio 9.00.2047.00 Microsoft Analysis Services Client Tools 2005.090.2047.00 Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447) Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 6.0.3790.1830 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790 I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory. I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error: (i replaced the real server name with "someServer".) Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer". This is how I set up my Linked server: Provider: "Oracle Provider for OLE DB" Product Name: SomeServer Data Source: SomeServer Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword" The query I run is: Select * from [Someserver].[schema or database]..[tbl_name] Any help??? What am i missing?
View Replies !
Failed To Process Cub With Oracle Datasource Via Oracle OLE DB Provider
Hi everyone, Could you please help me take a look at the error below? OLE DB or ODBC error: Class not registered. Now I can connect to the Oracle datasource successfully via Oracle OLEDB provider, but failed to process it. I just installed the Oracle Client on my machine, should I installed Oracle OLEDB provider separatly? According to the error above, seems that I should register something.... could you tell me which dll I should re-register..... Thanks^_^ Winnie
View Replies !
New SSRS Install, Works Great Until I Try To Access Oracle Datasource - System.Data.OracleClient Requires Oracle Client Software
re: new SSRS install, works great until I try to access Oracle datasource - System.Data.OracleClient requires Oracle client software From my desktop the report accessing an Oracle 9i db with no problem. I've deployed other reports accesing sql server with no problem. Now I deploy the report and try to access my Oracle source and get: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater On the server in question, I know they've installed the oracle client. I can access the db from sql plus with no problem. As the user that I access ssrs credentials, i remote login and am able to write to oracle home.. C:oracleora10gNETWORKADMIN i've confirmed my database is in tnsnames and it works from sqlplus.
View Replies !
Which OLE DB Driver To Use For Oracle DB, Microsoft's Or Oracle's?
Hi everybody, I am currently working on this datawarehouse project, which is using source data from several Oracle databases. There are two different choices when picking OLE DB driver: Oracle provider for Ole DB Microsoft Ole DB Provider for Oracle Which one is the best? And which one will be developed further on? I've read treads that adresses part of this issue, but they are adressing a specific problem, and not which to choose generally. I've heard that Microsoft is not developing their driver, and you should therefore choose Oracle's. Can anybody confirm that? Thanks, Nils Mortensen
View Replies !
Oracle Error &"ORA-12154&" From An Application Which Never Uses Oracle
My application which ran perfectly well yesterday suddenly stopped working in this morning with following error message. I didn't change anything, at least I believe. Interesting thing is that I don't use any Oracle connection but connects to MS SQL Server 2005 Express version locally. Error messages are different according to the connection string though I don't think it makes difference: ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;SSPI=true" />ErrorMessage from VS2008 ="Unable to get records. Object reference not set to an instance of an object." ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;User Id=handtool_DB;Password=mypassword" />ErrorMessage from VS2008 = "Unable to get records. Unable to connect to database. ORA-12154: TNS:could not resolve the connect identifier specified." By the way, the application was generated from IronSpeed 5.1 Enterprise. My development environment is : VS2008, SQL2005 EXPRESS, Windows XP SP2, ASP.NET 2.0 We have actually an Oracle server in the network though. My gut feeling is that something might have changed in my network server during the night but how come it can possibly affect my application running on my local PC. I checked the connection of local SQL Express Server from SQL Server Management Studio and it connected well with UserID=handtoo_DB and showed all tables and stored procedures required for the application. So there is no problem in SQL Server database side. I would welcome and appreciate any input. fudata
View Replies !
Oracle
Has anyone noticed that Oracle can be a little bit difficult to work with at times?? Maybe some compatbility issues as well??
View Replies !
Oracle To SQL
I have an oracle procedure that needs to be converted to ms-sql. lXMLContext := DBMS_XMLQuery.newContext("select * from table"); -- Setup parameters on how XML is to be constructed. DBMS_XMLQuery.useNullAttributeIndicator(lXMLContex t,FALSE); DBMS_XMLQuery.setRaiseNoRowsException(lXMLContext, FALSE); DBMS_XMLQuery.setRaiseException(lXMLContext,TRUE); DBMS_XMLQuery.propagateOriginalException(lXMLConte xt,TRUE); DBMS_XMLQuery.setRowTag(lXMLContext,lWS.wsXMLRecor d.wsViewName); DBMS_XMLQuery.setDateFormat(lXMLContext,lDateMask) ; Any one can help in constructing a similar pattern in ms-SQL or atleast tell me what is all these doing? I am coding this using extended stored procedure in c#. Thanks, Venkat.
View Replies !
DTS And Oracle
I want to import a table called emp(user name scott/tiger) from oracle database to MSSQL database Step 1 login to scott/tiger in oracle Step 2 Create a table called emp create table emp (empno int, empname varchar(20)) Step 3 insert into emp (empno, empname) values(1,'aaa') insert into emp (empno, empname) values(2,'bbb') insert into emp (empno, empname) values(3,'ccc') Step 4 Now I want to import this table to mssql using DTS. I have used Data Transmission task in DTS for source query. I have given "select * from scott.emp" I am able to get the exact result set. I am able to get the same values in MSSQL. Step 5 Now I am creating another user called "test" and give access to "emp" table of "scott" user. Step 6 Now i want to pass on "test" instead of "scott" in the Data Transmission Task.(i.e). "SELECT * FROM test.emp" instead of "scott.test". I want to pass "test" dynamically. how to do this.
View Replies !
Oracle && Ms Sql
My client require e-commerce website and expected to have more than 100,000 products online. All products currently manage on Oracle Application server which is behind the firewall. Initially all products will be uploaded to online ms sql server and then whatever the products get change on oracle should be updated online on ms sql db. We need proposed solution under secure and easy method to keep online products DB according to the oracle system. All this process should be Automatic and Oracle application server send only updated data to MS SQL SERVER because client has more then 100,000 products on their ORACLE Application Server.
View Replies !
Oracle To Oracle Via DTS
Hello all, I've written a simple dts job that uses oracle (8.x) as a source and oracle (8.x) as a destination. I'm using SQL 2000 and Microsoft's oledb provider for oracle as the two connections. I've chosen "Transform Data Task" with the following SQL "SELECT * FROM REPORTER_STATUS WHERE LASTOCCURRENCE > TRUNC(SYSDATE)". As you can see, it's very simple, however it's very very very slow. (averages about 1000 rows per minute). In my column transformations, I've selected many to many versus the one to one. There are no activex scripts or anything along those lines. Just a simple push of the data from one oracle box to the other. The table schemas are identical etc... I've had this problem before with writing to Oracle and I can't imagine that it's really supposed to be this slow. If you need more details, please just let me know. Thank you, Jeremy
View Replies !
Oracle
It is interesting to see the Oracle add to save you money popping up on this page. I saw on CNN today that Oracle had promised California it's solution would save the 14 million. Now that it is implemented it cost them 41 million instead. Several people have gotten canned over it. I guess Oracle forgot to include that in the ad
View Replies !
MS SQL And Oracle?
Hi, have been asked the question by a customer, if the 2 RDBMS )Oracle and MSQ SQL 2000) can run ok on the same server (assuming the specs are adequate). I know MS recommend SQL run by itself, but is there anyone out there that can tell me anything about how this could/would work?? Your help would be invaluable Thanks
View Replies !
Using DTS With Oracle
I am trying to use DTS for doing ETL. My source and destination tables are both in Oracle. My ETL is simple – if the data exists then update it, if not then insert it. In my DTS I am using the data driven query, the error I am getting is “Provider cannot derive parameter information _____ “, the source of the error is Microsoft for OLE DB Provider for Oracle. Has any body out there tried this ? Please help. Thank you in advance.
View Replies !
Oracle To SQL
I need to write a direct pull dts package from an oracle database to a SQL database. What version of sql do I use? PSQL or TSQL for the queries and updates?
View Replies !
DTS And Oracle
I am using DTS sql 7 How can I pass parameters using the lookups in DTS to access oracle database? i know in sql u could do e.g select company_ID from Company where Company_Desc = ? but it won't work with oracle. i have used select company_ID from Company where Company_Desc = '&company_desc' but it did not ask for a parameter.
View Replies !
DTS Job With Oracle
I currently have a DTS package that will transfer tables from an Oracle db to SQL 7. I am using the Oracle ODBC connection and have connected with the owners login and password. When I run the package through DTS, it runs successfully. However, when I try to run it as a scheduled job, it fails each time. I assume that this has something to do with connecting to Oracle. It's hard to figure out because SQL says only that the job failed. Has anyone else run into this issue?
View Replies !
ORACLE And DTS
I to import a large number of tables and data from ORACLE into SQLServer 7. Does anyone have any pointers? (I'm new to this!) NT
View Replies !
Oracle To Sql
Hi All, when i am executing follwing sql task from ssis. follwing delete statment will run on oracle Delete from DW_ADVENTRUREWORKS.EMPLOYEE; Delete from DW_ADVENTUREWORKS.DEPARTMENT; I am getting following error. failed with the following error: "ORA-00911: invalid character". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. give me solution, how can i slove this prob. Thanks, Alpesh
View Replies !
|
TRACKER
