SQL Functions

michael.boucher · May 9th, 2008, 01:06 AM

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!

Jeff Moden · May 9th, 2008, 01:38 AM

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

--Jeff Moden

Peso · May 9th, 2008, 03:17 AM

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
    )