DB2 & LotusApproach

[andrdoba]

Anyone has any experience with connecting to DB2 tables using Lotus Approach? I know how to connect and write SQL when using only one table.
I need an advice how to make this SQL work in Approach (LotusScript). Joining table causes me great dificulties. HELP!
FROM From.Invoice, From.Inv_Item_Account WHERE (Invoice.Inv_Id=Inv_Item_Account.Inv_Id) AND (Invoice.Inv_Dt='2002-04-04')

 

[blom0344]

Hello andrdoba,

You are probably looking for the correct syntax of DB2 SQL.

In your case it will be something like:

SELECT *
FROM Invoice,Inv_Item_Account
WHERE
(Invoice.Inv_Id=Inv_Item_Account.Inv_Id) AND (Invoice.Inv_Dt='2002-04-04')

you can change the '*' with the appropiate fields you want in the output.

This statement results in an inner join,which means you only get rows with matching values in both tables.
*****************************************************
If you want all from Invoice and matching rows from Inv_Item_Account:

SELECT *
FROM Invoice LEFT OUTER JOIN Inv_Item_Account
ON Invoice.Inv_Id=Inv_Item_Account.Inv_Id
WHERE
Invoice.Inv_Dt='2002-04-04'
****************************************************
If you want all from Inv_Item_Account and matching rows from Invoice:

SELECT *
FROM Invoice RIGHT OUTER JOIN Inv_Item_Account
ON Invoice.Inv_Id=Inv_Item_Account.Inv_Id
WHERE
Invoice.Inv_Dt='2002-04-04'

***************************************************
If you want a FULL OUTER JOIN:

SELECT *
FROM Invoice FULL OUTER JOIN Inv_Item_Account
ON Invoice.Inv_Id=Inv_Item_Account.Inv_Id
WHERE
Invoice.Inv_Dt='2002-04-04'

T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com