Query Help
Hi,
Please compare the below queries and tell me if both the queries will result the same data or different or duplicates..
A:
SELECT DISTINCT (SUBSTR(A.ACCT_NR, 1, 5)||'-'||SUBSTR(A.ACCT_NR,
a.invc_priorty_cd, a.fscl_nte_nr,
b.ord_id AS rebilled_order_id, 'PARCIAL' AS TYPE
FROM sir_invc a,
(SELECT a.ord_id, a.last_ord_id, a.acct_nr, a.cmpgn_nr,
a.cmpgn_yr
FROM sir_invc a, sir_invc_cncltn_rqst icr
WHERE a.cmpgn_yr = icr.campaign_year
AND a.cmpgn_nr = icr.campaign_number
--AND icr.ord_id = a.ord_id
AND icr.account_number = a.acct_nr
AND bilng_in68_cd = 129
AND wrhs_cd = 11
AND zone_cd = 2
AND TO_CHAR (icr.work_dt, 'yyyy-MM-dd') = '2012-03-29'
AND icr.invc_cncltn_answr_dt IS NOT NULL) b,
sir_invc_cncltn_rqst icr
WHERE a.last_ord_id IS NULL
AND a.ord_id = b.last_ord_id
AND a.acct_nr = b.acct_nr
AND a.cmpgn_nr = b.cmpgn_nr
AND a.cmpgn_yr = b.cmpgn_yr
AND a.cmpgn_yr = icr.campaign_year
AND a.cmpgn_nr = icr.campaign_number
AND icr.ord_id = a.ord_id
AND icr.account_number = a.acct_nr
AND TO_CHAR (icr.work_dt, 'yyyy-MM-dd') = '2012-03-29'
AND icr.invc_cncltn_answr_dt IS NOT NULL
AND a.bilng_in68_cd = 129
AND a.wrhs_cd = 11
AND a.zone_cd = 2
B:
SELECT DISTINCT (SUBSTR(A.ACCT_NR, 1, 5)||'-'||SUBSTR(A.ACCT_NR,
a.invc_priorty_cd, a.fscl_nte_nr,
b.ord_id AS rebilled_order_id, 'PARCIAL' AS TYPE
FROM sir_invc a,
(SELECT a.ord_id, a.last_ord_id, a.acct_nr, a.cmpgn_nr,
a.cmpgn_yr
FROM sir_invc a, sir_invc_cncltn_rqst icr
WHERE a.cmpgn_yr = icr.campaign_year
AND a.cmpgn_nr = icr.campaign_number
--AND icr.ord_id = a.ord_id
AND icr.account_number = a.acct_nr
AND bilng_in68_cd = 129
AND wrhs_cd = 11
AND zone_cd = 2
AND TO_CHAR (icr.work_dt, 'yyyy-MM-dd') = '2012-03-29'
AND icr.invc_cncltn_answr_dt IS NOT NULL) b,
sir_invc_cncltn_rqst icr
WHERE a.last_ord_id IS NULL
AND a.ord_id = b.last_ord_id
AND a.acct_nr = b.acct_nr
AND a.cmpgn_nr = b.cmpgn_nr
AND a.cmpgn_yr = b.cmpgn_yr
AND a.cmpgn_yr = icr.campaign_year
AND a.cmpgn_nr = icr.campaign_number
Please compare the below queries and tell me if both the queries will result the same data or different or duplicates..
A:
SELECT DISTINCT (SUBSTR(A.ACCT_NR, 1, 5)||'-'||SUBSTR(A.ACCT_NR,
a.invc_priorty_cd, a.fscl_nte_nr,
b.ord_id AS rebilled_order_id, 'PARCIAL' AS TYPE
FROM sir_invc a,
(SELECT a.ord_id, a.last_ord_id, a.acct_nr, a.cmpgn_nr,
a.cmpgn_yr
FROM sir_invc a, sir_invc_cncltn_rqst icr
WHERE a.cmpgn_yr = icr.campaign_year
AND a.cmpgn_nr = icr.campaign_number
--AND icr.ord_id = a.ord_id
AND icr.account_number = a.acct_nr
AND bilng_in68_cd = 129
AND wrhs_cd = 11
AND zone_cd = 2
AND TO_CHAR (icr.work_dt, 'yyyy-MM-dd') = '2012-03-29'
AND icr.invc_cncltn_answr_dt IS NOT NULL) b,
sir_invc_cncltn_rqst icr
WHERE a.last_ord_id IS NULL
AND a.ord_id = b.last_ord_id
AND a.acct_nr = b.acct_nr
AND a.cmpgn_nr = b.cmpgn_nr
AND a.cmpgn_yr = b.cmpgn_yr
AND a.cmpgn_yr = icr.campaign_year
AND a.cmpgn_nr = icr.campaign_number
AND icr.ord_id = a.ord_id
AND icr.account_number = a.acct_nr
AND TO_CHAR (icr.work_dt, 'yyyy-MM-dd') = '2012-03-29'
AND icr.invc_cncltn_answr_dt IS NOT NULL
AND a.bilng_in68_cd = 129
AND a.wrhs_cd = 11
AND a.zone_cd = 2
B:
SELECT DISTINCT (SUBSTR(A.ACCT_NR, 1, 5)||'-'||SUBSTR(A.ACCT_NR,
a.invc_priorty_cd, a.fscl_nte_nr,
b.ord_id AS rebilled_order_id, 'PARCIAL' AS TYPE
FROM sir_invc a,
(SELECT a.ord_id, a.last_ord_id, a.acct_nr, a.cmpgn_nr,
a.cmpgn_yr
FROM sir_invc a, sir_invc_cncltn_rqst icr
WHERE a.cmpgn_yr = icr.campaign_year
AND a.cmpgn_nr = icr.campaign_number
--AND icr.ord_id = a.ord_id
AND icr.account_number = a.acct_nr
AND bilng_in68_cd = 129
AND wrhs_cd = 11
AND zone_cd = 2
AND TO_CHAR (icr.work_dt, 'yyyy-MM-dd') = '2012-03-29'
AND icr.invc_cncltn_answr_dt IS NOT NULL) b,
sir_invc_cncltn_rqst icr
WHERE a.last_ord_id IS NULL
AND a.ord_id = b.last_ord_id
AND a.acct_nr = b.acct_nr
AND a.cmpgn_nr = b.cmpgn_nr
AND a.cmpgn_yr = b.cmpgn_yr
AND a.cmpgn_yr = icr.campaign_year
AND a.cmpgn_nr = icr.campaign_number
Gerwin Jansen
Did you run both queries and what was their output?
ASKER
I ran both queries and outputs are same.
The additional:
AND a.bilng_in68_cd = 129
AND a.wrhs_cd = 11
AND a.zone_cd = 2
in the "main" query will filter more rows and when the result is the same, than there probably aren't any rows with other values as the 129/11/2 selected.
I would use the first query, but change the qualification of the FROM subquery to have a clearer view of which fields are compared like:
(SELECT sa.ord_id, sa.last_ord_id, sa.acct_nr, sa.cmpgn_nr,
sa.cmpgn_yr
FROM sir_invc sa, sir_invc_cncltn_rqst sicr
WHERE sa.cmpgn_yr = sicr.campaign_year
AND sa.cmpgn_nr = sicr.campaign_number
--AND sicr.ord_id = sa.ord_id
AND sicr.account_number = sa.acct_nr
AND sa.bilng_in68_cd = 129
AND sa.wrhs_cd = 11
AND sa.zone_cd = 2
AND TO_CHAR (sicr.work_dt, 'yyyy-MM-dd') = '2012-03-29'
AND sicr.invc_cncltn_answr_dt IS NOT NULL) b,
AND a.bilng_in68_cd = 129
AND a.wrhs_cd = 11
AND a.zone_cd = 2
in the "main" query will filter more rows and when the result is the same, than there probably aren't any rows with other values as the 129/11/2 selected.
I would use the first query, but change the qualification of the FROM subquery to have a clearer view of which fields are compared like:
(SELECT sa.ord_id, sa.last_ord_id, sa.acct_nr, sa.cmpgn_nr,
sa.cmpgn_yr
FROM sir_invc sa, sir_invc_cncltn_rqst sicr
WHERE sa.cmpgn_yr = sicr.campaign_year
AND sa.cmpgn_nr = sicr.campaign_number
--AND sicr.ord_id = sa.ord_id
AND sicr.account_number = sa.acct_nr
AND sa.bilng_in68_cd = 129
AND sa.wrhs_cd = 11
AND sa.zone_cd = 2
AND TO_CHAR (sicr.work_dt, 'yyyy-MM-dd') = '2012-03-29'
AND sicr.invc_cncltn_answr_dt IS NOT NULL) b,
The next question may be, then, to compare explain plans to compare resources.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks