| Author |
 Topic  |
|
|
sent_sara
Constraint Violating Yak Guru
India
331 Posts |
 Posted - 02/03/2011 : 03:45:02
|
inorde to boost the performance,how to replace the like character:
[code]
declare @Param_St_Date datetime='01 feb 2005'
declare @Param_End_Date datetime='01 feb 2011'
declare @Recon_Type char(3)='All'
SELECT dbo.Get_CustName(B.Customer_Code) AS Cust_Name, B.Import_Reference, B.importdateid, B.AWB AS IMP_AWP, B.Custom_Ref, B.DN_Ref, B.ETA,
a.VAT_Rate, NULL AS Consignment, NULL AS Collection, NULL AS EXP_AWP, NULL AS Courier, NULL AS Courier_Ref, NULL AS Destination, NULL
AS Seal_No, NULL AS Dispatch, a.Recon_Status, SUM(B.IMP_Rate) AS IMP_Rate, SUM(B.Stones) AS IMP_Stone, SUM(B.Carats) AS IMP_Carats,
SUM(B.Value) AS IMP_Value, SUM(B.IMP_GBP) AS IMP_GBP, SUM(B.VAT_GBP) AS VAT_GBP, 0 AS Exp_Rate,
0 AS EXP_Stone, 0 AS EXP_Carats, 0 AS EXP_Value, 0 AS Exp_GBP
FROM Fact_Imp_Exp_Recon AS a INNER JOIN
Fact_Import AS B ON a.Imp_Refer = B.Import_Reference
WHERE (a.Imp_Acc_Date BETWEEN @Param_St_Date AND @Param_End_Date) AND
(a.Recon_Status LIKE CASE WHEN @Recon_Type = 'A' THEN '%' ELSE @Recon_Type END)
GROUP BY dbo.Get_CustName(B.Customer_Code), B.Import_Reference, B.importdateid, B.AWB, B.Custom_Ref, B.DN_Ref, B.ETA, a.Recon_Status,
a.VAT_Rate
[code] |
|
|
matty
Posting Yak Master
161 Posts |
Posted - 02/03/2011 : 04:13:46
|
quote:
Originally posted by sent_sara
inorde to boost the performance,how to replace the like character:
[code]
declare @Param_St_Date datetime='01 feb 2005'
declare @Param_End_Date datetime='01 feb 2011'
declare @Recon_Type char(3)='All'
SELECT dbo.Get_CustName(B.Customer_Code) AS Cust_Name, B.Import_Reference, B.importdateid, B.AWB AS IMP_AWP, B.Custom_Ref, B.DN_Ref, B.ETA,
a.VAT_Rate, NULL AS Consignment, NULL AS Collection, NULL AS EXP_AWP, NULL AS Courier, NULL AS Courier_Ref, NULL AS Destination, NULL
AS Seal_No, NULL AS Dispatch, a.Recon_Status, SUM(B.IMP_Rate) AS IMP_Rate, SUM(B.Stones) AS IMP_Stone, SUM(B.Carats) AS IMP_Carats,
SUM(B.Value) AS IMP_Value, SUM(B.IMP_GBP) AS IMP_GBP, SUM(B.VAT_GBP) AS VAT_GBP, 0 AS Exp_Rate,
0 AS EXP_Stone, 0 AS EXP_Carats, 0 AS EXP_Value, 0 AS Exp_GBP
FROM Fact_Imp_Exp_Recon AS a INNER JOIN
Fact_Import AS B ON a.Imp_Refer = B.Import_Reference
WHERE (a.Imp_Acc_Date BETWEEN @Param_St_Date AND @Param_End_Date) AND
(a.Recon_Status = CASE WHEN @Recon_Type = 'A' THEN a.Recon_Status ELSE @Recon_Type END)GROUP BY dbo.Get_CustName(B.Customer_Code), B.Import_Reference, B.importdateid, B.AWB, B.Custom_Ref, B.DN_Ref, B.ETA, a.Recon_Status,
a.VAT_Rate
[code]
|
 |
|
|
sent_sara
Constraint Violating Yak Guru
India
331 Posts |
Posted - 02/03/2011 : 07:11:31
|
thanks Matty
quote:
Originally posted by matty
quote:
Originally posted by sent_sara
inorde to boost the performance,how to replace the like character:
[code]
declare @Param_St_Date datetime='01 feb 2005'
declare @Param_End_Date datetime='01 feb 2011'
declare @Recon_Type char(3)='All'
SELECT dbo.Get_CustName(B.Customer_Code) AS Cust_Name, B.Import_Reference, B.importdateid, B.AWB AS IMP_AWP, B.Custom_Ref, B.DN_Ref, B.ETA,
a.VAT_Rate, NULL AS Consignment, NULL AS Collection, NULL AS EXP_AWP, NULL AS Courier, NULL AS Courier_Ref, NULL AS Destination, NULL
AS Seal_No, NULL AS Dispatch, a.Recon_Status, SUM(B.IMP_Rate) AS IMP_Rate, SUM(B.Stones) AS IMP_Stone, SUM(B.Carats) AS IMP_Carats,
SUM(B.Value) AS IMP_Value, SUM(B.IMP_GBP) AS IMP_GBP, SUM(B.VAT_GBP) AS VAT_GBP, 0 AS Exp_Rate,
0 AS EXP_Stone, 0 AS EXP_Carats, 0 AS EXP_Value, 0 AS Exp_GBP
FROM Fact_Imp_Exp_Recon AS a INNER JOIN
Fact_Import AS B ON a.Imp_Refer = B.Import_Reference
WHERE (a.Imp_Acc_Date BETWEEN @Param_St_Date AND @Param_End_Date) AND
(a.Recon_Status = CASE WHEN @Recon_Type = 'A' THEN a.Recon_Status ELSE @Recon_Type END)GROUP BY dbo.Get_CustName(B.Customer_Code), B.Import_Reference, B.importdateid, B.AWB, B.Custom_Ref, B.DN_Ref, B.ETA, a.Recon_Status,
a.VAT_Rate
[code]
|
|
|
| |
Topic |
|
|
|
Replacing like character
