Maybe try to create SQL views that mimic the OFBiz view entities.
Walter Vaughan wrote:
Okay, I'll admit my SQL skill are pre-newbie, but I'm kinda at a loss.
I'm trying to build some reports, and I am begging for some direction.
The following query works, but I need to add more columns...
SELECT
"party_group"."group_name" AS "Company_Name"
max("party_classification"."party_classification_group_id") as
"Discount Type",
sum("order_item"."unit_price")as "Discounted Amount",
sum("order_item"."unit_list_price")as "Retail Amount",
count( distinct "order_header"."order_id")as "Number Of Orders"
FROM
"party" INNER JOIN "party_classification" ON "party"."party_id" =
"party_classification"."party_id"
INNER JOIN "order_header" ON "party"."party_id" =
"order_header"."bill_to_party_id"
INNER JOIN "party_group" ON "party"."party_id" =
"party_group"."party_id"
INNER JOIN "order_item" ON "order_header"."order_id" =
"order_item"."order_id"
WHERE
party_classification_group_id LIKE 'DEALER%'
AND "order_item"."status_id" = 'ITEM_COMPLETED'
GROUP BY
"party_group"."group_name"
ORDER BY
"party_group"."group_name" ASC
I need to add in columns like phone numbers and primary contact names,
city states, etc...
So I guess I need subqueries or some sort of SQL pixie dust, cause I
can't figure out how to query in two different directions (order info
and customer info).
Help. Anyone got any example SQL scripts around? Or links to examples
that emulate the ofbiz database layout?
--
Walter