Joining

rbuser

While looking at the sql generated through datasets in the EUReporting tool
I have noticed that when joining two tables the sql doesn't reflect anything
but an inner join. I could select Left Outer Join but the sql will not
change to reflect a left outer join???

nardmoseley

This a limitation of the linking SQL, it always uses an inner join.

One possible work around is to manually edit the sql and then write code to
create the links between the datapipelines.

www.digital-metaphors.com/tips/EditSQLAndLink.zip


--
Nard Moseley
Digital Metaphors Corporation
www.digital-metaphors.com

rbuser

So why is the type of join even an option?

nardmoseley

Outer joins work for queries that are not linked.

Another work around might be to create simple non-joined queries and then
link them. In the link dialog you can specified to include master that do
not have corresponding detail. The link dialog is accessible by
double-clicking on the link or by pressing the right mouse over the detail
dataview window to display the context dialog.

--
Nard Moseley
Digital Metaphors Corporation
www.digital-metaphors.com

rbuser

I have a single dataset that is not linked to any other tables and includes
two tables joined together. I need to left outer join these two tables and
return a result set with every record in the main table that DOES NOT have a
corresponding record in the second table.

The current SQL generated by RB is:

SELECT INVOICE_1.INVOICEID, INVOICE_1.CUSTNBR,
INVOICE_1.SHOWID, INVOICE_1.INVOICEDATE,
INVOICE_1.TOTALAMOUNT,
INVOICE_1.DATECREATED, INVOICE_1.BALANCE,
INVOICE_1.DATEPACKED,
SHIPTRACKING_1.TRACKINGNBR,
INVOICE_1.PACKEDBY,
Cast(Cast((CAST('TODAY' AS DATE) - Cast(Cast(Extract(Month from
DateCreated) as Char(2))||'/'||Cast(Extract(Day from DateCreated) as
Char(2))||'/'||Cast(Extract(Year from DateCreated) as Char(4))as Date)) as
Char(6)) as Integer) Cast_Cast_CAST_AS_DATE_Ca
FROM INVOICE INVOICE_1
, SHIPTRACKING SHIPTRACKING_1
WHERE
(SHIPTRACKING_1.INVOICEID = INVOICE_1.INVOICEID)
AND ((
( SHIPTRACKING_1.TRACKINGNBR IS NULL )
OR ( SHIPTRACKING_1.TRACKINGNBR = '' )))


What I need is the following:

SELECT INVOICE_1.INVOICEID, INVOICE_1.CUSTNBR,
INVOICE_1.SHOWID, INVOICE_1.INVOICEDATE,
INVOICE_1.TOTALAMOUNT,
INVOICE_1.DATECREATED, INVOICE_1.BALANCE,
INVOICE_1.DATEPACKED,
SHIPTRACKING_1.TRACKINGNBR,
INVOICE_1.PACKEDBY,
Cast(Cast((CAST('TODAY' AS DATE) - Cast(Cast(Extract(Month from
DateCreated) as Char(2))||'/'||Cast(Extract(Day from DateCreated) as
Char(2))||'/'||Cast(Extract(Year from DateCreated) as Char(4))as Date)) as
Char(6)) as Integer) Cast_Cast_CAST_AS_DATE_Ca
FROM INVOICE INVOICE_1
Left Outer Join SHIPTRACKING SHIPTRACKING_1 on SHIPTRACKING_1.INVOICEID =
INVOICE_1.INVOICEID
WHERE ( SHIPTRACKING_1.TRACKINGNBR IS NULL )
OR ( SHIPTRACKING_1.TRACKINGNBR = '' )

The SQL statement must be an outer join to return ALL records of the main
table and null values for the fields from the joined table that don't exist
so that I can tell it to return just those where there isn't a corresponding
join record.

I can't create my own SQL statement because I nee to be able to use the
autosearch dialog.

Thank You,
Branden Johnson

nardmoseley

Check the Designer.DataSettings for DatabaseType and SQLType. Make sure that
these settings reflect the database engine that you are using. Left outer
join syntax varies by database product. However, it looks like from your
example that you are trying to generate SQL2 syntax (i.e. ANSI-92 syntax).
Database products such as SQL Server and MS Access support this type of
syntax.

If that does not help, let me know what database product you are using and
what connectivity components (i.e. BDE, ADO, etc).

--
Nard Moseley
Digital Metaphors Corporation
www.digital-metaphors.com

rbuser

Thank You!!!
The designer was set to SQL1 instead of SQL2.