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
Solved

VB.Net - SQL Query and Design Question

Posted on 2014-12-10
4
115 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
  • 2
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

861 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