Output set run errors

Hi Naomi!

If you can post your From column, that would help answer this more specifically.  Most likely, you just need to tack on "GROUP BY customer_no" in some form or other.  I'm guessing that your element is trying to pull a sum or some other formulaic thing and SQL is just confused without this clause in the statement.

Hi Nick,

I hope I am responding appropriately. I have attached a screen shot of the output set that is giving the error. You cannot see the from column. However I did run the stored procedure in SSMS and attached the SQL code that is returned when I run rp_run_query with the parameter of showsql='Y'.

The sql statement is created "dynamically" when the stored procedure is ran.

Select work.customer_no,  'Promotion Source Number' = a.source_no,  'Customer_fname' = customer.fname,  'Customer_lname' = customer.lname,  'Phone_1' = work.phone1,  'Eaddress_email' = work.email,  'Tickets-Number of Seats' = b.num_seats,  'Tickets-Total Amount' = sum(b.tck_amt)  From #work1 work JOIN t_customer customer ON work.customer_no = customer.customer_no   Left Outer Join vs_promotion a ON work.customer_no = a.customer_no  Left Outer Join lvs_tkt_hist b ON work.customer_no = b.customer_no 

Thanks Nick!

Depending on how these are actually set up, your culprit could be "Tickets-Number of Seats" or "Tickets-Total Amount".  Probably the latter.

If you go to TR_QUERY_ELEMENT, look for these two elements.  Towards the right there will be a SELECT column and a FROM column.  In the SELECT column, do you see anything like "SUM(x)"?  If so, look over to the FROM column and see if you have GROUP BY x anywhere there. 

Nick,

In T_QUERY_ELEMENT “Tickets-Total Amount” does have sum(!.tck_amt) and the from column is lvs_tkt_hist, however there is no group by clause, that is great but how do we fix that and keep it dynamic for everyone?

Thanks for your help.

Naomi

Hi Naomi!

Actually, now that I see your values - I don't think you need to add anything.  Just delete the "!." in "sum(!.tck_amt)" and that will correct the error.  Just to be sure it works, you can test this after you make this change by running an output set just for Tickets-Total Amount.

That did it! Thanks Nick!

Naomi

Hi,

We also had this issue.

Great to be able to find the solution here. Thanks.

Maybe this is something that needs to be scripted in the next update patch?

thanks,

Dara

Nick and Naomi,

Thanks for posting.  Solved my problem!  Good deal...  Should that be submitted as a bug or is there a reason for the !.?

Thanks

JB

So glad this post has been useful!

It's not actually a bug and is described in the System Tables document in the TR_QUERY_ELEMENT section.

Here is the explaination:

Data Select – The column name or expression from which data is pulled for the element.   This expression can either be an aggregate expression (e.g. sum(cont_amt)) which will return one value per constituent or it can be a non-aggregate expression which may return one or more than one row per constituent.  If this is a non-aggregate expression, ! must be used as a placeholder for the eventual table alias (e.g. !.cont_amt).

So glad this post has been useful!

It's not actually a bug and is described in the System Tables document in the TR_QUERY_ELEMENT section.

Here is the explaination:

Data Select – The column name or expression from which data is pulled for the element.   This expression can either be an aggregate expression (e.g. sum(cont_amt)) which will return one value per constituent or it can be a non-aggregate expression which may return one or more than one row per constituent.  If this is a non-aggregate expression, ! must be used as a placeholder for the eventual table alias (e.g. !.cont_amt).