Fwd: problem SQL...

We are trying to use sql to create a dynamic case statement. This statement is meant to be accessed via a web service. The structure of the statement is simply an attempt to be able to refer to a given parameter multiple times without having to repeat the parameter on the input side. This would enable a developer to create a single SQL statement that can handle variations in its use (i.e. such as lastname = parm1 and firstname contains parm2 and ignore the date of birth) - without this the developer would create myriad versions of the statement for each variation of the parameters, and would have to map the service call through a bunch of conditional branches in order to execute the right statement. This approach would allow a direct mapping from service call to SQL statement. A 'simplified' version of the statement is listed below - with only one set of options for last name: SELECT * FROM ( SELECT FIRST 30 p.party_id, p.last_name, p.phonetic_last, p.phonetic_first, p.first_name, p.middle_name, birth_date, 'HANSE' AS pLastName, --The literal "HANSE" would be passed as a parameter 'CONTAINS' AS pLastNameOption, --The literal 'CONTAINS' would be passed as a parameter FROM party p ) WHERE CASE WHEN pLastNameOption = 'IGNORE' OR last_name IS NULL THEN 1 WHEN pLastNameOption = 'EQUALS' THEN CASE WHEN last_name = pLastName THEN 1 ELSE 0 END WHEN pLastNameOption = 'GREATERTHAN' THEN CASE WHEN last_name LIKE pLastName || '%' THEN 1 ELSE 0 END WHEN pLastNameOption = 'CONTAINS' THEN CASE WHEN last_name LIKE '%' || pLastName || '%' THEN 1 ELSE 0 END WHEN pLastNameOption = 'PHONETIC' THEN CASE WHEN phonetic_last = phonetic(pLastName) THEN 1 ELSE 0 END END = 1 Is this possible without a stored procedure? Thanks Laurie Gustin IT Programmer Analyst Department of Public Safety lgustin@utah.gov 801-965-4410

In 11.50+ you can create the SQL dynamically in a stored procedure depending upon the inputs, indeed, you could either use conditional arguments to the procedure or you could have different procedures with different argument lists with the same name. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 16, 2011 at 4:07 PM, Laurie Gustin <lgustin@utah.gov> wrote: > We are trying to use sql to create a dynamic case statement. This statement > is > meant to be accessed via a web service. The structure of the statement is > simply an attempt to be able to refer to a given parameter multiple times > without having to repeat the parameter on the input side. > > This would enable a developer to create a single SQL statement that can > handle > variations in its use (i.e. such as lastname = parm1 and firstname contains > parm2 and ignore the date of birth) - without this the developer would > create > myriad versions of the statement for each variation of the parameters, and > would have to map the service call through a bunch of conditional branches > in > order to execute the right statement. This approach would allow a direct > mapping from service call to SQL statement. > > A 'simplified' version of the statement is listed below - with only one set > of > options for last name: > > SELECT > > * > FROM > ( > > SELECT FIRST 30> > p.party_id, > > p.last_name, > > p.phonetic_last, > > p.phonetic_first, > > p.first_name, > > p.middle_name, > > birth_date, > > 'HANSE' AS pLastName, --The literal "HANSE" would be passed as a parameter > > 'CONTAINS' AS pLastNameOption, --The literal 'CONTAINS' would be passed as > a > parameter > > FROM > > party p > ) > WHERE > > CASE > > WHEN pLastNameOption = 'IGNORE' OR last_name IS NULL > > THEN 1 > > WHEN pLastNameOption = 'EQUALS' > > THEN CASE WHEN last_name = pLastName THEN 1 ELSE 0 END > > WHEN pLastNameOption = 'GREATERTHAN' > > THEN CASE WHEN last_name LIKE pLastName || '%' THEN 1 ELSE 0 END > > WHEN pLastNameOption = 'CONTAINS' > > THEN CASE WHEN last_name LIKE '%' || pLastName || '%' THEN 1 ELSE 0 END > > WHEN pLastNameOption = 'PHONETIC' > > THEN CASE WHEN phonetic_last = phonetic(pLastName) THEN 1 ELSE 0 END > > END = 1 > > Is this possible without a stored procedure? > > Thanks > > Laurie Gustin > IT Programmer Analyst > Department of Public Safety > lgustin@utah.gov > 801-965-4410 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec519698ba1d027049ea07688

On 16/03/2011 20:07, Laurie Gustin wrote: > We are trying to use sql to create a dynamic case statement. This statement is > meant to be accessed via a web service. The structure of the statement is > simply an attempt to be able to refer to a given parameter multiple times > without having to repeat the parameter on the input side. > > This would enable a developer to create a single SQL statement that can handle > variations in its use (i.e. such as lastname = parm1 and firstname contains > parm2 and ignore the date of birth) - without this the developer would create > myriad versions of the statement for each variation of the parameters, and > would have to map the service call through a bunch of conditional branches in > order to execute the right statement. This approach would allow a direct > mapping from service call to SQL statement. > > A 'simplified' version of the statement is listed below - with only one set of > options for last name: > > SELECT > > * > FROM > ( > > SELECT FIRST 30> > p.party_id, > > p.last_name, > > p.phonetic_last, > > p.phonetic_first, > > p.first_name, > > p.middle_name, > > birth_date, > > 'HANSE' AS pLastName, --The literal "HANSE" would be passed as a parameter > > 'CONTAINS' AS pLastNameOption, --The literal 'CONTAINS' would be passed as a > parameter > > FROM > > party p > ) > WHERE > > CASE > > WHEN pLastNameOption = 'IGNORE' OR last_name IS NULL > > THEN 1 > > WHEN pLastNameOption = 'EQUALS' > > THEN CASE WHEN last_name = pLastName THEN 1 ELSE 0 END > > WHEN pLastNameOption = 'GREATERTHAN' > > THEN CASE WHEN last_name LIKE pLastName || '%' THEN 1 ELSE 0 END > > WHEN pLastNameOption = 'CONTAINS' > > THEN CASE WHEN last_name LIKE '%' || pLastName || '%' THEN 1 ELSE 0 END > > WHEN pLastNameOption = 'PHONETIC' > > THEN CASE WHEN phonetic_last = phonetic(pLastName) THEN 1 ELSE 0 END > > END = 1 > > Is this possible without a stored procedure? What version? Recent versions allow you to build a string and execute it. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish.