The automation works but keep looping and generating some reports but keep regenerating the same reports but does not completely generate all the required reports.
I am using Toad Data Point 4.3.
Loop Data Script:
SELECT [Vendor] FROM [ODW].[VR].[vw_Vendor_Recon_837_CDJ_Detail];
Loop Row:
DECLARE @ClaimLinkID varchar(50);
--Select Statement
SELECT DISTINCT
[Vendor]
,[Report Match Category]
-- Pull clean 837 claim number from ClaimLi
,REPLACE(RIGHT(ClaimLinkID,LEN(ClaimLinkID)-CHARINDEX(']',ClaimLinkID,1)-1),']','') as '837 Claim Number'
,[Vendor Claim Submission YM Net Paid Amount] as '837 Net Paid Amount'
,[CDJ Claim Nbr]
,[CDJ YM Total Paid Amount] as 'CDJ Total Paid Amount'
,[Vendor Claim Submission YM Net Paid Amount]-[CDJ YM Total Paid Amount] as 'Difference between 837 and CDJ Paid Amount'
,[Market]
,[LOB]
,[Claim Paid YearMonth] as 'Year Month'
FROM [ODW].[VR].[vw_Vendor_Recon_837_CDJ_Detail]
WHERE
--Get current date in YYYYMM as integer minus 1 month
-- [Claim Paid YearMonth] = (YEAR(GETDATE()) * 100) + MONTH(GETDATE())-1
[Claim Paid YearMonth] = '202106'
--AND [Report Match Category] NOT LIKE 'Exact Match%'
--Vendor List
AND ([Vendor] = :Loop_data_1_SQL.Vendor)
The code suggests you want 1 report per vendor for the month of June 2021. How is that loop variable set? Check your code for pulling the loop dataset. Make sure you are getting a distinct list of Vendors for the loop to step through. Use select distinct or use group by Vendor.
Thank you. I used SELECT DISTINCT in the Loop-Data part. Works like a charm.
Can I use this type of Looping to select specific vendors? Can I use the 'IN' clause in the Loop_Data or Loop_row? Is that possible?
Thank you. I used SELECT DISTINCT in the Loop-Data part. Works like a charm.
Can I use this type of Looping to select specific vendors? Can I use the 'IN' clause in the Loop_Data or Loop_row? Is that possible?
In statements can be tricky to set up as a variable. If you are trying to narrow down the vendors you want the loop to step through then put a where clause into your loop dataset query and there you can put in an In statement. With SQL Server you are limited to 1000 entries (Vendors) in the In Statement. You can get another 1000 with an Or In(another, 1000, entries) statement. Not the most efficient way of doing this if the list of vendors is huge. Generally if you are going over a 1000 for an In statement it is better to use a temp table or inline table (subquery) to get the list of vendors you want.