question

BenjaminChew-3397 avatar image
0 Votes"
BenjaminChew-3397 asked MelissaMa-msft commented

filter condition for virtual column

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


sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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%'

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @BenjaminChew-3397,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BenjaminChew-3397 avatar image
0 Votes"
BenjaminChew-3397 answered MelissaMa-msft commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BenjaminChew-3397,

Could you please validate below answers and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @BenjaminChew-3397,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.