filter condition for virtual column

Question

i have the following sql select statement, I would like to check if it is possible to have the column rename with the as keyword in the where statement, for example the level_1_data column?

SELECT r.response_row_id, r.form_id, r.level_row, r.acknowledgement_status, 
 Coalesce(NullIf(r.level_data,''),(select level_data from ResponseRow where response_row_id=r.level_3_row_id)) as level_1_data,    
 r.level_3_data, r.level_4_data, r.level_3_row_id, r.workflow_status, r.responder_workflow_remark, r.responder_workflow_documents, 
 r.approver_workflow_remark, r.approver_workflow_documents, r.revision_count, r.created_by,r.created_by_name, r.created_Date, 
 r.last_modified_by, r.last_modified_by_name, r.last_modified_date, r.is_deleted, r.data_responder
 FROM ResponseRow r

Answer 1

To write “where level_1_data like '%some text%'”, try this approach too:

SELECT r.response_row_id, r.form_id, r.level_row, r.acknowledgement_status, 
   t.level_1_data,    
   . . .
FROM ResponseRow r
cross apply (values (coalesce(nullIf(r.level_data,''),(select level_data from ResponseRow where response_row_id = r.level_3_row_id)))) t(level_1_data)
where t.level_1_data like '%some text%'

Answer 2

Hi @Benjamin Chew ,

Welcome to Microsoft Q&A!

I would like to check if it is possible to have the column rename with the as keyword in the where statement, for example the level_1_data column?

Could you please provide more details about above?

If you would like to add the alias or rename the column in below part, it is impossible.

where response_row_id=r.level_3_row_id  

If you would like to add alias to the column like below, it is possible.

select level_data as level_1_data from ResponseRow where response_row_id=r.level_3_row_id  

If you would like to add the where condition in the final part like below, it would show some errors.

  FROM ResponseRow r  
  where level_1_data=1  

You could update above part like below:

 FROM ResponseRow r  
  where Coalesce(NullIf(r.level_data,''),(select level_data  from ResponseRow where response_row_id=r.level_3_row_id))=1  

If you still have some concern, we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, and your updated query desired. We also need to see the expected result of the sample.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

hi i am loooking at writing something like

 FROM ResponseRow r
   where level_1_data like '%some text%'

if I used it like this

FROM ResponseRow r
   where Coalesce(NullIf(r.level_data,''),(select level_data  from ResponseRow where response_row_id=r.level_3_row_id)) like '%some text%'

having 2 Coalesce and stuff, wondering if it will cause the performance to be affected as I need the Coalesce at the select for displaying of data.

Answer 4

Hi @Benjamin Chew ,

Thanks for your update.

Yes, COALESCE could hurt your performance.

If you would like to enhance your query, you could try with ISNULL function together with Top 1 like below:

 isnull(r.level_data,(select top 1 level_data from ResponseRow where response_row_id=r.level_3_row_id))  

Or you could also try with CASE WHEN statement instead.

Or you could even try with LEFT JOIN the table itself.

Mention that all of above could not improve your query a lot.

You could consider to try with other methods to improve the performance like creating indexes, update statistics and so on.

Hope above could be a little helpful to you.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.