[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

Delphi 7 :: MS SQL 2005 SQL Statement for Rave Report

Dear Experts,

I have the following tables:
tblPartTypes -> tblParts -> tblPartBOMS (bill of material).

In tblPartTypes I have a field: PrintBOM :Integer (0 = False; 1 = True)
This field is a parameter setting based on the Part Type.
I want to print a list of all parts (tblParts) grouped by part type (tblPartType) and depending on the parameter (field :PrintBOM) it should also print the bill of material (tblPartBOMS) for each part. Is this possible?

In short:
Select all PartTypes and all the Parts linked to a PartType AND if the PartType field, PrintBOMS = TRUE, then also get the PartBOMS for each Part in this particulare PartType grouping.

Hope this is clear, if not please ask and I will try to explain it better.
0
Marius0188
Asked:
Marius0188
  • 6
  • 3
  • 2
  • +2
3 Solutions
 
diniludCommented:
Please explain more
0
 
carcotasuCommented:
You must create ID for each Parent, this ID must be transfer for your Child and so on..

EX:
Parent:  Child: ChildOfChild
1             1           1
1             1           2
1             1           3
2             2           1
2             2           2
.....


when you select get the id for parent when the child have parent 1 and ChildOfChild have Child 1
0
 
Marius0188Author Commented:
On my rave report I need to print the list of parts grouped by there part type.
Report Example:

----------------------------------------------------
Part Type: Front Bumpers (the "PrintBOM" is false for this part type)
---------------------------------------------------
Parts:
1. RM00001
2. RM00002
3. RM00003


----------------------------------------------------
Part Type: Rear Bumpers (the "PrintBOM" is TRUE for this part type)
---------------------------------------------------
Parts:
1. RM00009 BOM (bill of material): BM001; BM002; BM003
2. RM00010 BOM (bill of material): BM004; BM005; BM006
3. RM00011 BOM (bill of material): BM007; BM009; BM010


//REPORT END


Ok what should be happening, let me explain from the reporting:
On the report I need to print all parts, grouped under there respective Part Type (master detail between:
tblPartType -> tblParts. In tblPartTypes, I have a field: PrintBOM, which tells me whether I should print the bill of material, also, for the parts in this group / part type. If this is true then for all the parts in this particular Part Type (or grouping) I need the print the individual part's bill of material, BOM, which I can find in tblPartBOMS (master detail: tblParts -> tblPartBOMS).

I hope this helps. :)

Let me know.

Thanks.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Marius0188Author Commented:
Actually now that I think of it. I will need assistance with the SQL query as well a with the rave report.
0
 
carcotasuCommented:


tblPartTypes must have a unic field (id_pt)
tblParts must have a field "id_pt" all record have a parent in "tblPartsTypes" and unic field (id_p)
tblPartBOMS must have a field "id_p" all record have a parent in "tblParts"


Q.Close ;
Q.SQL.Clear ;
Q.SQL.Append('SELECT * ') ;
Q.SQL.Append('FROM tblPartsTypes a');
Q.SQL.Append('INNER JOIN tblParts b ON a.id_pt=b.id_pt');
Q.SQL.Append('INNER JOIN tblPartBOMS c ON b.id_p=c.id_p');
Q.SQL.Append('WHERE c.fieldX = TRUE) ;
Q.Open ;


This cod it's a demo and you will to adapted to your fields. it's not very hard.
0
 
Marius0188Author Commented:
Hi carcotasu,

Thanks for the code.
Please help me if I am wrong.

But I am looking at a way to extract, WITH ONE QUERY, all PartTypes, Parts AND BOMS (*** ONLY where the PartType.PrintBOM = 1 so it should look something like this in layman's terms:

SELECT PartType.PrintBOM, PartType.PartTypeID, Parts.ItemNumber
FROM tblPartType PartType INNER JOIN tblParts PART ON Part.PartTypeID = PartType.PartTypeID
IF PartType.PrintBOM = 1 THEN
SELECT BOM.BomNumber FROM tblPartBOMS BOM INNER JOIN tblParts PART ON BOM.PartID = PART.PartID

Do not have a clue whether this will work but that is the logic behind it.
I need to only exctract BOMS based on the PrintBOM field value in PartTypes.
If it is flagged 1 the also get the BOMS for each PART else don't.
This all should be in one query OR
if you have any suggestion on how to do this because the ultimate result
is that I need to be able to produce a rave report in Delphi 7 with this functionality.

Maybe there are some functionality in the rave report that can ease this task so please do not let me limit you to the query only, the reason being I am not that familiar with rave report.

Thanks all!
0
 
carcotasuCommented:
SELECT  PartType.PrintBOM, PartType.PartTypeID, Parts.ItemNumber

 IN YOUR CASE ONLY THIS FILED WILL BE VISIBLE, THIS YOU WHANT ? In this case for what you need to create a query with tblPartBOMS if's not visible any fields ?
0
 
imitchieCommented:
// first create the SQL function in the database
// Varchar(200) here is a sample. make it as big as you need the BOM list to be
// SQL 2005 allows Varchar(Max), which is 2GB wide

CREATE FUNCTION dbo.GetBOM(@partID int)
RETURNS varchar(200) AS
BEGIN
DECLARE @bom varchar(200)
SELECT @bom = coalesce(@bom + '; ', '') + BomNumber
FROM tblPartBOMS
WHERE PartID = @partID
return @bom
END
GO

// The Delphi SQL should look like this:

SELECT
 PartType.PrintBOM,
 PartType.PartTypeID,
 Parts.ItemNumber,
 CASE PartType.PrintBOM
  When 1 then dbo.GetPartBOM(PART.PartID)
  Else null END as BOM
FROM tblPartType PartType
 INNER JOIN tblParts PART ON Part.PartTypeID = PartType.PartTypeID
0
 
Marius0188Author Commented:
Mmmh, I don't understand what you mean. :)

Let me explain like this.
I have a master detail and master detail relationship
Master1          Detail1         Master2         Details2
tblPartType     tblParts       tblParts          tblPartBoms

-------------------------------
 tblPartTypes:
-------------------------------
PartTypeID INT PK
TypeCode VARCHAR(10)
Description VARCHAR(35)
PrintBom SMALLINT


-------------------------------
 tblParts:
-------------------------------
PartID INT PK
PartTypeID INT FK
PartNumber VARCHAR(10)
Description VARCHAR(35)


-------------------------------
 tblPartBoms:
-------------------------------
PartBomID INT PK
PartID INT FK
BomCode VARCHAR(10)
Description VARCHAR(35)


1. On my rave report I need to print all the Part Types in tblPartTypes
2. Underneath each part type I want to list all its parts in tblParts (detail section for tblPartTypes).
3. And now the conditional part based on the field value in tblPartTypes.PrintBOM:
    If "PrintBOM" is True for a specific part type then ALSO print the detail section for tblParts which you  
    can find in tblPartBOMS. But if the parameter tblPartType.PrintBOM = false then ignore tblPrintBOMS.


The database structure is actually very simple.
I have part groupings: tblPartTypes.
Each part grouping has several parts contained in it: tblParts
And each part are made up of several smaller parts or boms (bill of material): tblPartBoms.

I need a master detail report that will display / print all PartTypes, tblPartTypes, with there detail data, tblParts. And ONLY WHERE the PrintBom field is true should I display or print the boms, contained in tblPartBoms, for each part, that is containned in tblParts.

But I am really start to think that with my query I would not able to achieve this but rather through my rave report set up. Maybe I am wrong but I am sure with rave report's settings / properties one can achieve this.

0
 
Marius0188Author Commented:
Sorry when I post the last comment imitchie's comment did not display yet there my last comment is relevant to comment id#: 20356440.

But I must say, it looks like imitchie is having the right idea.
Will however still need to check it and test it.

Thanks all.
0
 
developmentguruCommented:
This should be a bit simpler...

select
  pt.PrintBOM,
  pt.PartTypeID,
  p.ItemNumber,
  bom.BOMNumber
from
  tblPartType pt
  inner join tblParts p on p.PartTypeID = pt.PartTypeID
  left outer join tblPartBOMs bom on bom.PartID = p.PartID
0
 
imitchieCommented:
developmentguru:
That doesn't naturally
1) not retrieve bomnumber when printbom is false
2) requires more complicated coding on the Delphi/Rave side to flatten the BOM number, which is trivially solved in SQL
3) requires coding to cater for 3 BOM lines per Part, causing Rave to try to display the data 3 times (default settings)?
0
 
Marius0188Author Commented:
Ok, guys.

The actual solution I found on one of my other posts, not that I am saying none of the one here would not of worked. But in the Rave designer, I have done a check on my master band's OnBeforePrint() event.
I depending on the result of the check I set the detail band's visibility to true or false.
Works great.

See this comment:
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_22992633.html


I will share the points to major contributors.
:)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now