HotRod40
asked on
SQL
Hi,
Could anyone please help!
I want to connect 4 tables together to allow for access to information in other records.
I need to be able to look for the criteria of:
'If the 'PreInvoiceDate' field is less than or equal to a system date and for the 'InvoicedId' field to be blank.
The above works OK and retrieves a records information until I add the table named 'INVOICED' and the below information into the SQL query (the or statement):
'If the field InvoiceID is not blank (ie a record exists) then check for the 'FirstInvoice' field to be blank
If I add the above code then no information appears at all.
Whats wrong?
Here is the full code:
SELECT *
FROM CUSTOMER cu,
CONTRACT c,
FEEINFO f,
INVOICED i /*Problem Here*/
WHERE c.'contract number' = cu.'contract number' and
c.'contract number' = f.'contractid' and
c.'contract number' = i.'contractid' and /*Problem Here*/
f.'preinvoicedate' <= '08/28/2002' and
(f.'invoicedid' is null or (f.'invoicedid' is not null and i.'firstinvoice' is null)) /*Problem Here*/
Many Thanks in anticipation
Scott
Could anyone please help!
I want to connect 4 tables together to allow for access to information in other records.
I need to be able to look for the criteria of:
'If the 'PreInvoiceDate' field is less than or equal to a system date and for the 'InvoicedId' field to be blank.
The above works OK and retrieves a records information until I add the table named 'INVOICED' and the below information into the SQL query (the or statement):
'If the field InvoiceID is not blank (ie a record exists) then check for the 'FirstInvoice' field to be blank
If I add the above code then no information appears at all.
Whats wrong?
Here is the full code:
SELECT *
FROM CUSTOMER cu,
CONTRACT c,
FEEINFO f,
INVOICED i /*Problem Here*/
WHERE c.'contract number' = cu.'contract number' and
c.'contract number' = f.'contractid' and
c.'contract number' = i.'contractid' and /*Problem Here*/
f.'preinvoicedate' <= '08/28/2002' and
(f.'invoicedid' is null or (f.'invoicedid' is not null and i.'firstinvoice' is null)) /*Problem Here*/
Many Thanks in anticipation
Scott
ASKER
Thanks for that it does make things more clear except for one problem.
I receive a Type Mismatch error and I think it is because of the following:
TABLE FIELDNAME TYPE
FeeInfo InvoicedID Auto (+)
Invoiced InvoicedID String (A)
Any more suggestions?
Thanks Again..
I receive a Type Mismatch error and I think it is because of the following:
TABLE FIELDNAME TYPE
FeeInfo InvoicedID Auto (+)
Invoiced InvoicedID String (A)
Any more suggestions?
Thanks Again..
Change the field definition in the Invoiced table into long integer.
BTW what database are you using?
Nic;o)
BTW what database are you using?
Nic;o)
ASKER
I need an auto gen number to be created in the Invoiced table.
I presume that this will no longer happen??
And will the system recognize a long int and a string to be the same??
My database is Paradox. The dbase that comes with Delphi5
Thanks
I presume that this will no longer happen??
And will the system recognize a long int and a string to be the same??
My database is Paradox. The dbase that comes with Delphi5
Thanks
I'm a bit puzzled:
>I need an auto gen number to be created in the Invoiced table.
According to your comment above it's a string. Normally an autounumber is numeric (In MS Access it's format is a long integer)
But if Paradox accepts a string, then you need to make the FeeInfo "foreign key" also string to prevent the type-mismatch.
Nic;o)
>I need an auto gen number to be created in the Invoiced table.
According to your comment above it's a string. Normally an autounumber is numeric (In MS Access it's format is a long integer)
But if Paradox accepts a string, then you need to make the FeeInfo "foreign key" also string to prevent the type-mismatch.
Nic;o)
ASKER
Thanks I have changed the Data types.
The problem seemd to be when I include the Invoiced table to my query. This table is empty by the way.
As soon as I include this table, my records previously found are no longer retrieved?
Weird???
The problem seemd to be when I include the Invoiced table to my query. This table is empty by the way.
As soon as I include this table, my records previously found are no longer retrieved?
Weird???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT *
FROM CUSTOMER cu,
CONTRACT c,
FEEINFO f,
INVOICED i /*Problem Here*/
WHERE c.'contract number' = cu.'contract number' and
c.'contract number' = f.'contractid' and
c.'contract number' = i.'contractid' and /*Problem Here*/
f.'preinvoicedate' <= '08/28/2002' and
(f.'invoicedid' NOT IN (SELECT invoicedid FROM INVOICED WHERE firstinvoice not is null) )
This adds a NOT IN the set of "wrong" ID's with a first invoice filled.
Getting the idea ?
Nic;o)