Would like help designing a query

Posted on 2007-08-07
Last Modified: 2013-11-05
I am trying to write a query that will return all the packaging materials that we need to have in stock on a given date.  Here is some background:

I can write a query that will return all the part numbers and quantities that are scheduled to be produced by a given date.

I can write a query that will call a function provided by our ERP supplier that will return all the packaging materials, and quantities for any given item.

The only way that I would know to provide a list of the total packaging requirements needed on a given date would be to run the first query and insert the items into a temp table, then create a loop that would run through each item in the temp table and call the function that returns the packaging material requirements for each item.

Ive got to believe that there is a better way to do this, can someone steer me in the right direction ??

Thanks !!
Question by:SteveB2175
    1 Comment
    LVL 42

    Accepted Solution

    SS2005 has the APPLY clause which is designed exactly for that purpose.  Short of that or reverse engineering the vendor's function, I'm afraid you stuck with a procedural solution.  However, I don't think you need a temp table for the results of the first query.  Just iterate thru the results of the first query using a cursor. You probably want to collect the results of the second query in a temp table inside the loop of your cursor.


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now