MS SQL Server :: DECODE Function

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 !

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 In SQL Server?&"
What is the function in SQL that works like DECODE in Oracle?"Thanks,N

View Replies !

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 Replies !

What Is The DECODE Statement In SQLSERVER?
I need to know what is the DECODE statement called in SQLSERVER?

Can somebody provide me simple decode statement for sqlserver.

Thanks,
Ranjan

View Replies !

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 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 !

Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark

View Replies !

Help Convert MS Access Function To MS SQL User Defined Function
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function

View Replies !

In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO

I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.

Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.

Thanks in advance,
Scott Chang

View Replies !

Using RAND Function In User Defined Function?
Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View Replies !

I Want A Function Like IfNull Function To Use In Expression Builder
Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )

i hope to have the answer because i need it so much.

Maylo

View Replies !

ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )
Can anybody know ,how can we add builtin functions(ROW_NUMBER()) of Sql Server 2005 into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?

View Replies !

Function To Call Function By Name Given As Parameter
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View Replies !

Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.

Hi,

I am trying to create a inline function which is listed below.

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO

while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.

Pleae help me to fix this error

thanks

Purnima

View Replies !

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need.
Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error. Ugh. Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)

View Replies !

Use Getdate Function In A Own Function
Hi,I have written a stored proc with some temporary tables and also useda getdate() in my stored proc. When i try to call the sproc the erroris that we can only use extended sprocs or function inside a sproc.Now if try to write the stored proc directly inside a fuction ie copypaste after changing my temp tables to tables the problem is , i get aerror invalid use of getdate in sproc.What do i do to get somethingfor my results inside a table.Thanks in advance.RVG

View Replies !

Calling A Function From A Function?
Hi All

Yesterday Peso was gracious enough to help me with creating function/views/sp's

I took those examples and extended what had from excel into function in SQL

however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function?

Here are excerpts two functions I have:

We'll call this function UserUsage()
------------------------------------
RETURN(
SELECT ut.LastName, ut.FirstName,
CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)],
Max(hu.time_stamp) AS [Last Log Date],
pct.Title, cat.topic_name
FROM ZSRIVENDEL.dbo.UserTable ut,
ZSRIVENDEL.dbo.history_usage hu,
ZSRIVENDEL.dbo.pc_CourseTitles pct,
ZSRIVENDEL.dbo.cam_topics cat
WHERE ut.student_id = hu.student_id
AND hu.course_id = pct.CourseID
AND hu.topic_id = cat.topic_id
AND ((ut.ClientID=@ClientID)
AND (pct.ClientID=@ClientID)
AND (ut.GroupID=3400)
AND (hu.time_stamp>= @StartDate
And hu.time_stamp< @EndDate)
AND (hu.session_time<21600000))
GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name
)

and will call this function UserSummary():
-----------------------------------------
RETURN (
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
FROM ZSRIVENDEL.dbo.UserTable AS ut
INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu
ON hu.Student_ID = ut.Student_ID
WHERE ut.ClientID = @ClientID
AND ut.GroupID = 3400
AND hu.Time_Stamp >= @StartDate
AND hu.Time_Stamp < @EndDate
AND hu.Session_Time < 21600000
GROUP BY ut.LastName, ut.FirstName
)

As you can see the first part of the both query are simlar. In particular the:

SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]

and also the variables @StartDate and @EndDate.

In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please?

Thank you!

View Replies !

Using SQL MOD Function
DECLARE @TotalAmount AS INTEGER DECLARE @TotalPoints As INTEGER SELECT @TotalAmount = SUM(Amount) From Rewards WHERE MemberID = @MemberID AND Redeemed = 1 @TotalPoints = @TotalAmount MOD 20

I have the code above but I keep getting an error message around the last line that I am not sure what it means. Can anyone please help?? Thanks in Advanc3e

View Replies !

Use Function In Asp.net
I have this scalar -valued function in sql called dbo.GetGoodCustomer ALTER function [dbo].[GetGoodCustomer](@client_id int)returns numeric(10, 2)asbegin declare @getgoodcustomer as numeric(10, 2) declare @review_type as int select @review_type = item_num from customers where client_id = @client_id if @review_type = 0 begin select @getgoodcustomer = getgoodcustomer from customers where client_id = @client_id end if @review_type > 0 begin select @getgoodcustomer = l.g from customers c inner join detail l on c.client_id = l.client_id where c.client_id = @client_Id and c.item_num = l.item_num endReturn @getgoodcustomerendNow, how can I use this function in asp.net (vb.net) ?Because whatever is getgoodcustomer would give me I need to display that in datagrid's text box.Loop thru datagrid If CType(dgItem.FindControl("txtID"), TextBox).Text = 13 ThenDim txtgoodcustomer As TextBox = CType(dgItem.FindControl("txtcust"), TextBox)basically amount returns from function would be showing in txtcust textbox.I don't know I have never done this before..

View Replies !

