How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask ATLPROG Your Own Question
ATLPROG
ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 7677
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
44910485
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

I need to write a recursive SQL statement that will accomplish

Customer Question

I need to write a recursive SQL statement that will accomplish the following.
user will input values two fields (probably via a parameter I am guessing)
[Lot No_]
[Item No_]
The SQL statement will then go and pull all of information I need based off of the following logic
for the [Lot No_], [Item No_] combo
if [Document No_] like 'MRP%' (if the document types begins with MRP
pull the following fields
select [Lot], [QNTY], [REV]
do this recursively for all MRP entries.
then once there are nor more MRPs do a similar function for
if [Document No_] like 'PPR%'
select [PO],[QTY],[Lot],[vendor]
do this recursively for all PPR entries.
else throw an error if [Document No_] contains any other entry than something starting with MRP or PPR.
here is what I have so far:
with ILE as (
select [Document No_],[Lot No_], [Item No_]
from [VPT$Item Ledger Entry]
where [Lot No_] = '32' and [Item No_] = '90218-101' and CONTAINS([Document No_],'MRP00001')
-- this is the starting point for the recursion
union all
select c.[Document No_], c.[Lot No_], c.[Item No_]
from [VPT$Item Ledger Entry] c
join ILE p on p.[Lot No_] = c.[Lot No_] and p.[Item No_] = c.[Item No_]
)
select *
from ILE;
help in getting this working would be great appreciated
Submitted: 2 years ago.
Category: Programming
Expert:  Ingo U replied 2 years ago.
Hi,
what SQL technology/server/version are you using here? MSSQL? Orcacle? MySQL? or maybe SQL query inside Access?
If you could upload a backup of your database, or some schema definition , that would be helpful. Even an excel version of your source table might be handy for us to discuss the question.
It sounds to me like maybe you just need a query that retrieves a set of rows from a single table, based on multiple criteria. It may not be as complicated as you expect, using unions and such.
Customer: replied 2 years ago.
MSSQL and this is going to eventually be an SSRS report. I have uploaded a zip file with some supporting documentation. The word document should be particularly helpful. let me know what else I can provide to assist if you need more clarification.
Expert:  Ingo U replied 2 years ago.
Like I said, an actual database/table with sample data would be needed to understand the schema & relationships. I can make some guesses based on the materials provided but not enough to actually write & test a query.
Customer: replied 2 years ago.
so the .bak is just under 1GB, how should i get it to you?thanks
Customer: replied 2 years ago.
https://drive.google.com/file/d/0B_H5fqTr3x5sQzhVYmk0am9lUDQ/view?usp=sharing
Customer: replied 2 years ago.
that should allow you to download a .bak copy of the DB in question for further assistance :)
Expert:  Ingo U replied 2 years ago.
Thanks,
I cannot restore the file on my SQL server 2008R2. File format not recognized . Are you using a newer SQL version?
Customer: replied 2 years ago.
SQLServer 2012 is the version we are running
Expert:  Ingo U replied 2 years ago.
OK let me see if I can get that spun up easily..
Customer: replied 2 years ago.
let me know if I need to do something a different way. if worse comes to worse i can schedule something like a webex to get you access to the SQL box to query what you need if that will help.
Expert:  Ingo U replied 2 years ago.
Thanks, ***** ***** idea - I'm about to find out if my 2012 is operational, I'll let you know what happens.
Customer: replied 2 years ago.
no problem just let me know what I can do assist you if anything :)
Expert:  Ingo U replied 2 years ago.
OK I can see the gigantic database now.
I'm unclear on a couple of things, for starters:
- None of the rows in [VPT$Item Ledger Entry] start with 'MRP' in column [Document No_]
- I don't see columns named
[Lot], [QNTY], [REV] or
[PO],[QTY],[Lot],[vendor]
as described in your question.
For recursive query, there must be some type of parent-child relationship between two columns, I'm guessing from the cryptic word doc that "some parts are made up of other parts".
The word doc talks about other tables, and a "DOCID" field - I get a feeling there are multiple people using variations of terms to talk about the same (or different?) things. For example, is "DOCID" the column [Document No_] ?
Also, the description first talks about pulling
"Lot” “Quantity” and “Revision”, then later about pulling “PO” “Quantity” “Lot Info” “Vendor Name”
How is all this supposed to be combined into a single result set?
Do you have a query for the “Material Pick List Report” mentioned as a reference?
Customer: replied 2 years ago.
here is an example report from Material Pick list and those fields should mostly be in the VPT$Item Ledger Entry table. I am working on getting a copy of the material pick list SSRS report so I can send it to you.
Customer: replied 2 years ago.
here is a copy of the material pick list report for reference as well.
Expert:  Ingo U replied 2 years ago.
Sorry, there is just not enough information here for me to figure this out. I will opt out in the hope that someone else here may be more suitable for this task. My apologies, and good luck to you.
Customer: replied 2 years ago.
ok thanks what do I do now to have this moved to someone else?
Expert:  Ingo U replied 2 years ago.
Sorry, I didn't make that clear - by my opting out your question goes back to the list of open questions that all experts in this category monitor. Our conversation so far, and your download links, are all visible to them as well. You just need to wait to see if anyone else feels up to the task.
Remember, if you change your mind you can always contact customer service and get your deposit back.
Customer: replied 2 years ago.
great thanks, ***** ***** going to go ahead and upload the sample query i wrote with the results of what it returns on the database as well for further guidance. I think that would really help.