SQL Expression Field?

[SuperComputing]

OK, I have a pretty extensive report pulling data from 3 different tables, and working perfectly. Now, of course, anytime management sees something good, they want to break it. The only solution that I can see would maybe be a Expression Field, but it's not recognizing any of my syntax.

Here's the scenario:

The report pulls Item Number, Description, Category, Cost, etc... perfectly with the following SQL Query: (I'll abbreviate some...)
SELECT "Items"."ItemNumber", "Items"."Description1", "Items"."Category", "Items"."Subcategory", "Items"."LastCost", "Items"."QtyOnHand", "Items"."QtyCommitted", "Items"."QtyOnBackord", "Items"."QtyOnOrder", "Items"."ActiveDiscontinued", "InventoryMonthly"."Location", "InventoryMonthly"."QtySold", "Forecast"."Current", "Forecast"."Revised", "InventoryMonthly"."YearId"
FROM {oj ("Items" "Items" LEFT OUTER JOIN "Forecast" "Forecast"
ON "Items"."ItemNumber"="Forecast"."ItemNumber")
INNER JOIN "InventoryMonthly" "InventoryMonthly" ON "Items"."ItemNumber"="InventoryMonthly"."ItemNumber"}
WHERE "InventoryMonthly"."Location"='WHS1'

Now, I have sperately developed the following SQL Query on 2 more tables that pulls the exact data I need:

SELECT SUM(PurchLine.QtyOrdered)
FROM PurchOrder
LEFT OUTER JOIN PurchLine
ON PurchLine.PoNumber = PurchOrder.PoNumber
WHERE PurchLine.ItemNumber = 'XXXXXX-XXXX'

So, my problem is, I can't make the report pull the seperate SQL query for each ItemNumber in the report that is already working. Whenever I use the database expert to link the ItemNumber fields together, the report will run forever, just hang up.

Do I have a join wrong? Can I do it through an Expression field? Any ideas on what direction to take?

Thanks in advance!

 

[lbass]

Why are you using a left outer join on PurchLine when you are requiring a specific line item from that table?

Linking to a command will slow your report, because the linking occurs locally. You could try a SQL expression like this:

(
SELECT SUM(QtyOrdered)
FROM PurchOrder A
Inner JOIN PurchLine B
where A.PoNumber = B.PoNumber and
B.ItemNumber = 'XXXXXX-XXXX'
)

Note there should be no table referenced within the summary.

-LB

 

[SuperComputing]

OK, I got the inner/outer join thing, (now) I guess that I didn't analyze the question because I was getting the right answer...

As for the A and B, I will continue to have to use the "." notation since this is pulling from Pervasive SQL and it craps out with that syntax.

Now, as for the Crystal (v10) it doesn't understand anything that I am sending it.

Error in Compiling SQL Expression: Query Engine Error: '42000:[Pervasive][ODBC Client Interface][Pervasive][ODBC Engine Interface][Syntax Error: SELECT(SELECT<<???>>SUM(PurchLine.QtyOrdered)FROM PurchOrder INNER JOIN ..... WHERE .... FROM "PurchLine" "PurchLine", "PurcORder" "PurchOrder"...

It's doubling the SELECT and FROM statements, and I can't make it stop....

 

[lbass]

Yes, of course, you have to add the appropriate punctuation. You should be able to use aliases though.

If you are using a SQL expression, try the following:

(
SELECT SUM("QtyOrdered")
FROM "PurchOrder" A, "PurchLine" B
where A."PoNumber" = B."PoNumber" and
B."ItemNumber" = 'XXXXXX-XXXX'
)

Replacing the 'XXXXXX-XXXX' with the real thing, of course.

I guess I'm wondering why there would be more than one of a specific item number within a PO though. Is it really your intent to sum per PO?

-LB

 

[SuperComputing]

The final result of the completed report should sum all PO's for all item numbers in a given range, but only 90 days out (I will tackle that later). The item qty's are kept in one table (purchline), referenced to another table by po number (purchorder), that table holds the dates.

And yes, each PO may contain a dozen different itemnumbers.

So my result should look like:

Item# Desc Cat Sales QtyOH BackOrd "Qty 90 Days Out"

What I would like is a field that I can drop into the report, on the details row, creating this new "90 days" column.

The thing is, when I put in your code suggestion, with the correct itemnumber I get this, and I don't know how to debug around it:

Crystal Reports

Error in Compiling SQL Expression:
Query Engine Error: '42000:[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Syntax Error: 
SELECT(SELECT<<???>>SUM("QtyOrdered")
FROM "PurchOrder" A, "PurchLine" B
WHERE A."PoNumber" = B."PoNumber" and B."ItemNumber" = 'XXXXXX-XXXX') 
FROM "PurchLine" "PurchLine".
OK

See the doubled SELECT and FROM's? I don't know what is causing this...

 

[lbass]

I really don't think a SQL expression is the solution--SQL expressions can't use parameters and aren't really intended for complex logic. They access data directly from the database and therefore must include all selection criteria, etc.

Since this report is already almost finished, it might be best to insert a subreport that is linked on item number--if you are doing this because you are concerned about row inflation.

-LB