Showing posts with label function. Show all posts
Showing posts with label function. Show all posts

Friday, March 30, 2012

problem in Function ?

Create FUNCTION FUNCTION_NAT

(

@.F_BRANCH_CODE CHAR,

@.F_COUNTRY CHAR,

@.F_CLIENT_VALUE CHAR

)

RETURNS TABLE

AS

RETURN

(

SELECT NLGIC_VALUE AS VALUE

FROM RAGHU.NAT

WHERE BRANCH_CODE = @.F_BRANCH_CODE

AND

COUNTRY = @.F_COUNTRY

AND

CLIENT_VALUE = @.F_CLIENT_VALUE

)

This is my function . when i run it as follows

select * from FUNCTION_NAT('450','British','BRI')

i did not get any value it is empty .

But when i run this query

SELECT NLGIC_VALUE AS VALUE

FROM RAGHU.NAT

WHERE BRANCH_CODE = '450'

AND

COUNTRY = 'British'

AND

CLIENT_VALUE = 'BRI'

it works fine . What is the problem in my function .

My knee-jerk reaction is that this might be a problem with the type definitions of the function parameters; hold on and I will try to verify. Look at this:

Code Snippet

alter FUNCTION FUNCTION_NAT
(
@.F_BRANCH_CODE CHAR,
@.F_COUNTRY CHAR,
@.F_CLIENT_VALUE CHAR
)
RETURNS TABLE
AS
RETURN
( select @.f_branch_code as branch_code,
@.f_country as country,
@.f_client_value as client_value
)

go

select * from function_nat('450','British','BRI')

/*
branch_code country client_value
-- -
4 B B
*/

You need to alter your type definitions from this

Code Snippet

(
@.F_BRANCH_CODE CHAR,
@.F_COUNTRY CHAR,
@.F_CLIENT_VALUE CHAR
)

to something like this:

Code Snippet

(
@.F_BRANCH_CODE CHAR(xx),

@.F_COUNTRY CHAR(yy),
@.F_CLIENT_VALUE CHAR(zz)
)

where xx, yy, and zz are the maximum number of characters that will be passed through each argument.

|||

Kent - i think you've hit the nail on the head.


From BOL: When n is not specified in a data definition or variable declaration statement, the default length is 1

SQL will not throw an error but merely cut the string off at the length. From the look of at least one of your variables, you may be better off with the VARCHAR datatype.


HTH!

sql

Friday, March 23, 2012

Problem in a derived column

Dear Friends,

I have an derived column in an ETL, that use the DateDiff function.

The experssion of the derived column is:

DATEDIFF("d",RKData,MaturityDate) / 365

