• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

Query Help


I would like some help tweaking this query to do something specific - I have to concantenate two fields from one table and then compare the values to another table and take the ID (from second table) and pipe separate the results into one field.

This is how I have written it now - but there is something wrong and your help is needed:

(SELECT DISTINCT LISTAGG((SELECT cll.CEC_ID FROM C_LANG_LIST cll WHERE TRIM(UPPER(cll.CODE)) = TRIM(UPPER((CONCAT(dw.iwic_disposal_recycle_code, dw.iwic_disp_recyc_operation_code))))) AS DRCodeList, '||') WITHIN GROUP (ORDER BY dw.dos_hwmd_sys_entered_yr) AS DRCodeList

The entire query:
SELECT DISTINCT dw.DOSSIER_WST_LINE_ITEM_NUM AS WasteStreamNumber, NUM_OF_SHIPMENTS AS NumberOfShipments,
dw.NOTICE_COPY_WST_TOTAL_QUANTITY AS QuantityNumber,
(SELECT CEC_ID FROM C_LANG_LIST cl WHERE UPPER(cl.code) = UPPER(dw.NOTICE_COPY_QTY_UNIT)) AS UnitOfMeasureCode,
(SELECT DISTINCT LISTAGG((SELECT cll.CEC_ID FROM C_LANG_LIST cll WHERE TRIM(UPPER(cll.CODE)) = TRIM(UPPER((CONCAT(dw.iwic_disposal_recycle_code, dw.iwic_disp_recyc_operation_code))))) AS DRCodeList, '||') WITHIN GROUP (ORDER BY dw.dos_hwmd_sys_entered_yr) AS DRCodeList
FROM DOSSIER_WASTE dw  
WHERE dw.dos_hwmd_sys_entered_yr = 10
AND dw.dossier_identifier = 325
AND dw.dossier_type_code = 'EXP';
0
Mosquitoe
Asked:
Mosquitoe
1 Solution
 
sdstuberCommented:
I think your parentheses are off
try this


S ELECT DISTINCT
       dw.dossier_wst_line_item_num AS wastestreamnumber,
       num_of_shipments AS numberofshipments,
       dw.notice_copy_wst_total_quantity AS quantitynumber,
       (SELECT cec_id
          FROM c_lang_list cl
         WHERE UPPER(cl.code) = UPPER(dw.notice_copy_qty_unit))
           AS unitofmeasurecode,
       (SELECT LISTAGG(cll.cec_id, '||') WITHIN GROUP (ORDER BY dw.dos_hwmd_sys_entered_yr)
          FROM c_lang_list cll
         WHERE TRIM(UPPER(cll.code)) =
                   TRIM(
                       UPPER(
                           (CONCAT(dw.iwic_disposal_recycle_code,
                                   dw.iwic_disp_recyc_operation_code
                                  )))))
           AS drcodelist
  FROM dossier_waste dw
 WHERE     dw.dos_hwmd_sys_entered_yr = 10
       AND dw.dossier_identifier = 325
       AND dw.dossier_type_code = 'EXP';
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
unclear what exactly you are trying to achieve ...

data samples, please.-
0
 
tigin44Commented:
your query should look like this...

SELECT dw.DOSSIER_WST_LINE_ITEM_NUM AS WasteStreamNumber, NUM_OF_SHIPMENTS AS NumberOfShipments, dw.NOTICE_COPY_WST_TOTAL_QUANTITY AS QuantityNumber, TRIM(UPPER((CONCAT(dw.iwic_disposal_recycle_code, dw.iwic_disp_recyc_operation_code)))))
dw.NOTICE_COPY_WST_TOTAL_QUANTITY AS QuantityNumber,
FROM DOSSIER_WASTE dw  
    INNER JOIN C_LANG_LIST cll ON TRIM(UPPER(cll.CODE)) =  TRIM(UPPER((CONCAT(dw.iwic_disposal_recycle_code, dw.iwic_disp_recyc_operation_code)))))
WHERE dw.dos_hwmd_sys_entered_yr = 10
AND dw.dossier_identifier = 325
AND dw.dossier_type_code = 'EXP';

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now