Wrox Programmer Forums
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 9th, 2008, 01:06 AM
Registered User
 
Join Date: May 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Functions

Hi,

I'm trying to create a function that will give me the purchase order number with the earliest delivery date for a product.

This is what I have at the moment:

CREATE
        FUNCTION earliestpo
                (
                        @itemcode INT) returns TABLE
        AS
                RETURN
                (SELECT top 1 t2.docnum,
                        t2.docduedate ,
                        t0.itemcode
                FROM wbau.dbo.oitm t0
                        INNER JOIN wbau.dbo.por1 t1
                        ON t0.itemcode = t1.itemcode
                        INNER JOIN wbau.dbo.opor t2
                        ON t1.docentry = t2.docentry
                GROUP BY t0.itemcode,
                        t2.docnum ,
                        t2.docduedate
                ORDER BY t2.docduedate
                );

OITM = Items Table
POR1 = Purchase Order Detail Table
OPOR = Purchase Order Header Table

My problem is when I go select * from earliestpo (######) this is giving me the earliest PO in the system, ignoring the fact that I want it for a particular product.

Any ideas? Thanks!
 
Old May 9th, 2008, 01:38 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Yeah... you haven't used @ItemCode anywhere in the function code... you need to add it to the missing WHERE clause...

--Jeff Moden
 
Old May 9th, 2008, 03:17 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also, why are you grouping?
Since you return no aggregated data, do not group by.

Code:
CREATE FUNCTION    dbo.fnEarliestPO
(
    @ItemCode INT
)
RETURNS TABLE
AS

RETURN    (
        SELECT TOP 1    t2.DocNum,
                t2.DocDueDate,
                @ItemCode AS ItemCode
                FROM        wbau.dbo.oitm AS t0
        INNER JOIN    wbau.dbo.por1 AS t1 ON t1.ItemCode = t0.ItemCode
        INNER JOIN    wbau.dbo.opor AS t2 ON t2.DocEntry = t1.DocEntry
        WHERE        t0.ItemCode = @ItemCode
                ORDER BY    t2.DocDueDate
    )





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Functions jezywrap ASP.NET 2.0 Professional 2 December 26th, 2006 05:30 PM
SQL date functions frankb522 Other Programming Languages 2 June 24th, 2006 09:04 AM
SQL Max() and Min() functions help shana Oracle ASP 1 April 25th, 2005 08:34 PM
SQL Max() and Min() Functions Help need... shana Access 1 September 7th, 2004 09:22 AM
sql server functions example mateenmohd Classic ASP Databases 3 June 12th, 2003 05:54 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.