ready2drum Posted December 17, 2009 Share Posted December 17, 2009 Not sure how to set up the following query so that the two Select statements return the same number of values....See query below: [codebox]SELECT TIDCAFAC.FACILITY, TIDCAFAC.CATALOG_ID, TIDCAMST.CATALOG_DESC, TIDCAFAC.CATALOG_STATUS, TIDCAFAC.STOCK_TYPE, TIDCAFAC.AUTO_REORDER_IND, TIDCAFAC.PRE_CAPITAL_IND, TIDCAMST.UNIT_OF_ISSUE, TIDCAFAC.TARGET_MAXIMUM, TIDCAWHS.QTY_IN_WHSE, TIDCAFAC.AVG_UNIT_PRICE, TIDCAWHS.LOC_ZONE, TIDCAWHS.LOC_ROW, TIDCAWHS.LOC_SECTION, TIDCAWHS.LOC_TIER, TIDCAWHS.LOC_BIN, TIDCAMFR.MANUFACTURER_CODE, TIDCAMFR.MANUF_PART_NUMBER, TIDCAPUR.VENDOR_CODE, TIDCAPUR.VENDOR_SUFFIX, MAX(TIDRCLIN.REC_LINE_DATE) AS REC_LINE_DATE, TIDCAWHS.LAST_UPDATED_DATE, TIDCAFAC.LEAD_TIME_ORDER, TIDCAFAC.LEAD_TIME_VENDOR, TIDCAFAC.LEADTIME_PUTAWAY, TIDCAFAC.REPAIRABLE_IND, TIDCAFAC.WORK_ORDER_NBR, TIDCAFAC.AUTO_REQUISITION, TIDCAPUR.AUTO_AWARD, TIDCAPUR.BPO_CANDIDATE, TIDCAFAC.EXPENSED_ITEM_IND, TIDCAMST.COMMODITY_CAT_CODE, TIDCAMST.COMMODITY_TYPE_COD, TIDCAMST.COMMODITY_NAME_COD, TIDCAFAC.CYCLE_COUNT_CODE, TIDCAFAC.ABC_ANALYSIS_CODE, MAX(TIDACDST.COST_CENTER_CHARGE) AS COST_CENTER_CHARGE, MAX(TIDACDST.ACTIVITY_ID) AS ACTIVITY_ID, MAX(TIDACDST.ACCOUNT_NBR) AS ACCOUNT_NBR, MAX(TIDACDST.SUB_ACCOUNT_NBR) AS SUB_ACCOUNT_NBR, (select tidcafac.facility, tidcafac.catalog_id, tidcafac.q_level, trim(tidacdst.COST_CENTER_CHARGE) || '-' || trim(tidacdst.ACTIVITY_ID) || '-' || trim(tidacdst.ACCOUNT_NBR) || case when tidacdst.SUB_ACCOUNT_NBR >' ' then '-' || trim(tidacdst.SUB_ACCOUNT_NBR) else '' end CC from tidcafac inner join tidacdst on tidcafac.a_xref_code = tidacdst.a_xref_code where tidcafac.facility = 'BIG') FROM TIDCAFAC INNER JOIN TIDCAMFR ON TIDCAFAC.CATALOG_ID = TIDCAMFR.CATALOG_ID AND TIDCAFAC.Q_LEVEL = TIDCAMFR.Q_LEVEL INNER JOIN TIDCAMST ON TIDCAFAC.CATALOG_ID = TIDCAMST.CATALOG_ID_MASTER INNER JOIN TIDCAPUR ON TIDCAFAC.FACILITY = TIDCAPUR.FACILITY AND TIDCAFAC.CATALOG_ID = TIDCAPUR.CATALOG_ID AND TIDCAFAC.Q_LEVEL = TIDCAPUR.Q_LEVEL INNER JOIN TIDCAWHS ON TIDCAFAC.FACILITY = TIDCAWHS.FACILITY AND TIDCAFAC.CATALOG_ID = TIDCAWHS.CATALOG_ID AND TIDCAFAC.Q_LEVEL = TIDCAWHS.Q_LEVEL INNER JOIN TIDRCLIN ON TIDCAFAC.FACILITY = TIDRCLIN.FACILITY AND TIDCAFAC.CATALOG_ID = TIDRCLIN.CATALOG_ID AND TIDCAFAC.Q_LEVEL = TIDRCLIN.Q_LEVEL LEFT OUTER JOIN TIDACDST ON TIDCAFAC.A_XREF_CODE = TIDACDST.A_XREF_CODEWHERE (TIDCAFAC.FACILITY = :facility) AND (:commodity_cat_code LIKE '%' || TIDCAMST.COMMODITY_CAT_CODE || '%')GROUP BY TIDCAFAC.FACILITY, TIDCAFAC.CATALOG_ID, TIDCAMST.CATALOG_DESC, TIDCAFAC.CATALOG_STATUS, TIDCAFAC.STOCK_TYPE, TIDCAFAC.AUTO_REORDER_IND, TIDCAFAC.PRE_CAPITAL_IND, TIDCAMST.UNIT_OF_ISSUE, TIDCAFAC.TARGET_MAXIMUM, TIDCAWHS.QTY_IN_WHSE, TIDCAFAC.AVG_UNIT_PRICE, TIDCAWHS.LOC_ZONE, TIDCAWHS.LOC_ROW, TIDCAWHS.LOC_SECTION, TIDCAWHS.LOC_TIER, TIDCAWHS.LOC_BIN, TIDCAMFR.MANUFACTURER_CODE, TIDCAMFR.MANUF_PART_NUMBER, TIDCAPUR.VENDOR_CODE, TIDCAPUR.VENDOR_SUFFIX, TIDCAWHS.LAST_UPDATED_DATE, TIDCAFAC.LEAD_TIME_ORDER, TIDCAFAC.LEAD_TIME_VENDOR, TIDCAFAC.LEADTIME_PUTAWAY, TIDCAFAC.REPAIRABLE_IND, TIDCAFAC.WORK_ORDER_NBR, TIDCAFAC.AUTO_REQUISITION, TIDCAPUR.AUTO_AWARD, TIDCAPUR.BPO_CANDIDATE, TIDCAFAC.EXPENSED_ITEM_IND, TIDCAMST.COMMODITY_CAT_CODE, TIDCAMST.COMMODITY_TYPE_COD, TIDCAMST.COMMODITY_NAME_COD, TIDCAFAC.CYCLE_COUNT_CODE, TIDCAFAC.ABC_ANALYSIS_CODE ORDER BY TIDCAMST.COMMODITY_CAT_CODE, TIDCAMST.COMMODITY_TYPE_COD, TIDCAMST.COMMODITY_NAME_COD[/codebox] I tried modifying the subquery in order to resolve this error, but it kept causing other error message so I'm resorting to the Forum for guidance on this issue.Any suggestions would be greatly appreciated....Thanks in advance! Link to comment Share on other sites More sharing options...
justsomeguy Posted December 17, 2009 Share Posted December 17, 2009 You're going to need to point out what the two select statements are, that whole thing is a single select statement. Link to comment Share on other sites More sharing options...
ready2drum Posted December 17, 2009 Author Share Posted December 17, 2009 The first SELECT statement is: SELECT TIDCAFAC.FACILITY, TIDCAFAC.CATALOG_ID, TIDCAMST.CATALOG_DESC, TIDCAFAC.CATALOG_STATUS, TIDCAFAC.STOCK_TYPE, TIDCAFAC.AUTO_REORDER_IND, TIDCAFAC.PRE_CAPITAL_IND, TIDCAMST.UNIT_OF_ISSUE, TIDCAFAC.TARGET_MAXIMUM, TIDCAWHS.QTY_IN_WHSE, TIDCAFAC.AVG_UNIT_PRICE, TIDCAWHS.LOC_ZONE, TIDCAWHS.LOC_ROW, TIDCAWHS.LOC_SECTION, TIDCAWHS.LOC_TIER, TIDCAWHS.LOC_BIN, TIDCAMFR.MANUFACTURER_CODE, TIDCAMFR.MANUF_PART_NUMBER, TIDCAPUR.VENDOR_CODE, TIDCAPUR.VENDOR_SUFFIX, MAX(TIDRCLIN.REC_LINE_DATE) AS REC_LINE_DATE, TIDCAWHS.LAST_UPDATED_DATE, TIDCAFAC.LEAD_TIME_ORDER, TIDCAFAC.LEAD_TIME_VENDOR, TIDCAFAC.LEADTIME_PUTAWAY, TIDCAFAC.REPAIRABLE_IND, TIDCAFAC.WORK_ORDER_NBR, TIDCAFAC.AUTO_REQUISITION, TIDCAPUR.AUTO_AWARD, TIDCAPUR.BPO_CANDIDATE, TIDCAFAC.EXPENSED_ITEM_IND, TIDCAMST.COMMODITY_CAT_CODE, TIDCAMST.COMMODITY_TYPE_COD, TIDCAMST.COMMODITY_NAME_COD, TIDCAFAC.CYCLE_COUNT_CODE, TIDCAFAC.ABC_ANALYSIS_CODE, MAX(TIDACDST.COST_CENTER_CHARGE) AS COST_CENTER_CHARGE, MAX(TIDACDST.ACTIVITY_ID) AS ACTIVITY_ID, MAX(TIDACDST.ACCOUNT_NBR) AS ACCOUNT_NBR, MAX(TIDACDST.SUB_ACCOUNT_NBR) AS SUB_ACCOUNT_NBR, and the second SELECT statement is... (SELECT tidcafac.facility, tidcafac.catalog_id, tidcafac.q_level, trim(tidacdst.COST_CENTER_CHARGE) || '-' || trim(tidacdst.ACTIVITY_ID) || '-' || trim(tidacdst.ACCOUNT_NBR) || case when tidacdst.SUB_ACCOUNT_NBR >' ' then '-' || trim(tidacdst.SUB_ACCOUNT_NBR) else '' end CC FROM tidcafac inner join tidacdst on tidcafac.a_xref_code = tidacdst.a_xref_code WHERE tidcafac.facility = 'BIG') Link to comment Share on other sites More sharing options...
ready2drum Posted December 17, 2009 Author Share Posted December 17, 2009 The first SELECT statement is: [codebox]SELECT TIDCAFAC.FACILITY, TIDCAFAC.CATALOG_ID, TIDCAMST.CATALOG_DESC, TIDCAFAC.CATALOG_STATUS, TIDCAFAC.STOCK_TYPE, TIDCAFAC.AUTO_REORDER_IND, TIDCAFAC.PRE_CAPITAL_IND, TIDCAMST.UNIT_OF_ISSUE, TIDCAFAC.TARGET_MAXIMUM, TIDCAWHS.QTY_IN_WHSE, TIDCAFAC.AVG_UNIT_PRICE, TIDCAWHS.LOC_ZONE, TIDCAWHS.LOC_ROW, TIDCAWHS.LOC_SECTION, TIDCAWHS.LOC_TIER, TIDCAWHS.LOC_BIN, TIDCAMFR.MANUFACTURER_CODE, TIDCAMFR.MANUF_PART_NUMBER, TIDCAPUR.VENDOR_CODE, TIDCAPUR.VENDOR_SUFFIX, MAX(TIDRCLIN.REC_LINE_DATE) AS REC_LINE_DATE, TIDCAWHS.LAST_UPDATED_DATE, TIDCAFAC.LEAD_TIME_ORDER, TIDCAFAC.LEAD_TIME_VENDOR, TIDCAFAC.LEADTIME_PUTAWAY, TIDCAFAC.REPAIRABLE_IND, TIDCAFAC.WORK_ORDER_NBR, TIDCAFAC.AUTO_REQUISITION, TIDCAPUR.AUTO_AWARD, TIDCAPUR.BPO_CANDIDATE, TIDCAFAC.EXPENSED_ITEM_IND, TIDCAMST.COMMODITY_CAT_CODE, TIDCAMST.COMMODITY_TYPE_COD, TIDCAMST.COMMODITY_NAME_COD, TIDCAFAC.CYCLE_COUNT_CODE, TIDCAFAC.ABC_ANALYSIS_CODE, MAX(TIDACDST.COST_CENTER_CHARGE) AS COST_CENTER_CHARGE, MAX(TIDACDST.ACTIVITY_ID) AS ACTIVITY_ID, MAX(TIDACDST.ACCOUNT_NBR) AS ACCOUNT_NBR, MAX(TIDACDST.SUB_ACCOUNT_NBR) AS SUB_ACCOUNT_NBR,[/codebox] and the second SELECT statement is... [codebox](SELECT tidcafac.facility, tidcafac.catalog_id, tidcafac.q_level, trim(tidacdst.COST_CENTER_CHARGE) || '-' || trim(tidacdst.ACTIVITY_ID) || '-' || trim(tidacdst.ACCOUNT_NBR) || case when tidacdst.SUB_ACCOUNT_NBR >' ' then '-' || trim(tidacdst.SUB_ACCOUNT_NBR) else '' end CC FROM tidcafac inner join tidacdst on tidcafac.a_xref_code = tidacdst.a_xref_code WHERE tidcafac.facility = 'BIG') [/codebox] Link to comment Share on other sites More sharing options...
justsomeguy Posted December 17, 2009 Share Posted December 17, 2009 When you have a select subquery in the list of columns to select it can only return one field. The entire select subquery acts as a single field. If you need to return several fields you'll need to duplicate the subquery and have it return each field individually. Link to comment Share on other sites More sharing options...
ready2drum Posted December 17, 2009 Author Share Posted December 17, 2009 I'll give that a try, but I'm not sure how to split up the subquery and still manage to use the trim functions. Link to comment Share on other sites More sharing options...
justsomeguy Posted December 17, 2009 Share Posted December 17, 2009 I'm not really sure what you're doing there, are those a bunch of trim statements separated by OR operators? Link to comment Share on other sites More sharing options...
ready2drum Posted December 17, 2009 Author Share Posted December 17, 2009 Actually, those pipe symbols represent 'concatenate' so that when the results are displayed, the fields are separated by dashes. 1 2 3 4 cost center charge-activity id-account nbr-sub account nbrExample: 12050-450-AEGT455-11000 1 2 3 4 I hope this helps! Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.