Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Would like help designing a query

Posted on 2007-08-07
Medium Priority
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

dqmq earned 2000 total points
ID: 19648589
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

571 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