July 25, 2010 at 11:41 pm
Hi All,
i have a query as given below
Select * from (
SELECT TOP(1) * from dbo.vwGetCallAttemptList AS CallList
inner join
(
SELECT [wrk_ha_calldetail].[id_calllist], max(CreationDate) as CallDetailCreationDate
FROM [wrk_ha_calldetail]
GROUP BY id_calllist
HAVING COUNT([wrk_ha_calldetail].[id_calllist]) = 1
) c_detail on CallList.[id_calllistid] = [c_detail].[id_calllist]
WHERE
dbo.fn_GetBusinessDays([CallList].[CreationDate] ,GETDATE()) between 16 and 30
AND DATEDIFF(dd,[c_detail].CallDetailCreationDate,[dbo].[fn_getnextbusinessday](GETDATE(),3)) >0
--AND CallList.[id_calllistid] in (SELECT id_calllistID from tf_GetZoneFilter())
AND [dbo].[GetLatestCallResult](CallList.[id_CallListId]) <> 16
AND [dbo].[GetLatestCallResult](CallList.[id_CallListId]) <> 12
AND ('352,368,276,389,215,369,332,217,250,391,353,390' is null or exists(select id_account from [dbo].fn_parse_accounts('352,368,276,389,215,369,332,217,250,391,353,390')
where id_account = CallList.[id_account]))
ORDER BY [c_detail].CallDetailCreationDate Asc
UNION
SELECT TOP(1) * from dbo.vwGetCallAttemptList AS CallList
inner join
(
SELECT [wrk_ha_calldetail].[id_calllist], max(CreationDate) as CallDetailCreationDate,
[wrk_ha_calldetail].[id_ContactOutcome],[wrk_ha_calldetail].[id_CallResult]
FROM [wrk_ha_calldetail]
GROUP BY id_calllist,id_ContactOutcome,id_CallResult
HAVING COUNT([wrk_ha_calldetail].[id_calllist]) = 2
) c_detail on CallList.[id_calllistid] = [c_detail].[id_calllist]
WHERE
dbo.fn_GetBusinessDays([CallList].[CreationDate] ,GETDATE()) between 16 and 30
AND DATEDIFF(dd,[c_detail].CallDetailCreationDate,[dbo].[fn_getnextbusinessday](GETDATE(),3)) >0
--AND CallList.[id_calllistid] in (SELECT id_calllistID from tf_GetZoneFilter())
AND [dbo].[GetLatestCallResult](CallList.[id_CallListId]) <> 16
AND [dbo].[GetLatestCallResult](CallList.[id_CallListId]) <> 12
AND [c_detail].[id_ContactOutcome]=8
AND [c_detail].[id_CallResult]=4
AND ('352,368,276,389,215,369,332,217,250,391,353,390' is null or exists(select id_account from [dbo].fn_parse_accounts('352,368,276,389,215,369,332,217,250,391,353,390')
where id_account = CallList.[id_account]))
ORDER BY [c_detail].CallDetailCreationDate Asc
) as CalllistResut
But qhwn i try executing the Query i get the following error, Ple help me out
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
July 25, 2010 at 11:58 pm
Check all the Field names used in select qry, No. of Fields and Field Names should be same when using UNION in sql.
July 26, 2010 at 1:15 am
The secod section of the UNION operation has additional columns ([wrk_ha_calldetail].[id_ContactOutcome],[wrk_ha_calldetail].[id_CallResult]).
Either remove those columns or expand the first SELECT statement:
SELECT [wrk_ha_calldetail].[id_calllist], max(CreationDate) as CallDetailCreationDate,0 as [id_ContactOutcome], '' [id_CallResult]
FROM [wrk_ha_calldetail]
A few more notes:
Don't use SELECT *, specifically name the columns.
Try to avoid function calls to columns in a WHERE clause since this will force a table scan leading to bad performance.
The following statement can be rewritten:
AND DATEDIFF(dd,[c_detail].CallDetailCreationDate,[dbo].[fn_getnextbusinessday](GETDATE(),3)) >0
-- to
AND [c_detail].CallDetailCreationDate > [dbo].[fn_getnextbusinessday](GETDATE(),3)
AND ('352,368,276,389,215,369,332,217,250,391,353,390' is null ... is obsolete since a assigned string is not null.
Try to rewrite your function [dbo].fn_parse_accounts() and [dbo].[fn_getnextbusinessday]() to inline table-valued functions. If you need assistance, please post the code of both functions together with related table definition as well as some sample data to test against.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply