Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: crystal user editing someone elses SQL Post Reply Post New Topic
Author Message
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Online Status: Offline
Posts: 81
Quote cbaldwin Replybullet Topic: crystal user editing someone elses SQL
    Posted: 11 Jan 2016 at 6:26am
I have a report that was made by an external company in SQL. I would like to add two additional fields to their command file. The SQL is way way over my head.

I would like the fieldS
COMPONENT_INVENTORY.DIVISION
and
COMPONENT_INVENTORY.PARENT_COMP_MAN_ID
with an inner join to
COMPONENT_MANUFACTURE.COMP_MAN_ID
where COMPONENT_INVENTORY.PARENT_COMP_MAN_ID=COMPONENT_MANUFACTURE.COMP_MAN_ID

If anyone can offer assistance that would be great.
I have attached the complete SQL for the Command file for the report in question.

Thanks in advance for any help or insight.
CJB

SELECT *
FROM



(SELECT
null header,
ccmd.donor_id,
    dd.draw_start_datetime,
    dv.collection_location_id
    ||
    CASE
      WHEN dv.sublocation_id IS NULL
      THEN NULL
      ELSE '-'
    END
    || dv.sublocation_id source,
    dv.donor_group_id,
    dd.actual_collection_type_cd,
    dd.failure_cd,
    CASE
      WHEN ccmd.worksite_id='LEGACY'
      THEN NULL
      ELSE 'CM'
    END inv,
    CASE
      WHEN ccmd.source_unit_no IS NULL
      THEN ccmd.unit_no
      ELSE ccmd.source_unit_no
    END unit_no,
    CASE
      WHEN ccmd.source_unit_no IS NULL
      THEN NULL
      ELSE ccmd.unit_no
    END pool_unit_no,
    ccmd.comp_man_id,
    ccmd.component_group,
    ccmd.comp_product_code,
    ccmd.worksite_id,
    ccmd.comp_man_status_cd,
    ccmd.expiration_datetime,
    ccmd.quarantine_reason_cd,
    ccmd.discard_reason_cd,
    ccmd.discard_container_id,
    NULL ship_to_facility_id,
    NULL shipment_datetime,
    NULL ret_from_facility_id,
    NULL completed_datetime
FROM (WITH
table_var AS
(
    SELECT
      cm.donor_id,
      cm.draw_id,
      cm.comp_man_id,
      cm.component_group,
      cm.unit_no,
      cm.comp_product_code,
      cm.worksite_id,
      cm.comp_man_status_cd,
      cm.expiration_datetime,
      cm.quarantine_reason_cd,
      cm.discard_reason_cd,
      cm.discard_container_id,
      NULL source_unit_no
    FROM
      component_manufacture cm
    WHERE
      cm.donor_id IS NOT NULL
    UNION ALL
    SELECT
      cma.donor_id,
      cma.draw_id,
      cl.comp_id comp_man_id,
      cpl.component_group,
      cpl.unit_no,
      cpl.comp_product_code,
      cpl.worksite_id,
      cpl.comp_man_status_cd,
      cpl.expiration_datetime,
      cpl.quarantine_reason_cd,
      cpl.discard_reason_cd,
      cpl.discard_container_id,
      cma.unit_no source_unit_no
    FROM
      component_lineage cl
    INNER JOIN component_manufacture cma
    ON
      cl.parent_comp_id=cma.comp_man_id
    INNER JOIN component_manufacture cpl
    ON
      cl.comp_id      =cpl.comp_man_id
    AND cpl.donor_id IS NULL
    AND cma.donor_id IS NOT NULL
)
SELECT
tv.DONOR_ID,
tv.DRAW_ID,
tv.COMP_MAN_ID,
tv.COMPONENT_GROUP,
tv.UNIT_NO,
tv.COMP_PRODUCT_CODE,
tv.WORKSITE_ID,
tv.COMP_MAN_STATUS_CD,
tv.EXPIRATION_DATETIME,
tv.QUARANTINE_REASON_CD,
tv.DISCARD_REASON_CD,
tv.DISCARD_CONTAINER_ID,
tv.SOURCE_UNIT_NO
FROM
table_var tv
UNION ALL
SELECT
du.donor_id,
du.draw_id,
NULL comp_man_id,
NULL component_group,
du.unit_no,
NULL comp_product_code,
'LEGACY' worksite_id,
du.unit_status_cd,
NULL expiration_datetime,
du.quarantine_reason_cd,
du.discard_reason_cd,
du.discard_container_id,
NULL source_unit_no
FROM
donor_unit du
WHERE
NOT EXISTS
(
    SELECT
      tv1.*
    FROM
      table_var tv1
    WHERE
      tv1.donor_id =du.donor_id
    AND tv1.draw_id=du.draw_id
    AND tv1.unit_no=du.unit_no
)) ccmd
LEFT OUTER JOIN donor_draw dd
ON ccmd.draw_id= dd.draw_id
LEFT OUTER JOIN cd_failure cf
ON dd.failure_cd=cf.failure_cd
LEFT OUTER JOIN donor_visit dv
ON dd.visit_id               =dv.visit_id
WHERE dd.donor_id            ={?UNL_OR_DONOR}
AND ccmd.comp_man_status_cd IN ('C', 'IP', 'Q', 'D', 'PC')
UNION ALL
SELECT
null header,
ccmdd.donor_id,
    dd.draw_start_datetime,
    dv.collection_location_id
    ||
    CASE
      WHEN dv.sublocation_id IS NULL
      THEN NULL
      ELSE '-'
    END
    || dv.sublocation_id source,
    dv.donor_group_id,
    dd.actual_collection_type_cd,
    dd.failure_cd,
    'CI' inv,
    CASE
      WHEN ccmdd.source_unit_no IS NULL
      THEN ccmdd.unit_no
      ELSE ccmdd.source_unit_no
    END unit_no,
    CASE
      WHEN ccmdd.source_unit_no IS NULL
      THEN NULL
      ELSE ccmdd.unit_no
    END pool_unit_no,
    ci.compinv_id,
    ci.component_group,
    ci.comp_product_code,
    ci.worksite_id,
    ci.comp_inv_status_cd,
    ci.expiration_datetime,
    ci.quarantine_reason_cd,
    ci.discard_reason_cd,
    ci.discard_container_id,
    CASE
    
      WHEN ci.component_group IN ('RP','LP')
      
      THEN (
          SELECT distinct last_value (rs.ship_to_facility_id) over (order by rs.shipment_datetime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) next_facility
          FROM rp_shipment_carton_comp rscc
          INNER JOIN rp_shipment rs
          ON rscc.rp_shipment_id=rs.rp_shipment_id
          WHERE rscc.compinv_id =ci.compinv_id
      )
      
      ELSE (
          SELECT distinct last_value(s.ship_to_facility_id) over (order by s.shipment_datetime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) next_facility
          FROM shipment_item_component sic
          INNER JOIN shipment s
          ON sic.shipment_id =s.shipment_id
          WHERE sic.compinv_id=ci.compinv_id
      )
      
    END ship_to_facility_id,
    
    CASE
    
      WHEN ci.component_group IN ('RP','LP')
      
      THEN (
          SELECT distinct last_value (to_char(rs.actual_shipment_datetime, 'MM/DD/YY')) over (order by rs.actual_shipment_datetime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) next_shipment_date
          FROM rp_shipment_carton_comp rscc
          INNER JOIN rp_shipment rs
          ON rscc.rp_shipment_id=rs.rp_shipment_id
          WHERE rscc.compinv_id =ci.compinv_id
      )
      
      ELSE (
          SELECT distinct last_value(to_char(s.shipment_datetime, 'MM/DD/YY')) over (order by s.shipment_datetime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) next_shipment_date
          FROM shipment_item_component sic
          INNER JOIN shipment s
          ON sic.shipment_id =s.shipment_id
          WHERE sic.compinv_id=ci.compinv_id
      )
      
    END shipment_datetime,
    
    (
        SELECT distinct last_value(sr.ret_from_facility_id) over (order by sr.completed_datetime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) next_facility
        FROM shipment_return_component src
        INNER JOIN shipment_return sr
        ON src.shipment_return_id=sr.shipment_return_id
        WHERE src.compinv_id     =ci.compinv_id
    ) ret_from_facility_id,
      
    (
        SELECT distinct last_value(to_char(sr.completed_datetime, 'MM/DD/YY')) over (order by sr.completed_datetime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) next_return
        FROM shipment_return_component src
        INNER JOIN shipment_return sr
        ON src.shipment_return_id=sr.shipment_return_id
        WHERE src.compinv_id     =ci.compinv_id
    ) completed_datetime
      
FROM
    (SELECT DISTINCT donor_id,
      unit_no,
      source_unit_no
    FROM (WITH
table_var AS
(
    SELECT
      cm.donor_id,
      cm.draw_id,
      cm.comp_man_id,
      cm.component_group,
      cm.unit_no,
      cm.comp_product_code,
      cm.worksite_id,
      cm.comp_man_status_cd,
      cm.expiration_datetime,
      cm.quarantine_reason_cd,
      cm.discard_reason_cd,
      cm.discard_container_id,
      NULL source_unit_no
    FROM
      component_manufacture cm
    WHERE
      cm.donor_id IS NOT NULL
    UNION ALL
    SELECT
      cma.donor_id,
      cma.draw_id,
      cl.comp_id comp_man_id,
      cpl.component_group,
      cpl.unit_no,
      cpl.comp_product_code,
      cpl.worksite_id,
      cpl.comp_man_status_cd,
      cpl.expiration_datetime,
      cpl.quarantine_reason_cd,
      cpl.discard_reason_cd,
      cpl.discard_container_id,
      cma.unit_no source_unit_no
    FROM
      component_lineage cl
    INNER JOIN component_manufacture cma
    ON
      cl.parent_comp_id=cma.comp_man_id
    INNER JOIN component_manufacture cpl
    ON
      cl.comp_id      =cpl.comp_man_id
    AND cpl.donor_id IS NULL
    AND cma.donor_id IS NOT NULL
)
SELECT
tv.DONOR_ID,
tv.DRAW_ID,
tv.COMP_MAN_ID,
tv.COMPONENT_GROUP,
tv.UNIT_NO,
tv.COMP_PRODUCT_CODE,
tv.WORKSITE_ID,
tv.COMP_MAN_STATUS_CD,
tv.EXPIRATION_DATETIME,
tv.QUARANTINE_REASON_CD,
tv.DISCARD_REASON_CD,
tv.DISCARD_CONTAINER_ID,
tv.SOURCE_UNIT_NO
FROM
table_var tv
UNION ALL
SELECT
du.donor_id,
du.draw_id,
NULL comp_man_id,
NULL component_group,
du.unit_no,
NULL comp_product_code,
'LEGACY' worksite_id,
du.unit_status_cd,
NULL expiration_datetime,
du.quarantine_reason_cd,
du.discard_reason_cd,
du.discard_container_id,
NULL source_unit_no
FROM
donor_unit du
WHERE
NOT EXISTS
(
    SELECT
      tv1.*
    FROM
      table_var tv1
    WHERE
      tv1.donor_id =du.donor_id
    AND tv1.draw_id=du.draw_id
    AND tv1.unit_no=du.unit_no
))
    WHERE donor_id={?UNL_OR_DONOR}
    ) ccmdd
INNER JOIN donor_unit du
ON du.donor_id=ccmdd.donor_id
AND du.unit_no=(
    CASE
      WHEN ccmdd.source_unit_no IS NULL
      THEN ccmdd.unit_no
      ELSE ccmdd.source_unit_no
    END)
INNER JOIN donor_draw dd
ON du.draw_id= dd.draw_id
LEFT OUTER JOIN cd_failure cf
ON dd.failure_cd=cf.failure_cd
LEFT OUTER JOIN donor_visit dv
ON dd.visit_id=dv.visit_id
LEFT OUTER JOIN component_inventory ci
ON ccmdd.unit_no=ci.unit_no
WHERE ci.compinv_id     IS NOT NULL
AND ci.comp_inv_status_cd NOT IN ('C','E')
)
ORDER BY draw_start_datetime,
unit_no,
inv DESC,
component_group
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 11 Jan 2016 at 12:51pm
I took a little time and tried formatting the SQL so that it was easier to read.  It does not appear that they are doing anything really complicated.  But with the CTE's and Unions, it can be hard to follow.  Unfortunately I do not have the time to dissect the query.  So I am not sure where would be the proper place to add another table and the fields.
IP IP Logged
cbaldwin
Groupie
Groupie


Joined: 09 Apr 2014
Online Status: Offline
Posts: 81
Quote cbaldwin Replybullet Posted: 12 Jan 2016 at 2:28am
Thank you for considering my problem.
CJB
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.