BAQ SQL Error

aarong · October 9, 2020, 1:32pm

Hello everyone

Any one able to help me with this one?

I’m getting a SQL error when trying to calculate the job number.

Error:

Thanks
Aaron

Doug.C · October 9, 2020, 2:02pm

In SubQuery1, the Calculation for Jobs references {SubQuery3}. When you include a subquery in a calculation the subquery can only return 1 value.

Hally · October 9, 2020, 2:12pm

@aarong I have got to say that the subquery 3 is just flapping out in the breeze and doesn’t appear to be being used at all, not that I can see anyway.

aarong · October 9, 2020, 2:17pm

It’s referenced in TopLevel through a calculated field.

aarong · October 9, 2020, 2:18pm

@Doug.C What do you mean? I’ve used this method previously and it’s worked fine.

Doug.C · October 9, 2020, 2:20pm

I’m assuming by the coding that SubQuery3 is supposed to return one cell with a bunch of Jobs listed between <Calculated_JobNum></Calculated_JobNum>?

aarong · October 9, 2020, 2:23pm

Indeed

Hally · October 9, 2020, 2:28pm

You mean

When you simulate subquery 3 in sql on my server I get.

aarong · October 9, 2020, 2:29pm

Have you removed the filtering?

Doug.C · October 9, 2020, 2:29pm

You seem to be missing the FOR XML PATH clause.

Hally · October 9, 2020, 2:32pm

I’ve got no active trans in my data setting that to 0 gives me stuff.

aarong · October 9, 2020, 2:33pm

How could I be so dumb!

Doug.C · October 9, 2020, 2:35pm

I got the same error when I tried to run it in SQL without that specified.

aarong · October 9, 2020, 2:36pm

Hero, I totally forgot that bit.

Hally · October 9, 2020, 2:53pm

Thanks I just learnt something