How can I reference a calculated field in a subquery?

Stécy

I have the following query that is not valid but shows what I intend to do:

SELECT
    REPLACE(j.wo_id, 'PREFIX.', '') AS WOID,
    j.act_finish_time_local,
    STUFF ((SELECT ', '+item_id FROM bom_item WHERE parent_item_id = WOID AND bom_pos > 0 FOR XML PATH('')), 1, 1, '') AS Families
FROM job AS j
INNER JOIN job_event je ON je.wo_id = j.wo_id AND je.oper_id = j.oper_id 
WHERE j.oper_id = 'Sawing' AND j.wo_id LIKE 'PREFIX.%' AND j.act_finish_time_local IS NOT NULL 
GROUP BY j.wo_id, j.act_finish_time_local 
ORDER BY j.act_finish_time_local 

Here's sample data tables:

Table: job

| wo_id       | act_finish_time_local | oper_id |
|-------------|-----------------------|---------|
| PREFIX.0001 | 2014/01/01            | Sawing  |
| PREFIX.0002 | 2014/01/01            | Sawing  |

Table: job_event

| wo_id       | oper_id |
|-------------|---------|
| PREFIX.0001 | Sawing  |
| PREFIX.0002 | Sawing  |

Table: bom_item

| parent_item_id | item_id | bom_pos |
|----------------|---------|---------|
| 0001           | abc     | 1       |
| 0001           | def     | 2       |
| 0002           | qrs     | 1       |
| 0002           | tuv     | 2       |

Expected result

| WOID | act_finish_time_local | Families |
|------|-----------------------|----------|
| 0001 | 2014/01/01            | abc, def |
| 0002 | 2014/01/01            | qrs, tuv |

Now, the query does not compile as SQLServer is complaining about the WOID field not available.

Is there a way to fix this?

xeraphim

You cannot reference a calculated field or an alias in a subquery...

you have to calculate it again in the subquery or define it in a CTE and then re-use it outside the CTE

There is an interesting article about this topic which might be interesting for you

http://joecelkothesqlapprentice.blogspot.ch/2006/06/reference-alias-field-name.html

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How can I sum a group of a custom calculated field in SQL?

Access query is asking for parameters for calculated field, how can I stop this?

How can I implement Auto calculated field in mongodb?

How can I reference all elements in the JToken collection in a subquery

How can I use a subquery on my query results and then ORDER BY a calculated result for each row?

How can I access a specific field in a named subquery when the field name might not be unique?

How can I remove subquery?

How can I pass an extra calculated field in a Rails 7 controller response in this context

In Tableau how can I create a calculated field that categorizes data based on data across rows?

How can I use a calculated field in another calculation further down in the query?

How to add two different business objects into the same report, so that I can bring in a calculated field into the report, in Workday?

Date filter reference in a calculated field

How can I reference the value of a final static field in the class?

How I can reference a field defined in the gradle script?

How can I reference a field in MongoDB without knowing its name?

How can I swap in a new value for a field in a mutable reference to a structure?

How can I reference a "@typeparam TModel" field in the Blazor (sub)-component?

How can I populate the reference Field using Firestore

How can I reference to model field in Django using String?

Odoo 12 I can't change the value of the calculated field

Add an alias for a subquery that I can reference in the main WHERE clause

How can I animate a calculated position with JQuery

How can I add a custom method to auth_user model class? I need to return a calculated field along with results

In Django, how can I get a count of records based on a subquery of a subquery?

How to sort by calculated field?

How can I limit an entity reference selection to a specific field value on the reference

How can I transform a subquery to join in gorm?

How can I make a subquery with EXISTS on Knex?

How can I UPDATE more rows in a subquery?