Mary, Not sure about this but I think the DBMS doesn't like you putting a SELECT SUM GROUP BY in that final column. Theoretically you could have more than one value per line there. I would think it's possible to do it without the nested SELECT? I don't understand all the business logic but conceivably you might need to join to ADJUSTMENTS twice in order to achieve that Nick -----Original Message----- From: Mary Fowler [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 10, 2001 6:20 PM To: CF-Talk Subject: SQL subquery question Greetings, I have two tables. One (SECTIONS_DATA)is the main line item table of a budget. The second (ADJUSTMENTS)lists adjustments made to various line items of items in SECTIONS_DATA. The result set I am trying to get is one that can display the line items from SECTIONS_DATA with an adjustment_amount column added for each line item that has any adjustments. The amount that would appear in the adjustment_amount column would be the sum of all adjustment items from ADJUSTMENTS table for that particular line item. Some SECTIONS_DATA line items are not to be part of the record set. Below is the latest version I have tried. Am I going at this all wrong? The Oracle message I get (I am using Cold Fusion to send the query) is ORA-00936-Missing Expression. What is missing? Any ideas, hints, suggestions or answers would be greatly appreciated. Thanks in advance, :)mary SELECT DISTINCT SECTIONS_DATA.CITY_ID, SECTIONS_DATA.ORIGINAL_AMT, SECTIONS_DATA.CITY_COLUMN, SECTIONS_DATA.CRDF_LINENUM, SECTIONS_DATA.CRDF_SECTION (SELECT SUM(ADJUSTMENTS.ADJ_AMT) AS ADJUSTMENT_sum FROM ADJUSTMENTS GROUP BY ADJUSTMENTS.SECTION, ADJUSTMENTS.LINE, ADJUSTMENTS.CITY, ADJUSTMENTS.CITY_COL, ADJUSTMENTS.FISCAL_YEAR, ADJUSTMENTS.REC_MONTH ) AS ADJUSTMENT FROM SECTIONS_DATA, ADJUSTMENTS WHERE (SECTIONS_DATA.JUDICIAL_ID = '#FORM.JDID#' AND SECTIONS_DATA.FISCAL_YEAR = #VAL(FORM.FISCAL_YEAR)# AND SECTIONS_DATA.REC_MONTH = #VAL(FORM.REC_MONTH)#) AND (SECTIONS_DATA.JUDICIAL_ID = ADJUSTMENTS.JUDICIAL_ID AND SECTIONS_DATA.FISCAL_YEAR = ADJUSTMENTS.FISCAL_YEAR AND SECTIONS_DATA.REC_MONTH = ADJUSTMENTS.REC_MONTH) AND ((SECTIONS_DATA.CITY_ID = ADJUSTMENTS.CITY AND SECTIONS_DATA.CITY_COLUMN = ADJUSTMENTS.CITY_COL AND ADJUSTMENTS.SECTION ='2') OR (SECTIONS_DATA.CRDF_LINENUM = ADJUSTMENTS.LINE AND ADJUSTMENTS.SECTION IN ('6','7'))) AND NOT ((SECTIONS_DATA.CRDF_SECTION = '6' OR ADJUSTMENTS.SECTION = '6') AND (SECTIONS_DATA.CRDF_LINENUM IN ('13','16A','22','23') OR ADJUSTMENTS.LINE IN ('13','16A','22','23'))) AND NOT ((SECTIONS_DATA.CRDF_SECTION = '7' OR ADJUSTMENTS.SECTION = '7') AND (SECTIONS_DATA.CRDF_LINENUM IN ('1','6','11','17','45A','45B','45C','47','48','51','54','54A','55','56') OR ADJUSTMENTS.LINE IN ('1','6','11','17','45A','45B','45C','47','48','51','54','54A','55','56'))) ORDER BY CRDF_SECTION, CRDF_LINENUM, CITY_ID, CITY_COLUMN ********************************************************************** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ********************************************************************** ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