The problem is that the result always come as an integer, and in fact the result of the expression usually has decimal numbers.. :-(

I defined the new colum of derived column as doube-precison float [DT_R8]....

Someone help me?

Thanks!

PedroCGD wrote:

Dear Friends,

I have an derived column in an ETL, that use the DateDiff function.

The experssion of the derived column is:

DATEDIFF("d",RKData,MaturityDate) / 365

The problem is that the result always come as an integer, and in fact the result of the expression usually has decimal numbers.. :-(

I defined the new colum of derived column as doube-precison float [DT_R8]....

Someone help me?

Thanks!

Try:
(DT_NUMERIC,7,4)DATEDIFF("d",RKData,MaturityDate) / (DT_NUMERIC,7,4)365

That should give you 4 decimal places. Adjust the precision and scale accordingly, if you need.|||

THANKS!!

I changed to (DT_NUMERIC,7,2)DATEDIFF("dd",RKData,MaturityDate) / (DT_NUMERIC,7,2)365 and it works!!

Thanks!!!!!!

Wednesday, March 21, 2012

Problem handling NULL values in SQL Server 2000

Hi I have a function with if and else conditions. For some reason the
second if condition does not seem to work. It is not able to check for
Null values.
I am new to SQL programming and so I cant figure out the actual
problem.
Any help would be greatly appreciated. Cheers!
Here is the function.
ALTER FUNCTION dbo.labelSalutation ( @.moSurname varchar(50),
@.faSurname varchar(50) )
RETURNS varchar(50)
AS
BEGIN
DECLARE @.res varchar(50)
IF (@.moSurname = @.faSurname or @.moSurname = null)
BEGIN
SET @.res = 'The '+@.faSurname+' Family'
END
ELSE
IF (@.faSurname = NULL and moSurname <> Null) /* <-- This
condition does not work */
BEGIN
SET @.res = 'The '+@.moSurname+' Family'
END
ELSE
IF (@.moSurname<> @.faSurname)
BEGIN
SET @.res = 'The '+@.moSurname+' & '+@.faSurname+' Family'
END
RETURN @.res
ENDOn Aug 29, 9:57 am, Rex <rakesh...@.gmail.com> wrote:
> Hi I have a function with if and else conditions. For some reason the
> second if condition does not seem to work. It is not able to check for
> Null values.
> I am new to SQL programming and so I cant figure out the actual
> problem.
> Any help would be greatly appreciated. Cheers!
> Here is the function.
> ALTER FUNCTION dbo.labelSalutation ( @.moSurname varchar(50),
> @.faSurname varchar(50) )
> RETURNS varchar(50)
> AS
> BEGIN
> DECLARE @.res varchar(50)
> IF (@.moSurname = @.faSurname or @.moSurname = null)
> BEGIN
> SET @.res = 'The '+@.faSurname+' Family'
> END
> ELSE
> IF (@.faSurname = NULL and moSurname <> Null) /* <-- This
> condition does not work */
> BEGIN
> SET @.res = 'The '+@.moSurname+' Family'
> END
> ELSE
> IF (@.moSurname<> @.faSurname)
> BEGIN
> SET @.res = 'The '+@.moSurname+' & '+@.faSurname+' Family'
> END
> RETURN @.res
> END
Replace IF (@.faSurname = NULL and moSurname <> Null) with IF
(@.faSurname is NULL and moSurname is not Null)

Problem function year in sql mobile

Dear , all

I have a problem in fucntion year in sql mobile , Use select Date_in from Test Where Year(Date_in) = 8

Brg

Tingnong

The YEAR function does not exist in SQL Compact, you can use:

Code Snippet

DATEPART(yy, date)

like so:

Code Snippet

select [Order Date] from Orders Where DATEPART(yy, [Order date]) = 1991

(Using Northwind.sdf inculded with SQL Compact 3.1 SDK)

problem formatting date

Hi all,
I make an insert of date with VB.Net using the "format" function:
format(Now(), "dd/MM/yyyy hh:mm:ss").
In my dev environment it work fine.
But when I put the page on production server
the format fuction change the tima formatting, change ":" with "."
so it pass hh.mm.ss to the DB crashing the insert.
I used a replace function to restore ":", and it work fine.
Bue any idea?
Why this happen
thanks allThis is a pure VB.NET question, so you probably have better luck asking this in a VB.NET forum.
However, I strongly encourage you to format the datetime as 'YYYYMMDD hh:mm:ss'. Check
http://www.karaszi.com/SQLServer/info_datetime.asp for the reason.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Henry Cabot Enaus III" <hceiii@.yahoo.com> wrote in message
news:512ff012.0409010324.126ebd78@.posting.google.com...
> Hi all,
> I make an insert of date with VB.Net using the "format" function:
> format(Now(), "dd/MM/yyyy hh:mm:ss").
> In my dev environment it work fine.
> But when I put the page on production server
> the format fuction change the tima formatting, change ":" with "."
> so it pass hh.mm.ss to the DB crashing the insert.
> I used a replace function to restore ":", and it work fine.
> Bue any idea?
> Why this happen
> thanks all

Friday, March 9, 2012

Problem creating view for cube

Anyone see a problem with creating this view for a cube??

The error I get seems to be in line 8 with the sum function, but I see nothing wrong with it.

CREATE VIEW CarCube AS
SELECT CASE WHEN (GROUPING(CarType) = 1) THEN 'ALL'
ELSE ISNULL(CarType, 'Not Known')
END AS CarType,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'Not Known')
END AS Color
SUM(Amount) AS TotalAmt
FROM Inventory
GROUP BY CarType, Color WITH CUBEYou need a comma after Color on the line before|||lol, sometimes the simplest things are the hardest to find.

Thanks|||The people who are really good at finding at simple screw ups are the ones who are really good at making them. ;-)

Saturday, February 25, 2012

Problem creating a function from an assembly

I have compiled a dll with the following code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes

Public Class SICTrans

Public Shared Function TransSIC(ByVal inpSIC As String) As String
'Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim NewSIC, TempSIC, tempFSIC As String

If Len(NZ(inpSIC)) > 0 Then
TempSIC = NZ(inpSIC)

If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))

tempFSIC = Left(TempSIC, 2) + "." + Mid(TempSIC, 3, 2)

If Val(Right(TempSIC, 1)) > 0 Then

tempFSIC = tempFSIC + "/" + Right(TempSIC, 1)
End If
End If

NewSIC = tempFSIC

TransSIC = NewSIC

End Function
Public Shared Function NZ(ByVal input As String) As String
If Not (input Is Nothing) Then
Return input
Exit Function
End If
Return String.Empty

End Function
End Class

Which compiles fine...

i then use the following code to create the assembly in SQL which is fine:

USE NARD
GO
CREATE ASSEMBLY SICCodeTrans
FROM 'c:\SICCodeTrans.dll'
WITH PERMISSION_SET = SAFE
GO

but when i goto create the function with the following code it wont have it!

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.SICTrans.TransSIC
GO

It gives me the following error message

Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1
Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.

Any ideas?

Thanks

Marek Kluczynski

I think what's going on here is that there is a root namespace on the project.

IIRC, in a C# project VS will simply insert the root namespace delcaration into your code when you use certain templates. In VB, on the other hand, the compiler decides to insert the namespace definition without having it show up in the code.

When you go to create the function in SQL, it can't find it because there is no SICTrans class in the default namespace, there is a SICTrans class in whatever root namespace was set on the project.

