I use Showcase Strategy to develop queries and reports on the AS400. Showcase is telling me that I can't use a subquery to create a table reference to join with in the FROM clause. The Showcase Tech says subqueries are only allowed in WHERE clause. Non-AS400 SQL texts I have read suggest subquery table references as a great tool. Below is my statement that I want to use and the subquery is on lines 40 through 51. I tried running the statement using STRSQL but I am getting an error in my date conversion. Can you tell me if the AS400 allows a subquery in the FROM clause? Thank you so much, John Donnelly Findlay Automotive Group Las Vegas, NV 1 SELECT 2 SVSOHDR_COMPANY_NUMBER AS "Company Number", 3 date( decimal( ( SVSOHDR_SO_CLOSE_YEAR * 10000 ) + ( 4 SVSOHDR_SO_CLOSE_MONTH * 100 ) + SVSOHDR_SO_CLOSE_DAY, 5 8, 0 ), YYYYMMDD ) AS "Close Date", 6 SVSOHDR_SERVICE_ORDER_NUMBER AS "SO#", 7 SVSOHDR_SERVICE_ADVISOR_NUM AS "Consultant#", 8 SVSOLNR_LINE_TECHNICIAN_NUM AS "Technician#", 9 SVSORLR_WORK_LINE_NUMBER AS "Line #", 10 SUM ( CASE WHEN SVSORLR_BILLED_LABOR_HOURS > 0 THEN 11 SVSORLR_BILLED_LABOR_HOURS 12 ELSE SVSORLR_DERIVE_BILLED_LBR_HRS 13 END ) AS "Hours Added", 14 SUM ( CASE WHEN SVSORLR_BILLED_LABOR_AMOUNT > 0 THEN 15 SVSORLR_BILLED_LABOR_AMOUNT 16 ELSE SVSORLR_DERIVE_BILLED_LBR_AMT 17 END ) AS "Labor$", 18 PRTLINE.PTCHGAMT AS "Parts$" 19 FROM 20 SVSOHDPF 21 LEFT OUTER JOIN SVSOLNPF SERVICE 22 ON 23 SVSOHDPF.SVSOHDR_COMPANY_NUMBER = 24 SERVICE.SVSOLNR_COMPANY_NUMBER AND 25 SVSOHDPF.SVSOHDR_WORK_NUMBER = 26 SERVICE.SVSOLNR_WORK_NUMBER AND 27 SVSOHDPF.SVSOHDR_WORK_REVISION_NUMBER = 28 SERVICE.SVSOLNR_WORK_REVISION_NUMBER 29 LEFT OUTER JOIN SVSORLPF 30 ON 31 SERVICE.SVSOLNR_COMPANY_NUMBER = 32 SVSORLPF.SVSORLR_COMPANY_NUMBER AND 33 SERVICE.SVSOLNR_WORK_NUMBER = 34 SVSORLPF.SVSORLR_WORK_NUMBER AND 35 SERVICE.SVSOLNR_WORK_REVISION_NUMBER = 36 SVSORLPF.SVSORLR_WORK_REVISION_NUMBER AND 37 SERVICE.SVSOLNR_WORK_LINE_NUMBER = 38 SVSORLPF.SVSORLR_WORK_LINE_NUMBER 39 LEFT OUTER JOIN 40 (SELECT SVSORPR_COMPANY_NUMBER, 41 SVSORPR_WORK_NUMBER, 42 SVSORPR_WORK_REVISION_NUMBER, 43 SUM( SVSORPR_PARTS_CHARGE_AMOUNT) AS PTCHGAMT, 44 SVSORPR_WORK_LINE_NUMBER 45 FROM 46 SVSORPPF 47 GROUP BY 48 SVSORPR_COMPANY_NUMBER, 49 SVSORPR_WORK_NUMBER, 50 SVSORPR_WORK_REVISION_NUMBER, 51 SVSORPR_WORK_LINE_NUMBER ) AS PRTLINE 52 ON 53 SVSORLPF.SVSORLR_COMPANY_NUMBER = 54 PRTLINE.SVSORPR_COMPANY_NUMBER AND 55 SVSORLPF.SVSORLR_WORK_NUMBER = 56 PRTLINE.SVSORPR_WORK_NUMBER AND 57 SVSORLPF.SVSORLR_WORK_REVISION_NUMBER = 58 PRTLINE.SVSORPR_WORK_REVISION_NUMBER AND 59 SVSORLPF.SVSORLR_WORK_LINE_NUMBER = 60 PRTLINE.SVSORPR_WORK_LINE_NUMBER AND 61 WHERE 62 SVSOHDR_COMPANY_NUMBER = 1 63 AND SVSOHDR_WORK_REVISION_NUMBER = 0 64 AND SVSOLNR_LINE_MAJOR_SALE_TYPE = 'C' 65 AND SVSOHDR_SERVICE_ORDER_STATUS >= 50 66 AND SVSOHDR_SERVICE_ORDER_STATUS <> 85 67 AND SLSOST > 20 68 AND SVSOHDR_SERVICE_ORDER_NUMBER = 218453 69 GROUP BY 70 SVSOHDR_COMPANY_NUMBER, 71 date( decimal( ( SVSOHDR_SO_CLOSE_YEAR * 10000 ) + ( 72 SVSOHDR_SO_CLOSE_MONTH * 100 ) + 73 SVSOHDR_SO_CLOSE_DAY, 8, 0 ), YYYYMMDD ), 74 SVSOHDR_SERVICE_ORDER_NUMBER, 75 SVSOHDR_SERVICE_ADVISOR_NUM, 76 SVSOLNR_LINE_TECHNICIAN_NUM, 77 SVSORLR_WORK_LINE_NUMBER 78 ORDER BY 79 4

Software/Hardware used: