Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Red face Unanswered: Modulo Operator????

    I have the following SQL statement"

    Code:
    DECLARE @SORT VARCHAR(256)
    DECLARE @FROM VARCHAR(8000)
    DECLARE @SELECT VARCHAR(8000)
    DECLARE @ON VARCHAR(8000)
    DECLARE @SQL VARCHAR(8000)
    DECLARE @WHERE VARCHAR(8000)
    
    SET @SORT = 'PaymentAmount'
    
    SET @SELECT = 'SELECT PY.PaymentId
    ,         PY.PayeeId
    ,         PY.PartyAddressId
    ,         PY.DistributionId
    ,         PY.EntitlementId
    ,         PY.DeliveryTypeEnumItemId
    ,         PY.AccountPaymentId
    ,         PY.ParentPaymentId
    ,         PY.PaymentAmount
    ,         PY.PaymentDate
    ,         PY.PaymentStatusEnumItemId
    ,         PY.PaymentStatusDate
    ,         PY.ReleaseRunId
    ,         PY.ReleaseDate
    ,         PY.AccountTransactionLogId
    ,         PY.AccountStatusEnumItemId
    ,         PY.AccountStatusDate
    ,         PY.AccountPaidAmount
    ,         PY.ReconciledInd
    ,         PY.UndeliverableInd
    ,         PY.ReissueNote
    ,         PY.CreateDate
    ,         PY.CreateId
    ,         PY.ModifiedDate
    ,         PY.ModifiedId
    ,         DS.Description
    ,         AC.Description
    ,         AC.AccountProvider
    ,         AC.AccountId
    ,         PT.Name
    ,         PA.AddressLine1
    ,         PA.AddressLine2
    ,         PA.City
    ,         PA.State
    ,         PA.Zip5
    ,         PA.Zip4
    ,         PE.clm_no
    ,         CM.clmnt_idno '
    
    
    
    SET @FROM = 'FROM Payment PY (NOLOCK)
              JOIN (SELECT DISTINCT 
                           PY.AccountPaymentId, PY.' + @SORT +  
                           ', ROW_NUMBER() OVER(ORDER BY PY.' + @SORT +') AS RowNum
                      FROM Payment PY (NOLOCK)) AS SQ ' 
    
    SET @ON = 'ON (SQ.AccountPaymentID = PY.AccountPaymentID)
              JOIN Distribution DS (NOLOCK)
                ON (DS.DistributionId = PY.DistributionId)
              JOIN Account AC (NOLOCK)
                ON (AC.AccountId = DS.AccountId)
              JOIN PartyAddress PA (NOLOCK)
                ON (PA.PartyAddressId = PY.PartyAddressId)
              JOIN Party PT (NOLOCK)
                ON (PT.PartyId = PA.PartyId)
              JOIN Payee PE (NOLOCK)
                ON (PE.PayeeId = PY.PayeeId)
              JOIN clm CM (NOLOCK)
                ON (CM.clm_no = PE.clm_no) ' 
    
    SET @WHERE = 'WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize
        AND ((@PayeeName IS NULL)       OR (PT.[Name] LIKE '''%''' + @PayeeName + '''%'''))
        AND ((@AccountId IS NULL)       OR (AC.AccountId = @AccountId))
        AND ((@DistributionId IS NULL)       OR (DS.DistributionId = @DistributionId))
        AND ((@PaymentDate IS NULL)       OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time
        AND ((@PaymentNumber IS NULL)       OR (PY.AccountPaymentId = @PaymentNumber))
        AND ((@IsReconciled IS NULL)       OR (PY.ReconciledInd = @IsReconciled))
        AND ((@AmountIssued IS NULL)       OR (PY.PaymentAmount = @AmountIssued))
        AND ((@AmountPaid IS NULL)       OR (PY.AccountPaidAmount = @AmountPaid))
        AND ((@IssueStatus IS NULL)       OR (PY.PaymentStatusEnumItemId = @IssueStatus))
        AND ((@AccountStatus IS NULL)       OR (PY.AccountStatusEnumItemId = @AccountStatus))
    	AND ((@PayeeId IS NULL)                   OR (PE.PayeeId = @PayeeId))
    	ORDER BY PY.' + @SORT 
    
    
    SET @SQL = @SELECT + @FROM + @ON --+ @WHERE
    
    SELECT @SQL
    When I try to execute it, I get the following error:

    Msg 402, Level 16, State 1, Line 71
    The data types varchar and varchar are incompatible in the modulo operator
    .

    It appears to not like this line:

    SET @WHERE = 'WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize

    Could anyone shed some light on why it's puking on this? And is you have any possible suggestions to get around this?

    Thanks a million!!!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the modulo operator is %

    it's probably here where the problem is occurring -- OR (PT.[Name] LIKE '''%''' + @PayeeName + '''%'''))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You got a bit "quote happy" at:
    Code:
        AND ((@PayeeName IS NULL)       OR (PT.[Name] LIKE '''%''' + @PayeeName + '''%'''))
    -PatP

  4. #4
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Quote happy....

    I like that!!!

    Thanks gents...that was the trick.

    Cheers, mates...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •