Skip to Content
0

New to SAP queries trying to Create a BP List using the Parameters

Feb 20 at 07:21 PM

72

avatar image
Former Member

Here is the query , only when we put in the CreateDate and the Slp Name , we are not returning all the results. it is filtering some out.

SELECT DISTINCT T0.[CardCode], T0.[CardName], T2.[State], T1.[SlpName] AS 'Regional Manager', T3.[GroupName] AS 'Customer Group', T0.[CreateDate] AS 'Date Created',T0.[U_U_Lead_Type] FROM OCRD T0 INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode] INNER JOIN CRD1 T2 ON T0.[CardCode] = T2.[CardCode] INNER JOIN OCRG T3 ON T0.[GroupCode] = T3.[GroupCode] WHERE (T1.[SlpName] = [%0]) And T0.[CardType] = [%1] And T0.[CardCode] NOT LIKE 'E%' And (T0.[CreateDate] >= [%2] and T0.[CreateDate]<=[%3])

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Johan Hakkesteegt Feb 21 at 01:48 PM
0

Hi Anthony,

By leaving the Sales Person blank, you are effectively searching for leads where a sales person was not determined.

Please try this:

SELECT DISTINCT T0.[CardCode]
              , T0.[CardName]
              , T2.[State]
              , T1.[SlpName] AS 'Regional Manager'
              , T3.[GroupName] AS 'Customer Group'
              , T0.[CreateDate] AS 'Date Created'
              , T0.[U_U_Lead_Type] 
FROM OCRD T0
     INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]
     INNER JOIN CRD1 T2 ON T0.[CardCode] = T2.[CardCode]
     INNER JOIN OCRG T3 ON T0.[GroupCode] = T3.[GroupCode] 
WHERE ISNULL(T1.[SlpName], '') LIKE '%[%0]%'
  AND T0.[CardType] = '[%1]'
  AND T0.[CardCode] NOT LIKE 'E%'
  AND T0.[CreateDate] BETWEEN [%2] AND [%3]

regards,

Johan

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Johan Hakkesteegt

Thank you that worked better, but it still is not pulling all results. it only gave us 22 results when there are actually 52

for the the choices of all sales people , Type = L and date range

0

Hi Anthony,

INNER JOINs will also effectively limit results. If you have double checked that the expected 52 leads all fall within the given parameters, then the following test should return 52 records. Please give this a try:

SELECT T0.[CardCode]
, T0.[CardName]
--, T2.[State]
, T1.[SlpName] AS'Regional Manager'
--, T3.[GroupName] AS'Customer Group'
, T0.[CreateDate] AS'Date Created'
, T0.[U_U_Lead_Type]
FROM OCRD T0
     INNER JOIN OSLP T1 ON T0.[SlpCode]= T1.[SlpCode]
     --INNER JOIN CRD1 T2 ON T0.[CardCode]= T2.[CardCode]
     --INNER JOIN OCRG T3 ON T0.[GroupCode]= T3.[GroupCode]
WHERE ISNULL(T1.[SlpName],'') LIKE '%[%0]%'
  AND T0.[CardType]='[%1]'
  AND T0.[CardCode] NOT LIKE 'E%'
  AND T0.[CreateDate] BETWEEN [%2] AND [%3]

Please note how I commented out some of the lines. If this test still does not return the expected leads, please use this method, first to comment out the last parameter, then the second-to-last, and finally the third-to-last. Run the query each time, and analyze the result set to see which records do not fall within the given parameters.

Regards,

Johan

0

Hi Anthony,

Your query uses "Select Distinct" statement, will that be why the duplicates are eliminated?

Will you be able to provide some screenshots of the results returned and those 52 records?

Thanks,

Jimmy

0
Jimmy Liang Feb 21 at 03:20 AM
0

Hi Anthony,

Your query has different conditions as well as parameters, such as 1) Sales person, 2) BP Type, 3) BP number not starting with "E", and 4) a creation date range.

Are these conditions correct, or would you mind to specify why the result returned is not right or not complete?

Thanks,

Jimmy

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 21 at 01:04 PM
0

Jimmy Liang,

Conditions are correct.

the results are if we choose Type = Lead leave the Sales Person Blank and then choose our date range. it is not pulling all the leads for that range and some of the those records that it does pull are duplicated.

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Feb 24 at 09:36 AM
0

Hi,

If you use CRD1 table, it pulls state from bill to and ship to field. You have to add condition to get from either ship to or bill to. Otherwise, the records are duplicated.

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded