locked
azure ADF Pipeline query RRS feed

  • Question

  • Hello,

    How to write where clause in select query.

    The below statement gives error for where clause, whats the syntax, I need to bring limited data from source table, please help

     "sqlReaderQuery": "$$Text.Format('Select OwningBusinessUnitName],[OwningTeam],[OwningTeamName],[StatusCodeName],[StateCodeName],[PriorityCode],[PriorityCodeName],[ActivityUrlLink],[Description] FROM Activities WHERE [CreatedOn] >= '016-02-01')"

    Wednesday, September 21, 2016 10:35 AM

All replies

  • This forum is for questions about Azure Search. I'll move this thread to the ADF forum.
    Wednesday, September 21, 2016 3:11 PM
  • You have the date formatted incorrectly. You're missing a '2' in the 2016. That might be what's causing the syntax error. Can you please try back and check if it solves the problem?

    Thanks,

    Romit.

    Wednesday, September 21, 2016 4:22 PM
  • Well that is not the issue, if I run without single quote in where clause it works however the where clause does not work.

    "$$Text.Format('Select OwningBusinessUnitName],[OwningTeam],[OwningTeamName],[StatusCodeName],[StateCodeName],[PriorityCode],[PriorityCodeName],[ActivityUrlLink],[Description] FROM Activities WHERE [CreatedOn] >= 2016-02-01')"

    If I add single quote in where clause, we have some error.

    "$$Text.Format('Select OwningBusinessUnitName],[OwningTeam],[OwningTeamName],[StatusCodeName],[StateCodeName],[PriorityCode],[PriorityCodeName],[ActivityUrlLink],[Description] FROM Activities WHERE [CreatedOn] >= '2016-02-01'')"

    Thursday, September 22, 2016 5:25 AM
  • you can just use as the following :

    "sqlreaderquery" : "Select OwningBusinessUnitName],[OwningTeam],[OwningTeamName],[StatusCodeName],[StateCodeName],[PriorityCode],[PriorityCodeName],[ActivityUrlLink],[Description] FROM Activities WHERE [CreatedOn] >= '2016-02-01'"


    Oliver Yao - MSFT

    Monday, October 24, 2016 9:51 AM
  • or use escape  character \\ if using $$text.format :

    "$$Text.Format('Select OwningBusinessUnitName],[OwningTeam],[OwningTeamName],[StatusCodeName],[StateCodeName],[PriorityCode],[PriorityCodeName],[ActivityUrlLink],[Description] FROM Activities WHERE [CreatedOn] >= \\'2016-02-01\\'')"


    Oliver Yao - MSFT

    Monday, October 24, 2016 9:54 AM