Error with Create Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error with Create Function

Author

Topic

The Sweg
Starting Member

29 Posts

Posted - 2011-06-09 : 10:30:22

I've spent my whole life gathering a nice collection of ignorance about SQL so it's no surprise that I get so close to a solution but end up with one little thing wrong that keeps my statement from working!
I created a select statement that produces the exact result that I was after. (Yeah!)
However, when trying to put this statement into a CREATE VIEW statement, it wouldn't work because I use the COMPUTE BY clause in it, which I come to find out isn't allowed in VIEWS. (Grrrr!)
So, I'm thinking, "Why don't I write a User Defined Function with the same code and then reference the UDF in the VIEW statement?"
Of course, you can't use the GETDATE() function inside a UDF, which I was trying to do. (Grrrr!)
So, after cutting that out, and removing all else that gave me errors, I'm down to one last error and would love help with this.

Here's my code:

CREATE FUNCTION ReworkWeek()

RETURNS @ReworkTotalsTab TABLE

(
[Number] float(8),
[Name] varchar(80),
[Job #] varchar(80),
Department varchar(80),
Regular decimal(4)
)
AS

BEGIN

INSERT @ReworkTotalsTab

SELECT

H.EmployeeId AS [Number],

EM.[Name],

H.CostCode AS [Job #],

CF.Department,

ROUND(CONVERT(decimal(4), DATEDIFF(n,H.[TimeIn],H.[TimeOut]))/60,2,0) AS Regular

FROM [database1].dbo.EmployeeHours H

LEFT OUTER JOIN

(

SELECT

E.No_ AS [Number],

CASE WHEN E.[Middle Name] LIKE '' THEN E.[First Name] + ' ' + E.[Last Name]

ELSE E.[First Name] + ' ' + E.[Middle Name] + ' ' + E.[Last Name]

END AS [Name]

FROM [database2].[dbo].[Employees] E

) EM

ON H.EmployeeId = EM.[Number]

LEFT OUTER JOIN

(

SELECT

CAST(C.JobCode AS varchar) + '-' + C.CustomFieldData AS Department,

C.JobCode

FROM [database1].dbo.EmployeeCustomFieldsData C

WHERE CustomFieldId = '6312'

GROUP BY C.JobCode, C.CustomFieldData

) CF

ON H.JobCode = CF.JobCode

GROUP BY H.Costcode, H.EmployeeId, CF.Department, EM.[Name], H.JobCode, H.TimeIn, H.TimeOut

HAVING

ROUND(CONVERT(decimal(4), DATEDIFF(n,H.[TimeIn],H.[TimeOut]))/60,2,0) > 0

AND

H.JobCode IN( '155', '156', '157', '158', '159', '160', '161', '162', '163', '164', '165', '166')

ORDER BY H.CostCode, H.EmployeeId

COMPUTE SUM(ROUND(CONVERT(decimal(4), DATEDIFF(n,H.[TimeIn],H.[TimeOut]))/60,2,0)) BY H.CostCode

RETURN

END

Here's the error I get (using Query Analizer and SQL Server 2000):

Server: Msg 213, Level 16, State 5, Procedure ReworkWeek, Line 16
Insert Error: Column name or number of supplied values does not match table definition.

My guess is that my column names are not correct, but I don't know what else to put in them.

Any help is greatly appreciated!

--Sweg

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-09 : 11:11:08

I don't think you can do what you are trying to do. My experience with using the COMPUTE clause is limited. But, I think, in essence it returns separate result sets. So it doesn't make sense to have it in a view or function.

The Sweg
Starting Member

29 Posts

Posted - 2011-06-09 : 11:27:21

Thanks for the reply. Basically, what I'm trying to do is create a report. Usually, I create a view and can get that into Excel easily, which is what I'm trying to do here. I get the exact result I want from the original select statement as I had it written when running it in Query Analizer, however, I don't know how to get that result into a report or link it to some other output that clients can easily see.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-09 : 11:28:29

You can turn a compute into a single resultset using a couple of cte's an a union.
Depends a bit on the format you need.

select yr=year(crdate), xtype, num=COUNT(*)
from sysobjects
group by year(crdate), xtype
order by year(crdate), xtype
compute sum(COUNT(*)) by year(crdate)

;with cte as
(
select yr=year(crdate), xtype, num=COUNT(*), seq=1
from sysobjects
group by year(crdate), xtype
),
cte2 as
(
select yr, xtype='total', num=SUM(num), seq=2
from cte
group by yr
)
select * from cte
union all
select * from cte2
order by yr,seq,xtype

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

The Sweg
Starting Member

29 Posts

Posted - 2011-06-09 : 11:57:09

Thanks for the suggestion, Nigel. i'm not familiar with CTE's, but I will look into them and see if I can get this to work.

Thanks!

The Sweg
Starting Member

29 Posts

Posted - 2011-06-09 : 15:29:26

From what I can see, CTE's are not supported in SQL Server 2000, only 2005 and up. Ugggh.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-10 : 06:20:02

Without a cte it would be

select yr, xtype, num
from
(
select yr=year(crdate), xtype, num=COUNT(*), seq=1
from sysobjects
group by year(crdate), xtype
union all
select year(crdate), xtype='total', num=COUNT(*), seq=2
from sysobjects
group by year(crdate)
) a
order by yr,seq,xtype

The problem is that you will have to duplicate code.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

The Sweg
Starting Member

29 Posts

Posted - 2011-06-10 : 07:59:27

Thanks for the suggestion. I'll give that a try.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-10 : 23:20:19

>> I've spent my whole life gathering a nice collection of ignorance about SQL so it's no surprise that I get so close to a solution but end up with one little thing wrong that keeps my statement from working! <<

LOL! And am the other extreme :) I used to be a FORTRAN, C, Algol, Pascal, Ada programmer before SQL

>> However, when trying to put this statement into a CREATE VIEW statement, it wouldn't work because I use the COMPUTE BY clause in it, which I come to find out isn't allowed in VIEWS. (Grrrr!) <<

COMPUTE BY is depreciated, but we do have ROLLUP, CUBE and other things now.

You also screwed up the data element names -– read ISO-11179 for the right way to do it. Not just a little bit, but so ugly that your code will never work with ISO Standards of any kinds.

We seldom use FLOAT. And we hate table valued functions. You are formatting data for display in the database. Data elements change names from place to place (i.e. H.EmployeeId = EM.[Number] and other absurdities). You seem to have magical generic custom columns! That is HUGE "Oh Sh*t!" And you call them fields, which are nothing like columns.

ROUND(CONVERT(decimal(4), DATEDIFF(n,H.[TimeIn],H.[TimeOut]))/60,2,0)

We CAST() now, not the old CONVERT(). Why do you need nanoseconds? Why not use it to the minute? Why not have precise data element names?

DATEDIFF (M, H.emp_start_time, H.emp_end_time)

>> My guess is that my column names are not correct, but I don't know what else to put in them.<<

Probably, but that is just the start of problems int his total mess.

>> Any help is greatly appreciated! <<

Can you throw this out and start over with a real RDBMS? I am serious. I could not figure out what was happening, I could not correct the data element names, the math or anything else.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-06-11 : 04:18:59

quote:
Originally posted by jcelko

We seldom use FLOAT. And we hate table valued functions. ...

We CAST() now, not the old CONVERT().

Using the Royal We again Joe? Cause you're certainly not talking for all of us. How about helping (with a SQL 2000 problem) rather than just criticising?

--
Gail Shaw
SQL Server MVP

The Sweg
Starting Member

29 Posts

Posted - 2011-06-11 : 06:42:25

jcelko,
I don't mind the criticism, as long as I can benefit from it. There's some background details that I left out that might explain this 'total mess'. Firstly, I didn't create one single database or table that I am using in this statement, so the column names and data types are what they are and they are used by many other applications (time clock system, etc.) so I'm not sure changing column names at this point would prove much. Secondly, I'm stuck using SQL 2000 through no choice of my own, though gladly, we are upgrading soon to 2008. Thirdly, I was tasked with creating a report from this crap-pile of data by people who know way less than I do about SQL or databases in general, and as for what I know, it ain't much (which is why I'm posting in the "New to SQL Server Programming" forum). I'm not a DBA nor formally trained in any way (which I realize is painfully obvious). I'm an ex-carpenter, who became a computer draftsman only 8 years ago and now with less than 10 months of SQL experience (all self-taught) I'm at least able to write a statement in SQL that actually gave me the exact result I wanted (though the code is admittedly not pretty). The problem here is that I cannot get that result set from Query Analizer into Excel for client use, which is why I started down the CREATE VIEW path and then the UDF path.

I do appreciate all the helpful suggestions from users on here. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources