Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query difference: HOw to Avoiding UNION ALL with correlated Query Expand / Collapse
Author
Message
Posted Monday, January 11, 2010 12:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
hi

in my first query i have a UNION ALL operator, i want to avoid it as it contains most of the table repetitive , so there is only two difference in query ( who have UNION ALL in between ) , i marked those chances with ( ------> difference ) sign

i have also added "my query" in which i have used correlated query but its not working as expected.

i am attaching both New and Old Query in Zip file

i am soory to tell you that i cant put table and index schema

So please try to do it without them.



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #845205
Posted Monday, January 11, 2010 2:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
No problem. Note - this is UNTESTED.

	select

reg_id = ISNULL(dr1.reg_id, dr2.reg_id), -- changed
invitee_id = ISNULL(di1.invitee_id, di2.invitee_id), -- changed

contact_type = isnull(ct.cont_type_name, ''),
item_id = I.item_id,
item_type = i.item_type,
item_category = I.item_category,
item_name = I.item_name,
item_code = I.item_code,
fee_name = f.fee_name,
start_date = I.session_start_date,
end_date = I.session_end_date,
reg_date = ae.created_date,
item_quantity = ae.prod_qty,
item_price = bd.bskt_dtl_price_tier_amt,
eligible_credit = I.credit_value,
participant_flag = case
when pe.acct_id is null then 0
else 1 end,
cv_acct_id = ae.acct_id,
cv_entity_stub = ae.entity_stub,
cv_entity_type_id = ae.entity_type_id,
cv_prod_stub = ae.prod_stub
from [dbo].[ATTENDEE_ENTITY] ae with (nolock)
join [dbo].[DMP_EVENT_ITEM] I with (nolock)
on I.cv_acct_id = ae.acct_id and I.cv_evt_stub = ae.evt_stub
AND ae.prod_stub = I.cv_prod_stub
left join [dbo].[CONTACT_TYPE] ct with (nolock)
on ct.acct_id = ae.acct_id and ct.cont_type_stub = ae.cont_type_stub
left join [dbo].[BASKET_DETAIL] bd with (nolock)
on bd.acct_id = ae.acct_id and bd.bskt_dtl_stub = ae.bskt_dtl_stub
left join [dbo].[FEE] f with (nolock)
on f.acct_id = bd.acct_id and f.fee_stub = bd.bskt_dtl_fee_stub

-- changes
join [dbo].[DMP_EVENT_INVITEE] di1 with (nolock)
on di.cv_acct_id = ae.acct_id and di.cv_cont_stub = ae.entity_stub -- > Difference
and di.cv_free_stub = '00000000-0000-0000-0000-000000000000'
and di.cv_evt_stub = i.cv_evt_stub

join [dbo].[DMP_EVENT_REGISTRATION] dr1 (nolock)
on dr.invitee_id = di1.invitee_id and dr.event_id = di1.event_id

LEFT join [dbo].[DMP_EVENT_INVITEE] di2 with (nolock)
on di.cv_acct_id = ae.acct_id and di.cv_free_stub = ae.entity_stub -- > Difference

join [dbo].[DMP_EVENT_REGISTRATION] dr2 with (nolock)
on dr2.cv_invitee_stub = di2.cv_invitee_stub and dr2.event_id = di2.event_id

-- \changes

left join [dbo].[PARTICIPANT_ENTITY] pe (nolock)
on pe.acct_id = ae.acct_id
and pe.evt_stub = ae.evt_stub and pe.prod_stub = i.cv_prod_stub and pe.entity_stub = ae.entity_stub
where ae.acct_id = 2000022
AND (ae.entity_type_id = 3 OR ae.entity_type_id = 10) -- > Changes



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #845245
Posted Monday, January 11, 2010 2:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
thanks for ur reply but its not working

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #845251
Posted Monday, January 11, 2010 3:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
Bhuvnesh (1/11/2010)
thanks for ur reply but its not working


