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'
What is the function in SQL that works like DECODE in Oracle?"Thanks,N
View 10 Replies View RelatedI 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 11 Replies View RelatedIn 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 ?
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 1 Replies View RelatedHI,
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.
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?
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
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.
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
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
I need to know what is the DECODE statement called in SQLSERVER?
Can somebody provide me simple decode statement for sqlserver.
Thanks,
Ranjan
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...
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
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,
how to execute this stored procedure to decode a varchar hexadecimal string? My SQL syntax stills have faded with the sands of time... I want to do a select * from the users table and decode the Password field (but not update) in the process.
CREATE PROCEDURE spPasswordDecode (@hex varchar (100), @passwordtext varchar (100) output)
AS
declare @n int,
@len int,
@str nvarchar(500),
@output varchar(100)
[code]....
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...
ali
My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.
I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?
Hi,
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
Thanks.
Hi,
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
Thanks.
Hi,
When I am trying to access SQL Server 2000 database from another machine i got this error
Server: MSg 17, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied
but I could access the database on same server and in that server i could access other databases in different server.
Hi All,
View 14 Replies View RelatedHi,
View 36 Replies View RelatedWhen I proposed start to use SQL Server 2005 for new VS 2005 web sites, one of my co-workers responded that
we will update the old SQL Server 2000 databases to SQL Server 2005 when we are ready to use 2005 SQL Server.
Questions:
1. Any expected problems to upgrade old 2000 databases to new 2005 SQL Server?
2. I have installed both 2005/Management Studio Express and 2000/Enterprise Manager in my PC. Any expected
problems when running both 2000 and 2005 SQL Server at the same database server?
3. What is the best configuration for running SQL Server 2005 when we have old 2000 databases? Upgade or not upgrade?
TIA,
Jeffrey
I am getteing
need help
Query analyzer error Unable to connect server local Msg17, level 16,state 1
ODBC SQL server driver [DBNETLIB]SQL server does not exist
Hi,
I am having a problem connecting my .net applications from the application server to the database server. When I run the application from my windows xp (sp2) box it works fine. When I try to connect via SQL Management Studio to the database server from the application server I get the same error.
Here is the error:
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Here is the Environment:
App Server:
Windows Server 2003 Standard Edition
Inside Company's Firewall/ Network
Database Server:
Windows Server 2000 Advanced Edition
SQL Server 2000 SP4
Remote Connections to the Server is checked
Enable Protocols: Named Pipes & TCP/IP
TCP/IP Port: 1402 (I don't know why it isn't the default of 1433)
The db server is sitting out side the Company's firewall (don't ask me why). I can access it fine from inside the firewall on my XP box but not from windows server 2003. There is a web server outside the our network that also connects to the db server with no problem and that is running Windows Server 2003 Web Edition.
I can ping the db server from the app server using the IP address.
I tried using the IP address and the port 1402 in my connection string but that didn't work from any machine (XP and Server).
I imagine the issue is somehow related to the company's firewall but why would it only block Windows Server 2003 and not XP?
What do I tell the network admin to change?
Any help would be appreciated.
Thanks,
Oran
if you can restore a database to Server B using Server A as the service. Meaning we would issue the command on Server A but somehow point to Server B as where we want the restore to happen.
The backup file would be in a location independent of both servers.
Dear all,On Win2000 server with SP3, I am trying to access a SQL Server 7.0database, "TestDB", from VB6 via a SQL Server ODBC system DSN using ADO2.7. In SQL Server Enterprise Manager, there is a login named "Tester".In its property window, NO "Server Roles" was assigned but its"Database Access" was set to "TestDB". This login was also made as theuser of "TestDB" with "public", "db_datareader" and "db_datawriter"selected as its "Database role membership". All the tables I am tryingto access in "TestDB" were created under "Tester".My code is like:Set conn = New ADODB.Connectionconn.Open "DSN=TestDSN;UID=Tester;PWD=test"Set cmd = New ADODB.Commandcmd.ActiveConnection = conncmd.CommandText = SQLset rs = cmd.Execute()If I set the SQL to something like "SELECT * FROM tbl_test", I alwaysget an error of "-2147217865" saying "[Microsoft][ODBC SQL ServerDriver][SQL Server] Invalid object name tbl_test". If I set the SQL to"SELECT * FROM Tester.tbl_test", everything runs properly. Could anyoneplease kindly advise why the first SQL is not working? Or in otherwords, why must I prefix the table name with its owner while the DBconnection is already made under that owner name? Thanks in advance.Tracy
View 10 Replies View RelatedWhen I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;
Performing operation...
- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;
InnerException-->An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).
How do I solve this problem?
My site works fine in VWD2008 express, but I get this error when I try to use it on my live website.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
According to this article: http://support.microsoft.com/kb/914277 I am supposed to:
1.
Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
Ok, there is no such program in this folder. The only thing in there is "SQL Server Error and Usage Reporting"...
The other thing I am greatly concerned with is this: All is want is for my webpages to be able to access my database for user authentication. I DO NOT want to grant the internet rights to remote connect to my database.
Hi All,
Please some one help me...
I have to insert a csv into one table in sql server. But the problem is the file is in one server and SQL SERVER 2005 is in other server..
how do i insert the file....
please help me.....
Hi Guys,
I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well. (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!)
Everything went fine except that nothing seemed to be installed. I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing.
A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005.
So after a number of retries in which the install program kept saying that there was nothing to install, I selected every option under Advanced in the intall process. This resulted in stuff being installed and I now have SQL Server 2005 running on my machine, but no SQL Server Manager icon.
My questions are:
Where can I find the SQL Server Manager executeable?
Has anyone installed SQL Server Express, SQL Server Mangement Studio Express, SQL Server 2005 and SQL Server Management Studio on a single machine successfully? If so, what order did you install them in? (I'm planning on uninstalling everything SXQL Server related and starting again.)
Thanks very much for your help.
Regards
Gary
Hi,Is it possible to install on a Windows server 2003,SQL SERVER 2000 in the folowing configuration :SQL server 7.0 as a default InstanceandSQL server 2000 as a named instance.Thanks for your answer.
View 1 Replies View Related