SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query difference: HOw to Avoiding UNION ALL with correlated Query


Query difference: HOw to Avoiding UNION ALL with correlated Query

Author
Message
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5450 Visits: 4076
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;-)
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16898 Visits: 19557
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
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5450 Visits: 4076
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;-)
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16898 Visits: 19557
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
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5450 Visits: 4076
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;-)
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16898 Visits: 19557
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
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5450 Visits: 4076
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;-)
Azzu
Azzu
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 172
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....Smile
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5450 Visits: 4076
Not working Sad

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16898 Visits: 19557
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search