sql.in.clause ( )

Function stats

Average user rating
5.0000
37
361
9999
Support
FileMaker 8.0 +
Date posted
30 September 2013
Version
1
Recursive function
No

Author Info
 Fabrice

74 functions

Average Rating 4.4

author_avatar



 

Function overview

Prototype

sql.in.clause  ( _list;   _dataType )


Parameters

_list  


_dataType  "TEXT", "NUMBER", "DATE", "TIME", "TIMESTAMP"


Description

Tags:  Sql  

Builds the IN clause of a SELECT query
_dataType takes DATE, TIME, NUMBER, TIMESTAMP, TEXT

Examples

Sample input

sql.in.clause ( "" ; "TEXT" )
sql.in.clause ( List ( "Little" ; "Riding" ; "Hood" ; "TEXT" )
sql.in.clause ( List ( 1;2;3 ) ; "NUMBER" )


Sample output

" IS NULL"
" IN ('Little','Riding','Hood')"
" IN (1,2,3)"

 

Function code

/*
sql.in.clause ( _list ; _dataType )
by Fabrice Nordmann, 1-more-thing
http://www.1-more-thing.com

Builds the IN clause of a SELECT query
_dataType takes DATE, TIME, NUMBER, TIMESTAMP, TEXT

sql.in.clause ( "" ; "TEXT" ) => " IS NULL"
sql.in.clause ( List ( "Little" ; "Riding" ; "Hood" ; "TEXT" ) => " IN ('Little','Riding','Hood')"
sql.in.clause ( List ( 1;2;3 ) ; "NUMBER" ) => " IN (1,2,3)"

v.1 Aug 2013

*/

Case ( IsEmpty ( _list ) ; " IS NULL" ;
Let ([
    $cf.list = _list ;
    $datatype = Case ( IsEmpty ( _dataType ) ; "TEXT" ; _dataType ) ;
    $cf.list = CustomList ( 1 ; ValueCount ( _list ) ; "fm2sql.dataType ( getvalue ( $cf.list ; [n] ) ; $dataType )" ) ;
    _result = Case ( ValueCount ( _list ) = 1 ; "=" & $cf.list ; " IN (" & Substitute ( $cf.list ; ¶ ; "," ) & ")" ) ;
    $cf.list = "" ;
    $dataType = ""
];
_result
)
)

// ===================================
/*

    This function is published on FileMaker Custom Functions
    to check for updates and provide feedback and bug reports
    please visit http://www.fmfunctions.com/fid/361

    Prototype: sql.in.clause( _list; _dataType )
    Function Author: Fabrice (http://www.fmfunctions.com/mid/37)
    Last updated: 30 September 2013
    Version: 1

*/
// ===================================

 

Comments

Andres
07 November 2013



Thank you for sharing Fabrice! I just noticed this function requires another custom function from this site ("CustomList" by Agnès).

P.S.: it was great meeting you in POE Berlin and a privilege attending to your presentations.
  General comment