Null statement part deux(repost due to margin width error)

[max274]

sorry this is a repost of the last reply of my previous thread which somehow has messed up.



SELECT "PR"."PRNUM", "PR"."ISSUEDATE", "PR"."REQUIREDDATE",
"COMPANIES"."COMPANY", "COMPANIES"."ADDRESS1",
"COMPANIES"."ADDRESS2",
"COMPANIES"."ADDRESS3", "COMPANIES"."ADDRESS4",
"COMPANIES_1"."NAME",
"COMPANIES_1"."ADDRESS1", "COMPANIES_1"."ADDRESS2",
"COMPANIES_1"."ADDRESS3",
"COMPANIES_1"."ADDRESS4", "COMPANIES_1"."CONTACT",
"COMPANIES_1"."PHONE"
FROM "MAXIMO"."PR" "PR", "MAXIMO"."COMPANIES" "COMPANIES_1",
"MAXIMO"."COMPANIES" "COMPANIES"
WHERE ("PR"."VENDOR"="COMPANIES_1"."COMPANY") AND ("PR"."SHIPTO"="COMPANIES"."COMPANY") AND "PR"."PRNUM"='25289'

 

[max274]

see thread:
thread149-1537153

 

[IanWaterman]

Could not reply

The problem is because you are leading from the DESCRIPTION table. If there is no data in that table then records will be eliminated.

In database expert => Links

Make the first table PR and then join other tables from it.
DESCRIPTION must be a left outer from the PR table.

Ian

 

[max274]

sorry i posted a similar reports sql statement the 2nd time- here is the ORIGINAL - as you can see I tried the join but I still get a failure.....


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'

 

[IanWaterman]

in
"PO"."LDKEY">="LONGDESCRIPTION"."LDKEY"

I have Never used a >= in a join, try changing to =

YOu also have a strange mix of Oracle and ANSI syntax for the joining of tables. Is this the Crystal generated SQL?

What is your DB?

I can not see anything wrong, unless you are doing some more filtering on the report which is not being parsed to the Database.

Ian

 

[max274]

YES THIS IS A CRYSTAL GENERATED SQL
MY DATABASE IS A PROGRAM CALLED MAXIMO(IS THAT WHAT YOU MEAN)
NOT SURE WHAT YOU MEAN - i DID BRING 1 TABLE IN TWICE TO THE REPORT TO GET THE DATA IN THE HEADER

i HAVE TRIED THE LEFT OUTER JOIN FROM PO.LDKEY TO POLINEDESCRIPTION.LDKEY. (MAKE = AND ENFORCE FROM)IT WORKS SOMEWHAT.
THE PO LINE THAT WAS MISSING IS NOW SHOWING UP, BUT THERE IS NO LONG DESCRITION?
HERE IS THE NEW 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"."ITEMNUM",
"POLINE"."ORDERQTY", "POLINE"."UNITCOST",
"POLINE"."LOADEDCOST", "POLINE"."POLINENUM",
"PO"."SHIPTOATTN", "POLINE"."DESCRIPTION"
FROM "MAXIMO"."PO" "PO", "MAXIMO"."COMPANIES"
"COMPANIES_1", "MAXIMO"."COMPANIES" "COMPANIES",
"MAXIMO"."POLINE" "POLINE"
WHERE ("PO"."VENDOR"="COMPANIES_1"."COMPANY") AND ("PO"."SHIPTO"="COMPANIES"."COMPANY") AND
("PO"."PONUM"="POLINE"."PONUM") AND
"PO"."PONUM"='3386-053'

 

[IanWaterman]

YOur query does not include the LongDescription table?

Ian

 

[max274]

Thank you.