Hi,

I was just wondering if there's a better way to achieve what I'm doing
with SQL - a general standard that avoids memory tables that I'm missing?

I have a master table and a detailed table. To make it easy to imagine,
lets say I have a customer table, and an invoice table.

I want a query that shows me all the customers I have, and I want it to
show also all invoices where amountpaid is null (Outstanding invoices so
to speak).

However - if there are no invoices where amountpaid is null I still want
to see the customer displayed.

Doing the following does not work:

Select C.Customer, I.Invoice
From Customers C
left outer join Invoices I on (I.CustID = C.ID)
where (I.Amountpaid Is null)

It shows:

- Customers where invoices exist and have a null amount in amountpaid
- and also shows customers that have no invoices

But does not show customers that have no invoices assigned against them.

(I'm wanting it to show every customer, but have the invoice number null
in those instances).


The only way I can come up with so far to achieve this is to run the
following:

Select Invoice, CustID
Into Memory\M1
From Invoices
where AmountPaid is null;
Select Customer, Invoice
From Customers C
left outer join Memory\M1 I on (I.CustID = C.ID)

Is this the best way?

Thanks & Regards

Adam.

Adam


>The only way I can come up with so far to achieve this is to run the
>following:
>
>Select Invoice, CustID
>Into Memory\M1
> From Invoices
>where AmountPaid is null;
>Select Customer, Invoice
> From Customers C
>left outer join Memory\M1 I on (I.CustID = C.ID)

If it works that makes it pretty good

I don't have any suitable tables to try on but if your version of DBISAM supports UNION you could try that, you could also try RIGHT OUTER JOIN, again if DBISAM supports it. My antiquated 4.25 does support both. I'd guess UNION would be your best first try. Something along the lines of


Select C.Customer, I.Invoice
From Customers C
left outer join Invoices I on (I.CustID = C.ID)
where (I.Amountpaid Is null)
UNION
SELECT Customer,NULL FROM Customers WHERE ID NOT IN (SELECT CustID FROM Invoices)

As I said nothing suitable here to test with so its a guess

Roy Lambert

Hi Roy,

Good thought with union. I'm trying to avoid memory tables in DBISAM as
much as I possibly can at present if there's a simple alternative.

(Don't get me wrong - they're one of my favourite features), but they're
also the primary reason why it's going to be so difficult to move my
larger applications across to EDB - it's riddled with involved SQL's
that I can't simply port across.

Cheers

Adam.

Adam

>Good thought with union. I'm trying to avoid memory tables in DBISAM as
>much as I possibly can at present if there's a simple alternative.
>
>(Don't get me wrong - they're one of my favourite features), but they're
>also the primary reason why it's going to be so difficult to move my
>larger applications across to EDB - it's riddled with involved SQL's
>that I can't simply port across.

I did start a project ages ago to see if I could automagically convert stuff but it died. You could still use either memory or temporary tables or temporary memory tables to give isolation. The syntax to create has changed a bit but should be automatically convertible and then you just need a subclassed query to handle the DBISAM style scripts.

If you want I can try and dig the project out.

Roy Lambert

Hi Roy,

Thanks. I've taken a look at memory tables in EDB, and for the way that
I use them there's quite a lot involved into converting them over. At
this stage I've resolved that I won't be able to upgrade my applications
any time soon.

(We're talking thousands of SQL queries now in an application where new
live updates are rolled out on a weekly basis to support my existing
clientele, so I know I'm not going to have the chance to do conversions
and testing at this stage).

Thanks for the offer though.

Cheers

Adam.