Skip to Content
0
Former Member
Aug 04, 2008 at 10:51 AM

Left,right or Inner Join

16 Views

Dear all,

I have 3 tables (Order Header, Order Lines and Order Progress) all of them are Inner join with each other - Order number being the common between all three tables.

Order header being the primary table I would like to use the join so that if Only date range is selected within the report, the order Numbers are distinctively counted only from the Order headers table.

Similarly if Date range and Account number is selected only Orders from Order header should be counted and when Product Group is selected then only Orders from Order lines should be counted. Similarly when All three options are selected then combination of all orders according to the account number and product group from all three tables should be looked at.

Currently when Only Date range or Date range and account number is selected, orders are being placed onto the report after getting scanned from all three tables and this has resulted in inaccurate results.

Example:

If only date range or date range and account number is selected Crystal counts the results on the Order lines table, counts the results on the Order headers table, add them up and displays them onto the report.

Date Range: 01/06/2008 - 30/06/2008
Account number: 0010065
Product Group: n/a

Report results:

Total Jobs = 5

Days - Jobs - Order Numbers - Counted 
2      -   2        -     1                -    2
3      -   7        -     4                -    2+2+1+2
-------------------------------------------------------------
5      -   9        -     5                -     9
--------------------------------------------------------------

Number of days are coming fine, number of orders coming fine but Number of jobs are counted twice.

SQL QUERY:

 SELECT order_header.order_no, order_header.order_status,
order_header.date_entered, order_header.act_despatch,
ndmas.ndm_name, order_lines.stock_code,
order_header.account_no
FROM   maxmast.order_header order_header,
maxmast.order_lines order_lines, maxmast.ndmas ndmas,
maxmast.slcust slcust
WHERE  (order_header.order_no=order_lines.order_no) AND
(ndmas.ndm_ndcode=slcust.slm_custcode) AND
(order_header.account_no=slcust.slm_custcode) AND
(order_header.date_entered>={ts '2008-06-01 00:00:00'} AND
order_header.date_entered<={ts '2008-06-30 00:00:00'}) AND
order_header.account_no='0010065'

Maybe there is a problem within my formula?

here is the formula code

WhileprintingRecords;

Local DateVar Start := {order_header.date_entered};   // Starting Date
Local DateVar End := {order_header.act_despatch};  // Ending
Date
Local NumberVar Weeks; // Number of weeks
Local NumberVar Days; // Number of days
Local Numbervar Hol:=0; //Number of holidays
Shared DateVar Array Holidays; // Bank holidays dates array
 
Weeks:=(Truncate(End - dayofweek(End) + 1 - (Start -
dayofweek(Start) + 1))/7)*5;
 
Days:=DayOfWeek(end) - Dayofweek(Start) + 1
+ (if dayofweek(start) = 1 then -1 else 0) //adjust for starting on
sunday:
+(if dayofweek(end) = 7 then -1 else 0); //adjust for ending on a
saturday:
 
//Check for bank holidays
Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek (Holidays<i>) in 2 to 6 and
Holidays<i> in Start to End then Hol:=Hol+1 );
 
Weeks + Days - Hol;

Crosstab layout:

Row - above Formula

Summarized field: Count(above formula)

Summarized Field: Count(above formula - Shown percentage).

If Account number, Product Group, Date range is selected:

SQL CODE:

SELECT order_header.order_no, order_header.order_status,
order_header.date_entered, order_header.act_despatch,
ndmas.ndm_name, order_lines.stock_code,
order_header.account_no
FROM   maxmast.order_header order_header,
maxmast.order_lines order_lines, maxmast.ndmas ndmas,
maxmast.slcust slcust
WHERE  (order_header.order_no=order_lines.order_no) AND
(ndmas.ndm_ndcode=slcust.slm_custcode) AND
(order_header.account_no=slcust.slm_custcode) AND
(order_header.date_entered>={ts '2008-06-01 00:00:00'} AND
order_header.date_entered<={ts '2008-06-30 00:00:00'}) AND
order_header.account_no='0113158' AND
order_lines.stock_code>='7-889'

Same query when ran under the old system the results came fine.

Query under old system:

Select Unique Order_header.order_no,date_entered,order_header.account_no
from order_header,order_lines
where date_entered between "01/06/2008" and "30/06/2008"and
order_header.order_no=order_lines.order_no and
order_lines.stock_code[1,5]="7-889"and
order_header.account_no="0113158"

Seems like the SQL query is fine however. It might be my above formula which is not working properly. Need some help here.

Could you please shed some light on what I should be doing as oppose to what I am currently doing?

Many thanks

Regards

Jehanzeb