BookmarkSubscribeRSS Feed

Community Trekker


Apr 18, 2012

Simple SQL Statement


I'm trying to use a sql statement that checks for three conditions and using this;

The JMP Table already exists so no problem with that.

The first statement forecaster like is to check for a forecaster's name in the data table and second is doing a week comparison and the third is checking if the values under the adherence column is greater than 0. The problem lies in the first condition Forecaster LIKE...I know that for sure because the statement is running fine when I'm taking out the Forecaster like String comparison. Please advise. 

(Data Table( "JMP Table$" ) << Select Where ( And( Forecaster LIKE 'xyz%',Week >= 201201,Adherence > 0 ) )



Super User


Jun 23, 2011

Re: Simple SQL Statement

JMP doesn't have a LIKE statement.  You can use contains, but that is equivalent to LIKE '%xyz%'.  So, to get a string that starts with xyz you can use regular expressions:

dt << Select Where ( regex(:Forecaster, "^xyz") == "xyz"

  & :Week >= 201201

  &:Adherence > 0 )