You can right-click on the project in VS and pull up the propertiesunder the "Application" tab you'll see this.

Assuming your namespace is NAMESPACE, you'd think you could do the following:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.NAMESPACE.SICTrans.TransSIC
GO

This won't workit's a (rather annoying) syntax error. You have to write it like this:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.[NAMESPACE.SICTrans].TransSIC
GO

Hope this works for you. Let us know. :)

Cheers
-Isaac

|||

yes this seems to have resolved the namespace error.

I now have the following error:

Msg 6551, Level 16, State 2, Procedure TransSICCode, Line 1

CREATE FUNCTION for "TransSICCode" failed because T-SQL and CLR types for return value do not match.

Many thanks

Marek

|||I suspect two things. First, you should map the string to an nvarchar, not a varcharCLR strings are Unicode. Second, you need to give a parameter to the nvarchar, e.g., nvarchar(4000), nvarchar(max), etc.

Cheers,
-Isaac|||Many thanks for all your help

Problem creating a function from an assembly

I have compiled a dll with the following code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes

Public Class SICTrans

Public Shared Function TransSIC(ByVal inpSIC As String) As String
'Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim NewSIC, TempSIC, tempFSIC As String

If Len(NZ(inpSIC)) > 0 Then
TempSIC = NZ(inpSIC)

If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))

tempFSIC = Left(TempSIC, 2) + "." + Mid(TempSIC, 3, 2)

If Val(Right(TempSIC, 1)) > 0 Then

tempFSIC = tempFSIC + "/" + Right(TempSIC, 1)
End If
End If

NewSIC = tempFSIC

TransSIC = NewSIC

End Function
Public Shared Function NZ(ByVal input As String) As String
If Not (input Is Nothing) Then
Return input
Exit Function
End If
Return String.Empty

End Function
End Class

Which compiles fine...

i then use the following code to create the assembly in SQL which is fine:

USE NARD
GO
CREATE ASSEMBLY SICCodeTrans
FROM 'c:\SICCodeTrans.dll'
WITH PERMISSION_SET = SAFE
GO

but when i goto create the function with the following code it wont have it!

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.SICTrans.TransSIC
GO

It gives me the following error message

Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1
Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.

Any ideas?

Thanks

Marek Kluczynski

I think what's going on here is that there is a root namespace on the project.

IIRC, in a C# project VS will simply insert the root namespace delcaration into your code when you use certain templates. In VB, on the other hand, the compiler decides to insert the namespace definition without having it show up in the code.

When you go to create the function in SQL, it can't find it because there is no SICTrans class in the default namespace, there is a SICTrans class in whatever root namespace was set on the project.

You can right-click on the project in VS and pull up the propertiesunder the "Application" tab you'll see this.

Assuming your namespace is NAMESPACE, you'd think you could do the following:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.NAMESPACE.SICTrans.TransSIC
GO

This won't workit's a (rather annoying) syntax error. You have to write it like this:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.[NAMESPACE.SICTrans].TransSIC
GO

Hope this works for you. Let us know. :)

Cheers
-Isaac

|||

yes this seems to have resolved the namespace error.

I now have the following error:

Msg 6551, Level 16, State 2, Procedure TransSICCode, Line 1

CREATE FUNCTION for "TransSICCode" failed because T-SQL and CLR types for return value do not match.

Many thanks

Marek

|||I suspect two things. First, you should map the string to an nvarchar, not a varcharCLR strings are Unicode. Second, you need to give a parameter to the nvarchar, e.g., nvarchar(4000), nvarchar(max), etc.

Cheers,
-Isaac|||Many thanks for all your help

Problem converting UTC dates in dts

Can anyone help me convert UTC dates in a dts package? I have a db I need to transform, but all the dates are in utc format. Is there a function I can use in an ActiveX transformation script to convert, say, 38166, to a valid datetime value? There seems to be almost no documentation in using utc in SQL.How is that a UTC time?

http://www.dxing.com/utcgmt.htm|||I'll try...but for the record i dont think 38166 is a valid UTC date.
Please correct me if i'm wrong.

here is what i thougght UTC date looked like:

2004-07-01T16:15:30

SQL server CAST and CONVERT will handle this for you, by default.|||But your example does somewhat resemble a Julian date...|||But your example does somewhat resemble a Julian date...
1938?

or 2038?

But yeah it looks more julian..|||My vote is for 2008, since the Unix epoch is based on 1970.

-PatP|||I'll go with thta...(since I wrote a udf for that one once)...god damn toolbox...got to clean this damn thing out...

Wait a minute...|||i bet there'll be a party or 2 ~3:15 on Jan 19 2038

the end of the world as we know it.

:D|||you've got a julian date converting UDF?

Oooooo...i've wanted to do that but never had a project that required it.

Care to share??
:)|||I wrote one ages ago for military style Julian dates, and just posted it a day or two ago. Check here (http://www.dbforums.com/t1003180.html) for details.

-PatP|||thanks much.

sorry for the thread hijack chunky,
did you get a decent answer for the original post?

perhaps you could post an example of the date string you are dealing with, if not?