There's always a danger of this when there's no sample data to test against. Could you provide some details of why it's not working?
Does it fail with an error?
Does it provide the correct row count?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #845254
Posted Monday, January 11, 2010 3:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
sample data is not possible , as it includes amny tables

yes row count is correct and time taken = 18 secs

but my concerns is UNION ALL with same tables

i want to replace it with correlated query.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #845255
Posted Monday, January 11, 2010 3:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
Bhuvnesh (1/11/2010)
sample data is not possible , as it includes amny tables

yes row count is correct and time taken = 18 secs

but my concerns is UNION ALL with same tables

i want to replace it with correlated query.


The row count is correct: are there any differences between the results of your first query and my query?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #845258
Posted Monday, January 11, 2010 3:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
yes . ur query is not returning any row

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #845259
Posted Monday, January 11, 2010 3:20 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 2:29 AM
Points: 604, Visits: 163
Hi Bhuvnesh

select
reg_id = dr.reg_id,
invitee_id = di.invitee_id,
contact_type = isnull(ct.cont_type_name, ''),
item_id = I.item_id,
item_type = i.item_type,
item_category = I.item_category,
item_name = I.item_name,
item_code = I.item_code,
fee_name = f.fee_name,
start_date = I.session_start_date,
end_date = I.session_end_date,
reg_date = ae.created_date,
item_quantity = ae.prod_qty,
item_price = bd.bskt_dtl_price_tier_amt,
eligible_credit = I.credit_value,
participant_flag = case
when pe.acct_id is null then 0
else 1 end,
cv_acct_id = ae.acct_id,
cv_entity_stub = ae.entity_stub,
cv_entity_type_id = ae.entity_type_id,
cv_prod_stub = ae.prod_stub
from [dbo].[ATTENDEE_ENTITY] ae with (nolock)
join [dbo].[DMP_EVENT_ITEM] I with (nolock)
on I.cv_acct_id = ae.acct_id and I.cv_evt_stub = ae.evt_stub
AND ae.prod_stub = I.cv_prod_stub
left join [dbo].[CONTACT_TYPE] ct with (nolock)
on ct.acct_id = ae.acct_id and ct.cont_type_stub = ae.cont_type_stub
left join [dbo].[BASKET_DETAIL] bd with (nolock)
on bd.acct_id = ae.acct_id and bd.bskt_dtl_stub = ae.bskt_dtl_stub
left join [dbo].[FEE] f with (nolock)
on f.acct_id = bd.acct_id and f.fee_stub = bd.bskt_dtl_fee_stub

join [dbo].[DMP_EVENT_INVITEE] di with (nolock)

on di.cv_acct_id = ae.acct_id


--- Changes

and Case When ISNULL(cv_cont_stub,0) != 0 THEN di.cv_cont_stub ELSE di.cv_free_stub END = ae.entity_stub

--and di.cv_free_stub = '00000000-0000-0000-0000-000000000000'


--- /Changes


and di.cv_evt_stub = i.cv_evt_stub



join [dbo].[DMP_EVENT_REGISTRATION] dr (nolock)
on dr.invitee_id = di.invitee_id and dr.event_id = di.event_id

left join [dbo].[PARTICIPANT_ENTITY] pe (nolock)
on pe.acct_id = ae.acct_id
and pe.evt_stub = ae.evt_stub and pe.prod_stub = i.cv_prod_stub and pe.entity_stub = ae.entity_stub
where

--- Change

ae.entity_type_id = Case When ISNULL(cv_cont_stub,0) != 0 THEN 3 ELSE 10 END

---/Change

AND ae.acct_id = 2000022


Please let me know if this helps....:)
Post #845262
Posted Monday, January 11, 2010 3:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
Not working

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #845265
Posted Monday, January 11, 2010 3:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
Bhuvnesh (1/11/2010)
sample data is not possible , as it includes amny tables

yes row count is correct and time taken = 18 secs

but my concerns is UNION ALL with same tables

i want to replace it with correlated query.

Bhuvnesh (1/11/2010)
yes . ur query is not returning any row


So...which is it?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #845266
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse