|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-10-17 : 07:51:40
|
| Hazel writes "I am trying to select data from across a few tables and as a result the select statement is very long. In order to keep my asp file neat, I would like to put the select statement into a text file and then in the asp file, call the select statement in the text file and execute it. It is supposed to obtain an invoice number parse from a form in a previous page into this select statement but I keep getting BOF or EOF errors.Would appreciate if you could provide some pointers.Below is my select statement SELECT COST_LOCAL_ENTITIES_VIEW.DESCRIPTION, COST_LOCAL_ENTITIES_VIEW.ADDRESS_1, COST_LOCAL_ENTITIES_VIEW.ADDRESS_2, COST_LOCAL_ENTITIES_VIEW.ADDRESS_3, COST_LOCAL_ENTITIES_VIEW.ADDRESS_4, NVL(COST_INVOICE_REVS_TBL.INVR_NO, COST_INVOICE_TBL.INVH_NO) AS VOUCHER_NO, COST_INVOICE_TBL.INVH_DATE, COST_INVOICE_TBL.INVH_TYPE, COST_INVOICE_TBL.INVH_CCY as invh_ccy, NVL(COST_INVOICE_REVS_TBL.INVR_STS, COST_INVOICE_TBL.INVH_STS) AS STATUS, COST_INVOICE_TBL.INVH_ORG_DEPT, COST_INVOICE_TBL.INVH_DUE_DATE, COST_INVOICE_TBL.INVH_PAY_MODE, COST_INVOICE_TBL.INVH_VEND, COST_INVOICE_TBL.INVH_CONTACT, COST_INVOICE_TBL.INVH_PHONE, COST_INVOICE_TBL.INVH_ORG_NO,COST_INVOICE_CATEGORY_VIEW.DESCRIPTION, COST_INVOICE_STATUS_VIEW.DESCRIPTION, COST_DEPT_MASTER_TBL.DEPT_NAME, COST_MODE_OF_PAYMENT_VIEW.DESCRIPTION, COST_VENDOR_MASTER_TBL.VEND_NAME, COST_INVOICE_TBL.INVH_GST_INV , COST_INVOICE_TBL.INVH_REM, COST_INVOICE_TBL.INVH_REASON, NVL(COST_INVOICE_REVS_TBL.INVR_INP_USER, COST_INVOICE_TBL.INVH_INP_USER) AS INP_USER, NVL(COST_INVOICE_REVS_TBL.INVR_INP_DATE, COST_INVOICE_TBL.INVH_INP_DATE) AS INP_DATE, NVL(COST_INVOICE_REVS_TBL.INVR_UPD_USER, COST_INVOICE_TBL.INVH_UPD_USER) AS UPD_USER, NVL(COST_INVOICE_REVS_TBL.INVR_UPD_DATE, COST_INVOICE_TBL.INVH_UPD_DATE) AS UPD_DATE, NVL(COST_INVOICE_REVS_TBL.INVR_VERI_USER, COST_INVOICE_TBL.INVH_VERI_USER) AS VERI_USER, NVL(COST_INVOICE_REVS_TBL.INVR_VERI_DATE, COST_INVOICE_TBL.INVH_VERI_DATE) AS VERI_DATE, COST_INVOICE_TBL.INVH_HOD_USER, COST_INVOICE_TBL.INVH_HOD_DATE, COST_INVOICE_TBL.INVH_MGT_USER, COST_INVOICE_TBL.INVH_MGT_DATE, COST_INVOICE_TBL.INVH_MGT_LIMIT_USER, COST_INVOICE_TBL.INVH_MGT_LIMIT_DATE,NVL(invh_exch_rate, fx_rate) AS exch_rate,paym_check_no,DECODE(invh_sts, 'B', local_currency, COST_INVOICE_TBL.INVH_CCY) as alloc_ccy ,COST_INVOICE_TBL.INVH_GST_RATE,COST_INVOICE_TBL.INVH_XFR_INV,NVL(invr_paym_user, invh_paym_user) AS paym_user,NVL(invr_paym_date, invh_paym_date) AS paym_dateFROM COST_LOCAL_ENTITIES_VIEW, COST_INVOICE_TBL, COST_INVOICE_CATEGORY_VIEW, COST_INVOICE_STATUS_VIEW, COST_DEPT_MASTER_TBL, COST_MODE_OF_PAYMENT_VIEW, COST_VENDOR_MASTER_TBL, COST_INVOICE_REVS_TBL, COST_BRANCH_PARAMETERS_VIEW,cost_payment_tbl,cost_foreign_exch_tblWHERE ( cost_invoice_tbl.invh_no = cost_invoice_revs_tbl.invr_no (+)) and( COST_LOCAL_ENTITIES_VIEW.ENTITY_CODE = COST_INVOICE_CATEGORY_VIEW.ENTITY ) and ( COST_INVOICE_CATEGORY_VIEW.CATEGORY = COST_INVOICE_TBL.INVH_TYPE ) and ( COST_INVOICE_TBL.INVH_STS = COST_INVOICE_STATUS_VIEW.CODE ) and ( COST_INVOICE_TBL.INVH_ORG_DEPT = COST_DEPT_MASTER_TBL.DEPT_CODE(+) ) and ( COST_INVOICE_TBL.INVH_PAY_MODE = COST_MODE_OF_PAYMENT_VIEW.CODE ) and ( COST_INVOICE_TBL.INVH_VEND = COST_VENDOR_MASTER_TBL.VEND_CODE ) and( invh_no = paym_no(+) ) and( invh_no = fx_invh_no(+) ) and( COST_INVOICE_TBL.INVH_NO = 'request.querystring("inv_no")')UNIONSELECT COST_LOCAL_ENTITIES_VIEW.DESCRIPTION, COST_LOCAL_ENTITIES_VIEW.ADDRESS_1, COST_LOCAL_ENTITIES_VIEW.ADDRESS_2, COST_LOCAL_ENTITIES_VIEW.ADDRESS_3, COST_LOCAL_ENTITIES_VIEW.ADDRESS_4, COST_INVOICE_TBL.INVH_NO AS VOUCHER_NO, |
|
Parse a variable from a form to a long select statement 