Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VB.Net - SQL Query and Design Question

Posted on 2014-12-10
4
Medium Priority
?
119 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

610 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