SQL AVG Function
I have a table:tblRateUserUserIDJudge intUserIDJudged intscore int
this table contains the userid of the person giving a score (judge) and the person receiving the score (judged) and the score itself.
Each user can only score another user once.
tblRateUser content:judged judge score6 5 87 5 107 6 515 7 415 5 9
When a new score is inserted I want to get the average score for the rated user by counting all scores for that user and divide by the number of records.
declare @avgscore decimal(4,1) set @avgscore=(select avg(score) from tblRateUser WHERE UserCodeJudged=@ID) print 'avg score: '+ cast(@avgscore as nvarchar(15)) in the above example this results for usercode judged 7 in an average score of 7.0 whereas I would expect 7.5 (10+5)/2
what am I doing wrong?

View Replies !

Function
Can a UDF return a table query as result? Please kindly provide sample T-SQL. Thanks.

View Replies !

Last Function
SQL Server has no Last() and Max() pulls the Max not the last. Is there a way to pull the Last payment amount with the date of that payment, in SQL Server????

tblClients - ClientID (PK)
tblPayments - PaymentID (PK)

Thanks for any help,

View Replies !

SQL Function
hello everyone,I created a function in MSSQL that builds an html coupon based upon thecoupon ident (more complex than that, but that's not where i'm havingproblems!)When the function builds the coupon, it returns the stream asVarChar(8000)I am calling this function in a sproc that passes in the coupon ident.But when I execute this sproc, I get the following error:Syntax error converting the varchar value '<html>......"What am I doing wrong? Are there problems with the datatypes I"musing?Thanks in advance for any help on this?

View Replies !

Need A Function
I'm looking for a string function that is similar to the INSTRfunction in VB. I haven't seen anything in the help files that I canuse. Does anyone have any suggestions?Here's what I'm trying to do:There is a field in a table that will look something like this -"XXXXXX - YY".I want to separate it on the dash and get two strings out of it -"XXXXXX" and "YY". I'm trying to keep it all in a stored procedureand avoid a vb script or exe.I'm envisioning something like this:declare @CDT datetimeselect @CDT = createdatetime from imOrderHdrwhere VendorCode = 'SYG' and createdatetime is not nulland status in (1,2,3)select d.VendorStockNumber, substring(i.ItemDescription, 1,instr(iItemDescription, '-') - 1),substring(i.ItemDescription, instr(iItemDescription, '-') + 1),d.QtyOrdered, d.PurchasePrice, (d.QtyOrdered * d.PurchasePrice) asExtensionfrom imOrderDetail djoin imItem i on i.ItemCode = d.ItemCodewhere d.CreateDateTime = @CDTI'd write my own function, but the computers this will be run on haveSQL 7.Any suggestions will be appreciated.Thanks!Jennifer

View Replies !

Function
Good Morning,I have a question.Is there a function like ISNULL but for the space value(example ISSPACE)?THANKS

View Replies !

Function
What are the diffferences between a scalar Function and a table function?

View Replies !

API Function
Hi
How can I use an API function in a stored procedure in sql server 2000?

View Replies !

SP Vs FUNCTION
Hi,
When we talk about the difference between SP and Function, it is said that Function returns a value where as SP doesn't. My Question is, Then what does the "OUTPUT" parameter Stands for in SP?
Kindly make my doubts clear on this.

Thanks In Adv.
Rahul Jha

View Replies !

Last Function
Access has a last function available for when you are grouping on a summary level. What this does is return the last record for that particular grouping. Does sql have function of that nature?

Example:

Item_Code Time
123456 11:40
123456 11:41
123456 11:42

By grouping on item code and using the last function on the Time column, the third record would be returned.

Item_Code Time
123456 11:42

All, help is appreciated.

View Replies !

Using A Function
I have found this very practicle function

CREATE FUNCTION FN_TRANSLATE (@VALIN VARCHAR (8000),
@FROM VARCHAR(256), @TO VARCHAR(256))
RETURNS VARCHAR (8000)
AS
BEGIN
-- effets de bord
IF @VALIN IS NULL
RETURN NULL
IF @FROM IS NULL OR @TO IS NULL
RETURN NULL
IF LEN(@VALIN) = 0
RETURN @VALIN
-- initialisation
DECLARE @I INTEGER
DECLARE @OUT VARCHAR(8000)
SET @OUT = ''
-- lecture caractère par caractère
SET @I =1
WHILE @I <= LEN(@VALIN)
BEGIN
IF PATINDEX('%' + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM) > 0
BEGIN
IF LEN(@TO) >= PATINDEX('%'
+ SUBSTRING(@VALIN, @I, 1)
+ '%', @FROM)
SET @OUT = @OUT
+ SUBSTRING(@TO, PATINDEX('%'
+ SUBSTRING(@VALIN, @I, 1)+ '%', @FROM), 1)
END
ELSE
SET @OUT = @OUT + SUBSTRING(@VALIN, @I, 1)
SET @I = @I + 1
END
RETURN @OUT
END

