[cablalock]

Trying to select specific records from a view that is made from selections pointing to two other views.

Done this many times in MS SQL Database enviro... no joy in Pervasive...

Ideas besides building a separate view? (counter to my goal of one view serving many reports, etc...)

[mirtheil]

WHat version of PSQL are you using?
Do you get an error message? If so, what?

[cablalock]

Thanks for the post...

Error is...
ODBC Error: SQLSTATE = S1000, Native error code = 0
Grouped view joins are not supported.

database version is 8.00.114

[cablalock]

Here's the syntax for the view i'm trying to pull select fields from... .

create view vv_tmt_open_so_lines as

SELECT DISTINCT
If(Month(ol.DATE_ITEM_PROM)<Month(Current_Date()) And Year(ol.DATE_ITEM_PROM)<=Year(Current_Date()),'01 - Previous Month',
If(Month(ol.DATE_ITEM_PROM)>Month(Current_Date()) And Year(ol.DATE_ITEM_PROM)<Year(Current_Date()),'01 - Previous Month',
If(Year(ol.DATE_ITEM_PROM)>Year(Current_Date()),'0 3 - Future Month',
If(Month(ol.DATE_ITEM_PROM)>Month(Current_Date()) And Year(ol.DATE_ITEM_PROM)=Year(Current_Date()),'03 - Future Month',
If(Month(ol.DATE_ITEM_PROM)=Month(Current_Date()) And Year(ol.DATE_ITEM_PROM)=Year(Current_Date()),'02 - Current Month',
'Undefined'))))) AS "Month",

ol.DATE_ITEM_PROM-Current_Date() AS "Days_Left",
ol.CUSTOMER AS "Customer",
ol.ORDER_NO AS "Order",
ol.RECORD_NO AS "Line",
ol.PART AS "Part",
ol.DESCRIPTION AS "Description",
ol.QTY_BO AS "Qty_BO",
ol.QTY_SHIPPED AS "Ship_TD",
oh.CUSTOMER_PO AS "Cust_PO",
oh.date_order as "Date_Enter",
ol.DATE_ITEM_PROM AS "Date_Prom",
DayName(ol.DATE_ITEM_PROM) as "Day_Prom",
(ol.price*ol.QTY_BO) AS "BO_Price_Ext"


FROM v_order_header oh, v_order_lines ol

where oh.ORDER_NO = ol.ORDER_NO
and ol.RECORD_TYPE ='L'
and oh.RECORD_TYPE ='A'

[mirtheil]

I can't try it with your data but one of the new features in PSQL 9.5 is "Grouped View Support" (at http://www.pervasive.com/library/doc...html#wp122493).

PSQL 8.0 is very old. It's had 4 service releases and is currently at 8.7 (SP3). There's also been PSQL 9.0, 9.1, 9.5 and now 10.0. It might be time to upgrade to the current version.

[cablalock]

Thanks - upgrade is in the plans...

thanks for the help.

feel like answering another one for me?

[mirtheil]

Post up.. I'll try.