Joins need one little tweak that I can't figure out

tmcarstens · #1 (**permalink**) 03-25-10, 17:20

I have the following SQL generated by CrystalReports.

Code:
SELECT "JrnlHdr"."Reference", "JrnlHdr"."JrnlKey_Journal", "JrnlHdr"."PostOrder", "JrnlHdr"."MainAmount", "JrnlHdr"."AmountPaid", "JrnlHdr"."DateDue", "JrnlHdr"."TransactionDate", "Customers"."CustomerID", "Customers"."Customer_Bill_Name", "Customers"."Phone_Number", "Contacts"."LastName", "Contacts"."FirstName", "Contacts"."IsPrimaryContact"
FROM {oj (("JrnlRow" "JrnlRow" LEFT OUTER JOIN "JrnlHdr" "JrnlHdr" ON "JrnlRow"."PostOrder"="JrnlHdr"."PostOrder") LEFT OUTER JOIN "Customers" "Customers" ON "JrnlRow"."CustomerRecordNumber"="Customers"."Cust omerRecordNumber") INNER JOIN "Contacts" "Contacts" ON "Customers"."CustomerRecordNumber"="Contacts"."Cus tomerRecord"}
WHERE "JrnlHdr"."TransactionDate"<={d '2010-03-04'} AND "JrnlHdr"."JrnlKey_Journal"=3 AND "Contacts"."IsPrimaryContact"=1
ORDER BY "Customers"."CustomerID", "JrnlHdr"."PostOrder"

What I'd like to do is to remove clients who only have invoices pending that are younger than 1 month. I do want to show invoices with companies that have pending ones older than 1 month and it should include the invoices younger than one month for them!

JrnlHdr is invoice info. JrnlRow are the rows in the invoices. The rest should be self explanatory.

Thanks for any help or at least a direction to push me into Smile

dav1mo · #2 (**permalink**) 03-26-10, 11:04

add in an exists subselect to check for more than 1 month old

Dave

tmcarstens · #3 (**permalink**) 03-30-10, 11:51

I still have to keep the invoices that are young (younger than 1 month) on those customers that owe money on invoices older than a month.

That would cut these out.

dav1mo · #4 (**permalink**) 03-30-10, 12:45

no, the exists subselect would just verify that a customer owed money an an invoice more than 1 month old, the SQL in main part of query would join the tables to show you all info for all invoices.
Dave

tmcarstens · #5 (**permalink**) 03-30-10, 13:09

Is there a direction you can point me in. A starting point would do.

Having trouble wrapping my head around it.

dav1mo · #6 (**permalink**) 03-30-10, 17:11

Code:

select some_cols
   from some_tables
where some conditions = some other conditions
   and exists ( select 1 from some tables
                    where open invoice date <= current date - a month)

Dave

tmcarstens · #7 (**permalink**) 04-19-10, 17:38

Tried and I get a SELECT SELECT error from Crystal Reports. Here's an image. If you don't have a quick SQL related (and not Crystal) solution then I'll ask some crystal people.

http://i41.tinypic.com/23vf5zs.png

dav1mo · #8 (**permalink**) 04-20-10, 11:26

how about showing the sql that was generated in Crystal?
Dave

tmcarstens · #9 (**permalink**) 04-20-10, 11:35

I used the following. You can run a normal SQL query without Crystal doing anything.

SELECT "JrnlHdr"."Reference", "JrnlHdr"."JrnlKey_Journal", "JrnlHdr"."PostOrder", "JrnlHdr"."MainAmount", "JrnlHdr"."AmountPaid", "JrnlHdr"."DateDue", "JrnlHdr"."TransactionDate", "Customers"."CustomerID", "Customers"."Customer_Bill_Name", "Customers"."Phone_Number", "Contacts"."LastName", "Contacts"."FirstName", "Contacts"."IsPrimaryContact"
FROM {oj (("JrnlRow" "JrnlRow" LEFT OUTER JOIN "JrnlHdr" "JrnlHdr" ON "JrnlRow"."PostOrder"="JrnlHdr"."PostOrder") LEFT OUTER JOIN "Customers" "Customers" ON "JrnlRow"."CustomerRecordNumber"="Customers"."Cust omerRecordNumber") INNER JOIN "Contacts" "Contacts" ON "Customers"."CustomerRecordNumber"="Contacts"."Cus tomerRecord"}
WHERE "JrnlHdr"."TransactionDate"<=CURRENT_DATE AND "JrnlHdr"."JrnlKey_Journal"=3 AND "Contacts"."IsPrimaryContact"=1
AND EXISTS (SELECT "JrnlHdr"."Reference", "JrnlHdr"."JrnlKey_Journal", "JrnlHdr"."PostOrder", "JrnlHdr"."MainAmount", "JrnlHdr"."AmountPaid", "JrnlHdr"."DateDue", "JrnlHdr"."TransactionDate", "Customers"."CustomerID", "Customers"."Customer_Bill_Name", "Customers"."Phone_Number", "Contacts"."LastName", "Contacts"."FirstName", "Contacts"."IsPrimaryContact"
FROM {oj (("JrnlRow" "JrnlRow" LEFT OUTER JOIN "JrnlHdr" "JrnlHdr" ON "JrnlRow"."PostOrder"="JrnlHdr"."PostOrder") LEFT OUTER JOIN "Customers" "Customers" ON "JrnlRow"."CustomerRecordNumber"="Customers"."Cust omerRecordNumber") INNER JOIN "Contacts" "Contacts" ON "Customers"."CustomerRecordNumber"="Contacts"."Cus tomerRecord"}
WHERE "JrnlHdr"."TransactionDate"<=CURRENT_DATE AND ("JrnlHdr"."DateDue" = CURRENT_DATE + INTERVAL '31' DAY ) )
ORDER BY "Customers"."CustomerID", "JrnlHdr"."PostOrder"

dav1mo · #10 (**permalink**) 04-20-10, 14:40

Ok, I really do not have the time to go through this jumbled mess, you should really format your SQL statement and use CODE tags so that it is readable to all of us. A couple of quick items I see is that the LEFT OUTER JOINS are not needed as you turn them back to INNER joins with your WHERE clause. Also, the EXISTS subselect should not be your entire query, it should just be checking to see if the order is more than 1 month old (by the way, aren't you checking to see if its due a month from now??? do you want 31 days in future or past?) and just SELECT 1 in there as the data is not used for anything. With that high level look I didn't see anything that stood out as being the cause of your error.
Dave

tmcarstens · #11 (**permalink**) 04-20-10, 15:05

Sorry about the 'mess'. It was output SQL that I got from Crystal. I'll modify it by your suggestions and throw the SQL back in here. Wasn't aware of the <code> tag on this forum.

Thanks for your patience!

guelphdad · #12 (**permalink**) 04-20-10, 16:40

Use a formatter like:
Instant SQL Formatter

Internet Services

Web Development

Digital Marketing

Consumer Tech