T-SQL with Recursion – Display top ID on all levels below (To: Kent Milligan)

30 pts.
Tags:
iseries v5r4
Recursion
SQL Query
T-SQL
Hell Kent,

I read through "V5R4 SQL Packs a Punch" http://www-03.ibm.com/systems/resources/systems_i_software_db2_pdf_rcte_olap.pdf   

I would need the query in figure 20 and the result in figure 21 which do not show up in the article. 

I have a query similar to the example (parts of an iSeries Power5 server) that shows all the levels correctly, however, I must display the top level part of the recursion in every line of the result. (In the example there should be a field "Product" filled with "iSeries Power5 server" in every line of the result.). I must be able to distinguish the parts of different products.

Would you be so kind and post the corresponding T-SQL query?

regards arno

 



Software/Hardware used:
45R4

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Hello Cavallino, This is probably not the place to contact Kent Milligan, but the community might still be able to help you with your query. There is no figure 20 nor 21 in the pdf you referenced, so you might want to explain your requirement with as much details as possible. Also, make sure to clarify the database system you are going to run your query on (You mentioned V5R4, but also mentioned T-SQL which is used on Microsoft and Sybase databases). Thanks,
    69,605 pointsBadges:
    report
  • Cavallino
    Hello Carlos, >There is no figure 20 nor 21 in the pdf you referenced ... this is my problem! I would like to know what is in these figures. I did not find any contact to Kent exept this place, sorry. Sorry for confusing V5R4 and T-SQL, I meant SQL-Statements on an iSeries V5R4. arno
    30 pointsBadges:
    report
  • carlosdl
    As I understand from the article, figure 20 would show a query to include the previous level's part code (which looks easy), not the top level's. However, I would recommend posting your current query, so others have a better picture of what you are trying to accomplish whitout needing to open the pdf from the link you provided.
    69,605 pointsBadges:
    report
  • Cavallino
    Hello Carlos, I found that I simply had to add the parent field to the with clause and the two selects. This works perfectly now:
    WITH myrec (mylevel, product, parent, child, data1, number1) as 
    ( 
    SELECT 1 mylevel, parent, parent, child, data1, number1 from dwh.testrec WHERE parent >= 'w'   
    UNION ALL 
    SELECT b.mylevel + 1, b.product, a.parent, a.child, a.data1, a.number1 FROM dwh.testrec a INNER JOIN myrec b ON b.child=a.parent WHERE b.mylevel < 20) 
    SEARCH DEPTH  FIRST BY product, parent, child SET mysort 
    SELECT * FROM myrec ORDER BY mysort 
    
    I am wondering, why I have to mention the fields "product, parent, child" in SEARCH DEPTH to get a tree-view. I might be wrong here but shouldn't be "product" enough as the code loops through the records depending on the "product"? (I double define what I want with "Depth first" and the three fields, but I may be wrong) regards arno
    30 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following