• Replies 4 replies
  • Subscribers 337 subscribers
  • Views 53 views
  • Users 0 members are here
Related

Output Set Error Question

Adriana Law
$organization

I've having some trouble with an Output Set Element and can't figure out why it's returning the error Incorrect syntax near the keyword 'Where'.

I'm trying to create an output that concatenates a string of n=[FirstName]&d=[SUMDonationAmount]

This is what I have:

data_select       'n=' + b.fname + '&d=' + sum(a.cont_amt)
data_from         (SELECT DISTINCT b.customer_no, b.fname, a.* FROM vs_contribution a JOIN t_customer b on a.customer_no = b.customer_no)
data_where      a.cont_dt > dateadd(yy, -1, getdate())

Can anyone see where I'm going wrong?

Thanks in advance,

Adriana Law
Sydney Opera House

  • Former Member
    Former Member $organization

    Adrian

    I think you need to give your from statement an alias as you have bracketed a select statement.

    You're running this:

     

    SELECT 'n=' + b.fname + '&d=' + sum(a.cont_amt)

    FROM (SELECT DISTINCT b.customer_no, b.fname, a.* FROM vs_contribution a JOIN t_customer b on a.customer_no = b.customer_no)

    WHERE a.cont_dt > dateadd(yy, -1, getdate())

     

    And you're referencing the a and b tables within the bracketed select statement, which the outer SELECT and WHERE parts cannot access.

     

    You need to do something like this:

     

    SELECT 'n=' + x.fname + '&d=' + sum(x.cont_amt)

    FROM (SELECT DISTINCT b.customer_no, b.fname, a.* FROM vs_contribution a JOIN t_customer b on a.customer_no = b.customer_no) x

    WHERE x.cont_dt > dateadd(yy, -1, getdate())

     

    Give that a go and let me know if it works.

    Christine

  • $organization

    Hi Adriana,

    In addition to what Christine said, I think you also need to convert your sum(a.cont_amt) to varchar. Something like this:

     'n=' + b.fname + '&d=' + cast(sum(a.cont_amt) as varchar(10))

    Thanks,
    David 

  • $organization

    Hi Adrianna,

    1. have a.cont_amt instead of a.* ;
    2. when you use sum(cont_amt) , it must be "group by" clause, so have your SQL statement concluded by "group by fname"

    Simon

  • $organization in reply to Simon Basyuk

    Thanks all for your help on this - I had a chance to return to it last night and all is now well.

    Data select 'n=' + x.fname + '&d=' + cast(sum(x.cont_amt) as varchar(10))
    Data from (SELECT distinct b.fname, a.* FROM vs_contribution a JOIN t_customer b on a.customer_no = b.customer_no) x
    Data where x.cont_dt > dateadd(yy, -1, getdate()) group by x.fname

    Adriana