?
Solved

VB.Net - SQL Query and Design Question

Posted on 2014-12-10
4
Medium Priority
?
118 Views
Last Modified: 2014-12-15
Good Day Experts!

I am working on a project that I need a some assistance/direction if you can help.  

I have a staging table that contains Invoice Numbers and Order Numbers.  There are multiple lines with the same Invoice Number as multiple Order Numbers can be invoiced on the same Invoice Number.   It looks like the following:

Invoice     Order
1234         A
1234         B
1234         C
5678         A
5678         B
5678         C
4321         A
4321         B
4321         C

I have to now go get the "detail" for each Order on the Invoice and output a text file for each Invoice.  So, the above data would produce 3 separate files(1 for each Invoice).  

I could do a select * on the table and then when the InvoiceNumber changes start a new file.  However, instead I would like to have the InvoiceNumber as the "OuteLoop" trigger to create the file.  But the trouble I am having is figuring how to get those unique InvoiceNumbers while getting a list of the OrderNumbers related to each InvoiceNumber.  

Do you think this is possible all in one query? Or do I need to do Select distinct InvoiceNumbers for the outer loop then once inside the loop query the table again for the related OrderNumbers so I can go get the detail information?

Thanks for the suggestions and insight,
jimbo99999
0
Comment
Question by:Jimbo99999
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 40491493
you apply a sorting:
select ...
  from yourtable
order by Invoice, 'Order

and you loop on that one;
each time the invoice changes, you close the previous file (if any), and open a new one
after the loop, don't forget to close the open file (if any)


or, indeed you loop on the invoice:
select invoice from yourtable group by invoice

and on each of those line, get all the orders (and their details)
select order ... from yourtable where invoice = x

you may also (which is the eventually recommended method) load the data into a dataset, and make relations, then you can fetch the data in 1 go, and still loop properly as you requested
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40491500
see here to start with: http://msdn.microsoft.com/en-us/library/system.data.dataset.relations%28v=vs.110%29.aspx
you fetch the full listing (data table1)
you fetch the distinct invoice list from the data table 1 into data table 2
   => http://msdn.microsoft.com/en-us/library/wec2b2e6%28v=vs.110%29.aspx

and you have all you need
0
 

Author Comment

by:Jimbo99999
ID: 40491657
Excellent idea...I will try it out today.  Hopefully there won't be too many User fires to tend to.  

Thanks,
jimbo99999
0
 

Author Closing Comment

by:Jimbo99999
ID: 40500696
Thanks for the suggestion...it is working now!

Have a good day,
jimbo99999
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

764 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