Skip to Content
avatar image
Former Member

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

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])

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Feb 21 at 01:48 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Feb 21 at 03:20 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 21 at 01:04 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 24 at 09:36 AM

    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

    Add comment
    10|10000 characters needed characters exceeded