Solved

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

Posted on 2007-11-26
13
579 Views
Last Modified: 2013-11-23
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
Comment
Question by:Marius0188
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 10

Expert Comment

by:dinilud
ID: 20356062
Please explain more
0
 
LVL 1

Expert Comment

by:carcotasu
ID: 20356178
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
 

Author Comment

by:Marius0188
ID: 20356196
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
 

Author Comment

by:Marius0188
ID: 20356202
Actually now that I think of it. I will need assistance with the SQL query as well a with the rave report.
0
 
LVL 1

Expert Comment

by:carcotasu
ID: 20356298


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
 

Author Comment

by:Marius0188
ID: 20356345
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 1

Assisted Solution

by:carcotasu
carcotasu earned 100 total points
ID: 20356440
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
 
LVL 25

Accepted Solution

by:
imitchie earned 300 total points
ID: 20356683
// 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
 

Author Comment

by:Marius0188
ID: 20356704
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
 

Author Comment

by:Marius0188
ID: 20356721
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
 
LVL 21

Assisted Solution

by:developmentguru
developmentguru earned 100 total points
ID: 20357586
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20360701
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
 

Author Comment

by:Marius0188
ID: 20409455
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now