Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2010
    Posts
    8

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

    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    add in an exists subselect to check for more than 1 month old

    Dave

  3. #3
    Join Date
    Mar 2010
    Posts
    8
    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.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  5. #5
    Join Date
    Mar 2010
    Posts
    8
    Is there a direction you can point me in. A starting point would do.

    Having trouble wrapping my head around it.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  7. #7
    Join Date
    Mar 2010
    Posts
    8
    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

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how about showing the sql that was generated in Crystal?
    Dave

  9. #9
    Join Date
    Mar 2010
    Posts
    8
    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"

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  11. #11
    Join Date
    Mar 2010
    Posts
    8
    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!

  12. #12
    Join Date
    Mar 2004
    Posts
    480
    Use a formatter like:
    Instant SQL Formatter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •