February 19, 2019 at 2:42 am
Hi,
Pl suggest , the changes in code snippets. The charindex is not behaving properly. Even for two values in one parameter, the output is not coming.
SET @Supplier_Code = ',' + @Supplier_Code + ','
SET @Supplier_Type = ','+ @Supplier_Type + ','
SET @PO_Type = ',' + @PO_Type + ','
SET @PO_Status = ',' + @PO_Status + ','
CREATE TABLE #Supplier_PO_Term_Variance
(
Plexus_Customer_No INT,
Supplier_Code NVARCHAr(MAX),
Supplier_Default_Payment_Term NVARCHAR(100),
PO_Number NVARCHAR(500),
PO_Date DATETIME,
PO_Due_Date DATETIME,
PO_Update_Date DATETIME,
PO_Payment_Term NVARCHAR(75),
PO_Total_Value NVARCHAR(100),
Issued_By NVARCHAR(100),
Approved_By NVARCHAR(100)
)
INSERT #Supplier_PO_Term_Variance
(
Plexus_Customer_No,
Supplier_Code,
Supplier_Default_Payment_Term,
PO_Number,
PO_Date,
PO_Due_Date,
PO_Update_Date,
PO_Payment_Term,
PO_Total_Value,
Issued_By,
Approved_By
)
SELECT
CS.Plexus_Customer_No,
CS.Supplier_Code,
CS.Terms,
PPO.PO_No,
PPO.PO_Date,
PPO.Due_Date,
PPO.Update_Date,
PPO.Terms,
NULL AS PO_Total_value,
Issued_By = PU.First_Name+ ' ' +PU.Last_Name,
Approved_By = PC.First_Name+ ' ' +PC.Last_Name
FROM Common_v_Supplier AS CS
JOIN Common_v_Supplier_Type AS CST
ON CST.Plexus_Customer_No = CS.Plexus_Customer_No
AND CST.Supplier_Type = CS.Supplier_Type
JOIN Purchasing_v_PO AS PPO
ON PPO.Plexus_Customer_No = CS.Plexus_Customer_No
AND PPO.Supplier_No = CS.Supplier_No
JOIN Purchasing_v_PO_Status AS PPS
ON PPS.Plexus_Customer_No = PPO.Plexus_Customer_No
AND PPS.PO_Status_Key = PPS.PO_Status_Key
JOIN Purchasing_v_PO_Type AS PPT
ON PPT.Plexus_Customer_No = PPO.Plexus_Customer_No
AND PPT.PO_Type_Key = PPO.PO_Type_Key
JOIN Plexus_Control_v_Plexus_User AS PU
ON PU.Plexus_Customer_No = PPO.Plexus_Customer_No
AND PU.Plexus_User_No = PPO.Issued_By
JOIN Plexus_Control_v_Plexus_User AS PC
ON PC.Plexus_Customer_No = PPO.Plexus_Customer_No
AND PC.Plexus_User_No = PPO.Approved_By
WHERE (@Supplier_Code = ',,' OR (CHARINDEX(',' + CAST(CS.Supplier_Code AS VARCHAR(MAX)) + ',', @Supplier_Code) > 0))
AND (@Supplier_Type = ',,' OR (CHARINDEX(',' + CAST(CST.Supplier_Type AS VARCHAR(MAX)) +' ,', @Supplier_Type) > 0))
AND (@PO_Type = ',,' OR (CHARINDEX(',' + CAST(PPT.PO_TYpe AS VARCHAR(MAX)) + ',', @PO_Type) > 0))
AND (@PO_Status = ',,' OR (CHARINDEX(',' + CAST(PPS.PO_Status AS VARCHAR(MAX)) + ',', @PO_Status) > 0))
AND CS.Terms != PPO.Terms
AND PPO.PO_Date >= @Start_Date AND PPO.PO_Date <= @End_Date
--@Workcenter_code = ',,'
-- OR
-- (CHARINDEX(',' + CAST(PW.Workcenter_Code AS VARCHAR(30)) + ',', @workcenter_code) > 0)
--)
--OPTION(FORCE ORDER)
SELECT
Plexus_Customer_No,
Supplier_Code,
Supplier_Default_Payment_Term,
PO_Number,
PO_Date,
PO_Due_Date,
PO_Update_Date,
PO_Payment_Term,
PO_Total_Value,
Issued_By,
Approved_By
FROM #Supplier_PO_Term_Variance
GROUP BY
Plexus_Customer_No,
Supplier_Code,
Supplier_Default_Payment_Term,
PO_Number,
PO_Date,
PO_Due_Date,
PO_Update_Date,
PO_Payment_Term,
PO_Total_Value,
Issued_By,
Approved_By
DROP TABLE #Supplier_PO_Term_Variance
February 19, 2019 at 10:19 am
English is obviously not your first language. This post has nothing to do with the forum that it was posted in and your description of the problem is almost unintelligible.
It looks like your data is not normalized properly. If at all possible, you should normalize your data. A good string splitter will help tremendously with that. Eirikur's solution is one of the best.
If you want additional help, you should post readily consumable date as outlined in the first link in my signature. NOTE: We cannot read your tables, so the query you posted doesn't count as readily consumable data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply