Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    7

    Question Unanswered: Zull statement - left outer join problem

    using crystal 2008
    haveing prolbems with a left outerjoin to get a blank field(null) to show up.
    basically i have some po line have have long descriptions or text fields that i want to include with the report. Below is my sql statement. I have written a formula with a join between 2 fields to get the report to work- this does but- it leave the polines off that do not have a long description. I have tried left outer joins but seem to be having problems. The long description field where the data is actually at is under my po.line table, although serveral area have long description fields -
    *one note i had to add a duplicate table to the field to get some items in my header to work properly - not sure if this is effecting the report or not.



    OK- when I set up the link to a LO join enforce from = to
    and dont add the ldtext.field to the report the left outjoin works(but no long description)and all my line items show up.
    1. when I keep the same join and add either just the ld.text field to the report it comes back with a error saying invalid table
    2. when i use my formula that joins the ld.text and po.line and place it in the report with the same left outerjoin i get the same failure.
    3. when i dont reference the ldtext(field) at all in the report it works fine w/ the lo join
    4. when i Dont use a left outer join and use my formula(see below)
    if not isnull(({LONGDESCRIPTION.LDTEXT }))
    then {POLINE.DESCRIPTION}+{LONGDESCRIPTION.LDTEXT}
    else {POLINE.DESCRIPTION}

    and link from poline.ld
    to ld.ldtext - my formula works and populates the po.line and the ld.text together - except that any po.line with no description will not show up. Like its not there.

    Not sure what to do?
    here is my current sql statement with the longdescription field in the select statement

    here is the error statement i am getting:
    Failed to retrieve data from the database:
    Details:42000[intersolv][odb sql base driver][sql base] 00906 itn invalid table name[database vendor code:906]

    sql statement:
    SELECT "PO"."PONUM", "PO"."STATUS", "PO"."VENDOR",
    "COMPANIES"."NAME", "COMPANIES"."ADDRESS1",
    "COMPANIES"."ADDRESS2", "COMPANIES"."ADDRESS3",
    "COMPANIES"."ADDRESS4", "COMPANIES"."PHONE",
    "COMPANIES"."FAX", "COMPANIES_1"."NAME",
    "COMPANIES_1"."ADDRESS1", "COMPANIES_1"."ADDRESS2",
    "COMPANIES_1"."ADDRESS3", "COMPANIES_1"."ADDRESS4",
    "COMPANIES_1"."CONTACT", "COMPANIES_1"."PHONE",
    "COMPANIES_1"."FAX", "PO"."PURCHASEAGENT",
    "PO"."ORDERDATE",
    "PO"."REQUIREDDATE", "PO"."PAYMENTTERMS",
    "PO"."SHIPVIA", "PO"."FREIGHTTERMS", "PO"."FOB",
    "POLINE"."DESCRIPTION", "POLINE"."ITEMNUM",
    "POLINE"."ORDERQTY", "POLINE"."UNITCOST",
    "POLINE"."LOADEDCOST", "POLINE"."POLINENUM",
    "PO"."SHIPTOATTN", "LONGDESCRIPTION"."LDTEXT"
    FROM ("MAXIMO"."PO" "PO" LEFT OUTER JOIN
    "MAXIMO"."LONGDESCRIPTION" "LONGDESCRIPTION" ON "PO"."LDKEY"="LONGDESCRIPTION"."LDKEY"),
    "MAXIMO"."POLINE" "POLINE",
    "MAXIMO"."COMPANIES" "COMPANIES_1",
    "MAXIMO"."COMPANIES" "COMPANIES"
    WHERE ("PO"."PONUM"="POLINE"."PONUM") AND ("PO"."VENDOR"="COMPANIES_1"."COMPANY") AND ("PO"."SHIPTO"="COMPANIES"."COMPANY") AND "PO"."PONUM"='3386-053'

  2. #2
    Join Date
    Feb 2009
    Posts
    7
    turns out my odbc connection was old - once updated my left outer joined worked

Posting Permissions

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