January 11, 2010 at 12:54 am
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;-)
January 11, 2010 at 2:41 am
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
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
January 11, 2010 at 2:56 am
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;-)
January 11, 2010 at 3:01 am
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?
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
January 11, 2010 at 3:05 am
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;-)
January 11, 2010 at 3:11 am
Bhuvnesh (1/11/2010)
sample data is not possible , as it includes amny tablesyes 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?
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
January 11, 2010 at 3:12 am
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;-)
January 11, 2010 at 3:20 am
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....:)
January 11, 2010 at 3:26 am
Not working 🙁
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 11, 2010 at 3:29 am
Bhuvnesh (1/11/2010)
sample data is not possible , as it includes amny tablesyes 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?
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
January 11, 2010 at 3:32 am
i didnt get ur question but if u e asking about which query is corect and the query which has UNION ALL
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 11, 2010 at 4:10 am
Try this:
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
LEFT 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
LEFT 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
LEFT 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
If it doesn't give the results you are expecting, please explain how the results from this query differ from those from your original query with the UNION.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply