• Replies 4 replies
  • Subscribers 337 subscribers
  • Views 55 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

Parents
  • 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

Reply
  • 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

Children
No Data