Subquery as a table reference

Tags:
SQL
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

yes

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following