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

DECODE In MS SQL

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

DECODE?

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

Decode Function

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

Decode In SQL Server

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

Case? Decode?

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

GROUP BY On DECODE

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

DECODE In SQL Server?

Jul 20, 2005

What is the function in SQL that works like DECODE in Oracle?"Thanks,N

View 10 Replies View Related

Decode Funktion

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

Urgent!!!!!!!! Decode Function In Sql

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

What Is The DECODE Statement In SQLSERVER?

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

Equallent To Decode In Oracle?

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

How To Decode In SQL Server 2005

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

Decode Base64 To Image!

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

How To Translate Oracle Decode

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

Execute Stored Procedure To Decode A Varchar Hexadecimal String

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

Alternate Way

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

Rownum Alternate In MS-SQL

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

Alternate Names

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

Any Alternate Method Available ?

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

Alternate To A Not In Query

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

Alternate To Cursors

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

Alternate To Sql Web Assistant

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

Alternate For The SQL Query

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

Setting Up An Alternate Login To Sa

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

Candidate,composite And Alternate Key

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

Urgent - Alternate For Rowid

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

Alternate FTP Client For NT And SQL Server?

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

Alternate For Linked Server?

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

Alternate Database Toggle

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

Alternate Way Of Row Number() Function

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

Alternate Colours On Each Line

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

Alternate Synchronisation Partners

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







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