Alternate To Decode
Sep 30, 2006
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 8 Replies
ADVERTISEMENT
Jan 12, 2004
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 10 Replies
View Related
Jul 20, 2005
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 Related
Aug 28, 2001
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 3 Replies
View Related
Jun 4, 2004
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 1 Replies
View Related
Jun 8, 2008
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 1 Replies
View Related
May 14, 2007
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 4 Replies
View Related
Jul 20, 2005
What is the function in SQL that works like DECODE in Oracle?"Thanks,N
View 10 Replies
View Related
Jan 23, 2006
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 6 Replies
View Related
Dec 11, 2007
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 1 Replies
View Related
Mar 27, 2001
I need to know what is the DECODE statement called in SQLSERVER?
Can somebody provide me simple decode statement for sqlserver.
Thanks,
Ranjan
View 1 Replies
View Related
Jun 21, 2004
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 10 Replies
View Related
Mar 6, 2008
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 11 Replies
View Related
Jun 7, 2006
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 5 Replies
View Related
Aug 2, 2006
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 4 Replies
View Related
Nov 20, 2014
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]....
View 4 Replies
View Related
Jan 29, 2008
Hi all!
This is my sql statment that works perfectly
select name,id,case when maths>=35 then 'pass' else 'fail' end as maths,
case when science>=35 then 'pass' else 'fail' end as science,
case when tamil>=35 then 'pass' else 'fail' end as tamil ,
case when english>=35 then 'pass' else 'fail' end as english,
case when social>=35 then 'pass' else 'fail' end as social
from student
I dont want to use case in my querey? Is there any way can i replace
case with something?
Thanks in advance!
View 9 Replies
View Related
Jul 17, 2007
I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.
I want to get 100 rows onward to one particular data ... how can i ?
View 9 Replies
View Related
Aug 6, 2007
I need to implement support for alternate names on the database I’m working on.
When a user looks up: “Antony Bigglesworth” the query would return both: Antony Bigglesworth as well as Tony Bigglesworth, would both exist.
Anyone has a best practice on this or has done this before?
View 8 Replies
View Related
Aug 21, 2007
I've a question regarding SSRS 2005.
Is there an efficient scripting method to update the connection string for ALL reports that reside on a reporting/web server? "(automating the process, rather than having to change the data source for each individual report that resides on that server)".
All suggestions are highly appreciated .
Thanks,
View 3 Replies
View Related
Jul 23, 2005
-- tested schema below ---- create tables --create table tbl_test(serialnumber char(12))gocreate table tbl_test2(serialnumber char(12),exportedflag int)go--insert data --insert into tbl_test2 values ('123456789010',0)insert into tbl_test2 values ('123456789011',0)insert into tbl_test2 values ('123456789012',0)insert into tbl_test2 values ('123456789013',0)insert into tbl_test2 values ('123456789014',0)insert into tbl_test2 values ('123456789015',0)insert into tbl_test2 values ('123456789016',0)insert into tbl_test2 values ('123456789017',0)insert into tbl_test2 values ('123456789018',0)insert into tbl_test2 values ('123456789019',0)insert into tbl_test values ('123456789011')insert into tbl_test values ('123456789012')insert into tbl_test values ('123456789013')insert into tbl_test values ('123456789014')insert into tbl_test values ('123456789015')-- query --Select serialnumber from tbl_test2where serialnumbernot in (select serialnumber from tbl_test) andexportedflag=0This query runs quite fast with only the data above but when bothtables get million plus rows, the query simply bogs down. Is there abetter way to write this query?
View 5 Replies
View Related
Oct 1, 2007
Hi,I have a situation where I am loading data into a stagingtable for multiple data sources. My next step is to pick up therecords from the staging table and compare with the data in thedatabase and based on the certain conditions, decide whether to insertthe data into the database or update an existing record in thedatabase. I have to do this job as an sp and schedule it to run on theserver as per the requirements. I thought that cursors are the onlyoption in this situation. Can anyone suggest if there is any other wayto achieve this in SQL 2005 please.ThanksSeshadri
View 1 Replies
View Related
Jul 27, 2007
I am currently with sql 2k5 and using web assistant to generate some HTML files and email the same automatically to a set of users.
these html files that are generated are processed via a JOB in sql server.
I am in process of replacing of the web assistant procedures.
Is this possible to make this with XQuery of SQL Server 2K5 and convert it into HTML files with Stored Procedure.
Is there any other possible way to do it?
View 1 Replies
View Related
May 21, 2008
Dear All
i want to know some alternate for the below query
"Update STR_RGSTR_DFLT " & _
" Set GST_SVY_FREQ_P = " & iNewSurveyPct & _
" Where dhc_co_c = " & iCo & " and store_i = " & iStore & _
" And RGSTR_PROTO_C not in (0,3,4,8,10,11,12,13)"
in this the values in the where clause 'not in' i.e (0,3,4,8,10,11,12,13) are directly given
but we want some alternate solution.
the entire script is writen in VBScript. these values are already populated in an array like proto_code_ary = {0,3,4,8,10,11,12,13}
shall we use that array directly without hard coding the values in the query?
If any one find a solution please let me know.
Thanks & Regards
S.Manikandan
View 4 Replies
View Related
Oct 25, 2007
In times past I connected my web app to SQL Server by embedding the following in the web.config file: <connectionStrings> <add name="XyzApp_DB" connectionString="Data Source=MyServerSQLExpress;Database=XyzApp;User ID=sa;Password=secret_password"/> </connectionStrings> And it worked fine. But it was pointed out to me that this wasn't a very proper way to do things. So I was advised to set things up with a different connection string: <connectionStrings>
<add name="XyzApp_DB" connectionString="Data
Source=MyServerSQLExpress;Database=XyzApp;User
ID=Xyz_Admin;Password=different_password"/>
</connectionStrings>
To get this to work, I've followed these instructions: A
more secure login than “sa� is required to access your application. In the “connectionStrings� section of
“Web.config� you’ll find a User ID called “Xyz_Admin�. Here are the steps to ensure that your
DB has this login id:
a. Using
the Microsoft SQL Server Management tool, look in the root for the “Security�
folder and open it. Inside that, open
the “Logins� folder.
b. If
“Xyz_Admin� doesn’t exist then right-click on “Logins� and choose “New Login�:
i.
Enter “Xyz_Admin� in the “Login name� textbox.
ii.
Click on “User Mapping� on the left side and in the
‘Map’ column check the box beside “XyzApp�.
iii.
Go back to the “General� page and choose SQL Server
Authentication.
iv.
Specify any secret password you wish.
v.
You can uncheck “Enforce password expiration� if you
wish.
vi.
Specify “XyzApp� as the default database and press
OK.
c.
Open “Databases�, right-click on “XyzApp� and
select “New Query�. Run this query: sp_change_users_login 'update_one',
'xyz_admin', 'xyz_admin'
Now, I was pretty sure that this procedure was working fine but it doesn't seem to work for me today. I know that the client computer I'm using can connect to the DB on the server because I temporarily changed the User Id to "sa" and it connected fine. But when I change it back to "Xyz_Admin" it does not. So I'm thinking that there's something incorrect with the procedure above but I don't know what.Any ideas?Robert W.
View 2 Replies
View Related
Jun 6, 2008
Hi What is the difference between Candidate key , composite key and alternate key. I went through many websites but I didn't get examples. There were only definitions. Can anyone please tell me the site or blogs that elaborate this concept RegardsKaran
View 2 Replies
View Related
Apr 6, 2001
Hi,
What is the alternate for Rowid (in Oracle) in SQL Server ?? Can u tell me ...
urs
VJ
View 2 Replies
View Related
Feb 24, 2000
We just erected a new firewall that only allows PASV FTP. Now, my SQL Server can't call master..xp_cmdshell "ftp ...".
Does anyone know how to make the NT FTP client work in PASV (passive) mode or another FTP client that can be called from xp_cmdshell and works in passive more?
View 1 Replies
View Related
Apr 26, 2004
I need to join a remote table which has 20 million rows to few local tables on my SQL Server. Currently Linked server query is used but it's just hanging for ever..is there any alternative for this?
Thanks.
View 7 Replies
View Related
Mar 19, 1999
Hi I need some help on an architecture issue.
I have a production database with input from 3 locations outstation. I need to bring down this server once in a while for tuning and other administrative stuff. Right now I'm in no position to shut the server down. Is there any way I can do this.
Also how can I have 2 databases and toggle between the 2 seamlessly without effecting the users. Does it have to be a mirror on the devices ? What do the professionals usually do
I have heard about clusters. Is this a solution for my problem and what is a cluster anyway ?
Vijay
View 1 Replies
View Related
May 22, 2008
Hi all,
I AM USING ROW_NUMBER() FUNCTION IN MY QUREY IN SQL 2005 BUT IT IS GIVING ME PROBLEM IN SQL 2000.
SO CAN U TELL ME WHAT IS THE ALTERNATE WAY TO ACHIVE SAME RESULT.
MY QUREY IS:
select row_number() over (order by SalesYTD asc) as rownum, CountryRegionName, FirstName, LastName, SalesYTD, SalesLastYear from Sales.SalesPerson
CAN U TELL ME HOW CAN I REPLACE ROW_NUMBER() FUNCTION.
THANKS IN ADVANCE
-JOHN
View 3 Replies
View Related
Mar 3, 2008
I now have a report thanks to help on this site :) but the report is difficult to read.
Is there a way to colour code each line e.g every 2nd line blue or something?
Regards
Ian
View 2 Replies
View Related
Jun 8, 2006
Yes, I know synchronisation to alternate partners is deprecated in SQL2005 but....
In SQL2000 there is a Sync Partners tab in the publication properties dialog that allows you tick a checkbox for each co-publisher to be enabled as an alternate synchronisation partner. What is the equivalent in SQL2005?
I've set up replication in SQL2000 following these instructions http://support.microsoft.com/?kbid=321176 and it works. Now I'm trying to do the same thing in SQL2005 but I can't find a substitute for steps 10 & 11 in the section "Set Up the Alternate Synchronisation Partner". What's the answer?
Thanks in advance.
View 10 Replies
View Related