Solved

VB.Net - SQL Query and Design Question

Posted on 2014-12-10
4
113 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 142

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 142

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 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

22 Experts available now in Live!

Get 1:1 Help Now