but how can I use it in MS SQL 2000-25000 ?

FN_TRANSLATE(dbo.Users.Name, 'àâäçéèêëîïôöùûüÿ',
'aaaceeeeiioouuuy')

doesnt work

I get an error on the colum name dbo.Users.Name

thank you

View Replies !

SUM Function
I have this query:
SELECT SUM(st_Count) FROM Queue WHERE st_ID = 1483
It works, but how can I change it, to see SUM for many ID's, which I have in list? I try this:
SELECT SUM(st_Count) FROM Queue WHERE st_ID = List.tw_ID
But there is error:
The multi-part identifier "List.tw_ID" could not be bound.
How to display SUM for meny ID's?

View Replies !

Len() Function
We have len() function in some of our stored procedures. Why one of our SQL servers (6.5) will not recognize this function while others do? While we transfer the stored procedures, the stored procedure can not be transfered to the database of that server.
Your immediate help will be appreciated.
Su

View Replies !

MTD Function
Is there a SQL MTD function ! Let's I want to delete MTD rows. Need something
like:

delete from abc where date_entered = mtd

Suggestions are appreciated.

View Replies !

Max Function
Hi,

I am having some difficulty with the Max function. I would expect it to return one row when you use it in a select statement (as long as there is truly one unique row)

Executing the query below give me the following result

select * from lead_with
where id= 80430
ORDER BY 1 DESC

id firm covacc avgFinal
----------- ----------- ----------- ----------------------------------------
80430 122 0 8.000000
80430 122 29353 9.000000
80430 505 NULL 8.500000
80430 2045 NULL 7.500000
80430 122 18815 9.000000
80430 223 NULL 8.500000
80430 625 NULL 8.000000

Executing the next query give me the following results

select MAX(avgFinal), IBFirm
from lead_with
where [ID] = 80430
group by avgFinal,IBFirm
order by avgFinal desc

IBFirm
---------------------------------------- -----------
9.000000 122
8.500000 223
8.500000 505
8.000000 122
8.000000 625
7.500000 2045

Not only does it return more than one row, it actually misses a row from
above

80430 122 18815 9.000000

I guess I am missing something. What I am trying to do is get the row with
the maximum value in the avgFinal column.

Any help is appreciated.

Thanks,

James

View Replies !

DTS Function
Hi

I'm creating DTS packages to load data from text files. I'm finding that a lot of the data needs to be transformed in the same way (e.g trailing "." removed). Rather than writing individual activex scripts to do this, is it possible to write my own DTS function which I call from my DTS packages?

Thanks

Andy

View Replies !

VBA Function In SQL ??
Hi,

I am upgrading an AccessXP database to SQL 2000 and have an issue with week numbers.

There is currently a piece of VBA that calculates the week number for time recording purposes using the DatePart function as
DefaultVal_WeekNo = DatePart("ww", TaxDate, vbMonday, vbFirstFourDays)

I have tried to get this replicated in SQL by using

declare @TaxDate as datetime
declare @TheWeek float

set @TaxDate = @TheDate -90 -7

set @TheWeek = cast(datepart("ww",@TheDate) as numeric (30))
-
cast(datediff("wk", @TaxDate, @TheDate) as numeric (30))

but this is not reurning the correct values, i belive this is because the VB function has the vbMonday argument.
Can anyone tell me how i can get wround this please?

TIA

View Replies !

Function
I have a function dbo.Validlan
which is takes 4 paramentes:
par1,par2,par3,par4
I would like to select all 4 at run time:
SELECT par1,par2,par3,par4
FROM table1 t1
INNER JOIN table2 t2
ON t1.enrl=t2.enrl

View Replies !

Help With MAX Function ?
I have the following problem statement: Quote: What was the most expensive merchandise item sold in July? Display sale date, sale price and merchandise description. Sort output by descending sales price. (hard-coding date is OK). Here's my query statement:
Code:

SELECT saleDate AS "Sale Date", salePrice AS "Sale Price",description AS "Merchandise Description"FROM Sale, SaleItem, MerchandiseWHERE salePriceAND DATENAME(MONTH, dateBorn) = 'July'ORDER BY salePrice DESC

I want to know how I would use the MAX function on my query since I know this would give me the most expensive item. Am I writing my query statement correctly? Do I have to declare the MAX function in the SELECT statement and on the WHERE statement of just on the WHERE statement? Any clarifications/help would be appreciated

View Replies !

How To Use IN Function..
Hi..

I need to use IN function in a sql prodecure..

my procedure is below

Code:

CREATE PROCEDURE [dbo].[Cat]
@gets nvarchar(200)
AS
begin
select * tbl where id in (@gets) order by id desc
end
GO

the type of id field is identity number
also I tried
@gets int

gets is called from my asp page.

in asp page, I use prodecure like below

Code:

ids="11,12,13"
Rs.Open "Cat '"&ids&"'", db,1,3

it doesn't work!

I get the following error

Code:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value '11,12,13' to a column of data type int.

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

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