Check records which aren't in other table

rbuser

Hello,

I have the following SQL script:

SELECT TRAZAB_1.TrFch, TRAZAB_1.TrTur,
TRAZAB_1.TrLin, TRAZAB_1.TrTip,
TRAZAB_1.TrCli, TRAZAB_1.TrArt,
TRAZAB_1.TrCnt, ORDFABDET_1.OFCNT, ORDFABDET_1.OFNMR,
ORDFABDET_1.OFORD
FROM TRAZAB TRAZAB_1
LEFT OUTER JOIN ORDFABCAB ORDFABCAB_1 ON
(ORDFABCAB_1.OfFch = TRAZAB_1.TrFch)
AND (ORDFABCAB_1.OfTur = TRAZAB_1.TrTur)
AND (ORDFABCAB_1.OfLin = TRAZAB_1.TrLin)
AND (ORDFABCAB_1.OfTip = TRAZAB_1.TrTip)
AND (ORDFABCAB_1.OfCli = TRAZAB_1.TrCli)
LEFT OUTER JOIN ORDFABDET ORDFABDET_1 ON
(ORDFABDET_1.OfNmr = ORDFABCAB_1.OfNmr)
AND (ORDFABDET_1.OfART = TRAZAB_1.TRART)
WHERE ( TRAZAB_1.TrFch = '01/02/2006' )
AND ( TRAZAB_1.TrTur = 'M' )
AND ( TRAZAB_1.TrLin = 1 )
AND ( TRAZAB_1.TrTip = 'C' )
AND ( ORDFABDET_1.OfCnt IS NULL )
ORDER BY TRAZAB_1.TrArt

The important part is the second join:

LEFT OUTER JOIN ORDFABDET ORDFABDET_1 ON
(ORDFABDET_1.OfNmr = ORDFABCAB_1.OfNmr)
AND (ORDFABDET_1.OfART = TRAZAB_1.TRART)

Where it is joined third table with second (ORDFABDET_1 with ORDFABCAB1) and
also third table with first ( ORDFABDET_1 with TRAZAB_1).

I need to get the records in table 1 which aren't in tables 2 and 3 (A
master - detail ).

How can I join third table with second and also with first table?

I have two problems:

1.- When I select the join fields from table 2, if I change to table 1 and
join the fields the join between table 3 and 2 is not set.

2.- I have join only 3 to 2 and some data doesn't appear which should
appear.

I know that I can introduce the sentence SQL manually but in that case I
lost the feature of search.

Is there a solution for that?

Thanks,

Jose Maria Sanmartin

nicocizik

Hi Jose,


Unfortunately this scenareo is not currently supported from within the data
workspace. One option would be to alter the SQL manually and set up your
search criteria in code. I would recommend using the SQLBuilder object to
do this. See the TdaSQLBuilder topic in the ReportBuilder Help file.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Thanks.

Only a question. Is it possible to change SQLBuilder object with RAP?

Regards,

Jose Maria Sanmartin

nicocizik

Hi Jose,


Yes, the SQLBuilder object is available in RAP.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Thanks Nico. I will try through RAP.

nicocizik

Hi Jose,

No problem. Take a look at the TdaSQLBuilder topic in the ReportBuilder
help file for a description and example code of the object. I would
recommend first getting this working in Delphi, then moving it to RAP.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,

In the procedure globaloncreate event I have put:


var
lSqlBuilder: TdaSqlBuilder;
begin
lSQLBuilder := TdaSQLBuilder.Create(Report.DataPipeline);
lSQLBuilder.Clear;

lSQLBuilder.SelectTables.Add('TRAZAB');
lSQLBuilder.SelectTables.AddLeftOuterJoin('ORDFABCAB','TRAZAB','OfFch,OfTur,OfLin,OfTip,OfCli','=,=,=,=,=','TrFch,TrTur,TrLin,TrTip,TrCli');
lSQLBuilder.SelectTables.AddLeftOuterJoin('ORDFABDET','ORDFABCAB','Ofnmr,OfArt','=,=','OfNmr,TrArt');
lSQLBuilder.SelectFields.AddAllFields;

lSQLBuilder.SearchCriteria.AddAutoSearch('TRAZAB', 'TrFch', '=',
Report.AutoSearchFields[0].SearchExpression);
lSQLBuilder.SearchCriteria.AddAutoSearch('TRAZAB', 'TrTur', '=',
Report.AutoSearchFields[1].SearchExpression);
lSQLBuilder.SearchCriteria.AddAutoSearch('TRAZAB', 'TrLin', '=',
Report.AutoSearchFields[2].SearchExpression);
lSQLBuilder.SearchCriteria.AddAutoSearch('TRAZAB', 'TrTip', '=',
Report.AutoSearchFields[3].SearchExpression);
lSQLBuilder.SearchCriteria.AddAutoSearch('ORDFABDET', 'OfCnt', 'IS
NULL','');


lSQLBuilder.ApplyUpdates;

lSQLBuilder.Free;

end;

But it seems that the SQL only gets
LEFT OUTER JOIN ORDFABDET ORDFABDET_1 ON
(ORDFABDET_1.OfNmr = ORDFABCAB_1.OfNmr)

instead of
LEFT OUTER JOIN ORDFABDET ORDFABDET_1 ON
(ORDFABDET_1.OfNmr = ORDFABCAB_1.OfNmr)
AND (ORDFABDET_1.OfART = TRAZAB_1.TRART)

although I have put
lSQLBuilder.SelectTables.AddLeftOuterJoin('ORDFABDET','ORDFABCAB','Ofnmr,OfArt','=,=','OfNmr,TrArt');

OfNmr is from ORDFABDET and TrArt is from TRAZAB

How can I see the SQL data generated?

Is it possible to generate
LEFT OUTER JOIN ORDFABDET ORDFABDET_1 ON
(ORDFABDET_1.OfNmr = ORDFABCAB_1.OfNmr)
AND (ORDFABDET_1.OfART = TRAZAB_1.TRART)

with lSQLBuilder.SelectTables.AddLeftOuterJoin?

Thanks,

Jose Maria Sanmartin

rbuser

I meant the SQL script: SELECT ... FROM...

Thanks

rbuser

Solved with the following event

procedure globaloncreate:
var
lSqlBuilder: TdaSqlBuilder;
i: Integer;
SentenciaSQL: String;
begin
lSQLBuilder := TdaSQLBuilder.Create(Report.DataPipeline);
lSQLBuilder.SelectTables.Clear;

lSQLBuilder.SelectTables.Add('TRAZAB');
lSQLBuilder.SelectTables.AddLeftOuterJoin('ORDFABCAB','TRAZAB','OfFch,OfTur,OfLin,OfTip,OfCli','=,=,=,=,=','TrFch,TrTur,TrLin,TrTip,TrCli');
lSQLBuilder.SelectTables.AddLeftOuterJoin('ORDFABDET','ORDFABCAB','OfArt,Ofnmr','=,=','TrArt,OfNmr');

SentenciaSql := copy ( lSqlBuilder.Sql.SqlText.Text , 1 , 545 ) + '
AND (ORDFABDET_1.OfART = TRAZAB_1.TRART) ' +
copy ( lSqlBuilder.Sql.SqlText.Text , 546 , 1000 ) ;

lSQLBuilder.Sql.EditSQLAsText := True;
lSqlBuilder.Sql.SqlText.Text := SentenciaSQL;

lSQLBuilder.ApplyUpdates;

lSQLBuilder.Free;

end;

I have inserted a new line to join table third with table second.

Thanks for your support

Jose Maria Sanmartin

nicocizik

Hi Jose,

Excellent! Glad you got it working.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com