Tricky SQL

[tdgulbra]

I have a problem with a complex parent/child relationship. It is many-to-many, which is to say one parent can have many children and one child can have many parents. In addition, a child can be a parent.

I understand the need for an intermediate table to resolve the many-to-many and I am able to write SQL to query this side of the problem. The real issue is when a child becomes a parent.

To use a typical parts database. A sale is made of part A. Part A is comprised of Parts B & C. Part B is comprised of Parts D & E. I need to know the overall cost of Part A. By summing the cost of D & E (which is Part B), plus Part C. The problem is that there are changes made to the ratios of Parts D & E in order to produce Part B. Changes are also made to the ratios of Parts B & C to produce Part A. These changes are preserved in history with effective and expiration dates in the base table.

The question is: Since the cost can vary over time, how do I find the cost of Part A in time? I need to do this with SQL, please don't point me to programming.

 

[tdatgod]

Hi,
SQL doesn't do Part explsions (recursion) very well and therefore unless you know the maximum depth of the explosion it becomes very difficult to do in any database without programming.

However you don't need to do that programming outside of the database with teradata.

There is a PARTS explosion using Stored procedures example on the Teradata Demo CD complete with source code.

you can order a copy of the demo CD from

http://www.teradata.com/dwdemo

you can look at the source code on the CD without haveing to load it I think. If you do need to load it you only need to istall the demo applications and not the whole client/server code.


It produces a TEMP tables and runs in a while loop until all levels of the part have been exploded.

After the stored procedure ends you select the results back from the TEMP table.

 

[BillDHS]

This may not work, but may get you in the right direction:

Select T1.Order#,
((t2.PartPrice * T1.QuantityD)
+ (t3.PartPrice * T1.QuantityE)
+ (t4.PartPrice * T1.QuantityC))as TotalCost

From OrderTable T1, PartPriceTable T2, PartPriceTable T3,
PartPriceTable T4

Where T1.OrderDate Between T2.EffectDate and T2.ExpireDate
and T2.PartNum = D
and T1.OrderDate Between T3.EffectDate and T3.ExpireDate
and T3.PartNum = E
and T1.OrderDate Between T4.EffectDate and T4.ExpireDate
and T4.PartNum = C
;

I did a three table self join to the part price table.

 

[tdgulbra]

Thank you both for the input.

tdatgod: I need to incorporate this into a view to be queried by a third-party tool. I don't think stored procedures will help me.

BillDHS: I like the concept, but my "part" table contains over 3,000 parts. I don't think hard coding partnums is an option. I do have an indicator column in the table telling me whether or not it is a parent and we have set the recursion limit to 10, so I can do that as well. I am having problems making sure I get the proper "mix" for a point in time.

 

[tdatgod]

Hi,
Once you create the stored procedure you access it via normal SQL.


Call GetChildren(15675); /* 15675 is the part number */
Sel sum(count * price ) as (Total)
from PcLevel;

What is the type of input you would get from the third paty tool? Could you build a macro which calls the stored procedure and then selects the answer set back?

for future information if you need it

----------------------------------------

Here is the stored procedure example from the DEMO.....

Replace Procedure GetChildren(Parent Integer)
Begin
Declare Level Integer;
Set Level = 1;
Del from PCLevel All;
Insert Into PcLevel
Sel :Level,Parent,Child,Name
From BofM
Where Parent = arent;
While Activity_Count > 0 Do
Insert Into PCLevel
Sel :Level + 1
,bom.Parent
,bom.Child
,bom.Name
From BofM bom
,PCLevel pcl
Where pcl.Level = :Level
and bom.Parent = pcl.Child;
Set Level = Level+1;
End While;
End;



This is the TEMP table....


CREATE SET GLOBAL TEMPORARY TABLE Manufacturing.PCLevel ,NO FALLBACK ,
NO LOG
(
Level INTEGER NOT NULL,
Parent INTEGER NOT NULL,
Child INTEGER,
Name CHAR(25))
PRIMARY INDEX ( Parent )
ON COMMIT PRESERVE ROWS;




I guess you would also want to include other select columns like number and price and qualify the answer set by effective date.

 

[tdgulbra]

tdatgod,

Thanks, that is an interesting suggestion, but I have some questions.

Would the global temp table be instantiated for each query?
Can I incorporate the Call/Select in a view? What would a "Help View" show on such a view?

 

[tdatgod]

Hi,
The Global temproray table would be filled up for every query and deleted when the user logs off ( or when they run the next query it will be deleted before it starts ).

The query uses the global temp table just so 2 people executing the stored procedure at the same time can store their answers in separate tables.

Stored procedures are like Macros. I don't know of a way to put a MACRO into a view without just expaning the text of the macro and making a view.

Since there is no way to expand the text from the Store procedure I don't know who todo that.

 

[tdatgod]

Hi,
I thought about it for a little while. You could put a view around the global Temporary table however, you still have the problem populating the Temp Table for each query.

---