Query Help

Aravindan GP
Aravindan GP used Ask the Experts™
on
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, 6, 8)) AS ACCT_NR, a.ord_id cancelled_ord_id, a.julian_dt,
                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, 6, 8)) AS ACCT_NR, a.ord_id cancelled_ord_id, a.julian_dt,
                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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Did you run both queries and what was their output?

Author

Commented:
I ran both queries and outputs are same.

Commented:
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,
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

David VanZandtOracle Database Administrator III

Commented:
The next question may be, then, to compare explain plans to compare resources.
Oracle dba
Top Expert 2009
Commented:
if you want to know if 2 queries return the same data
in general, the easiest test is this :

select * from queryA
minus
select * from queryB

select * from queryB
minus
select * from queryA

if both above queries return no rows, then they match

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial