Select with case-statement

I need some help with a (simple ?) query. Query: (just and example) ====== select case when tabauth[1,1] = 's' and tabauth[2,2] = 'u' and tabauth[3,3] = '-' and tabauth[4,4] = 'i' and tabauth[5,5] = 'd' then 'Select, Update, Insert, Delete' end from systabauth; The problem is I have hardcoded this for 1 condition: if flags are "su-id" There can be many possible combinations: su-i- su--- su--d ... ... etc. Is there a way to simplify this query to cater for all possible conditions, or do you have to hardcode for every possible combination ? Dirk --0016e6db2d7bfc5304047c90f85a

I would just use a SPL with a "while" and add the values as they appear in the flags. > I need some help with a (simple ?) query. > > Query: (just and example) > ====== > > select case> > when tabauth[1,1] = 's' and tabauth[2,2] = 'u' and tabauth[3,3] = > '-' and tabauth[4,4] = 'i' and tabauth[5,5] = 'd' then 'Select, Update, > Insert, Delete' > > end > from systabauth; > > The problem is I have hardcoded this for 1 condition: if flags are > "su-id" > > There can be many possible combinations: > su-i- > su--- > su--d > .... > .... > etc. > > Is there a way to simplify this query to cater for all possible conditions, > or do you have to hardcode for every possible combination ? > > Dirk > > --0016e6db2d7bfc5304047c90f85a > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ______________________________________________________ GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT! Jetzt freischalten unter http://movieflat.web.de

I don't think you can get away without including all cases separately. But, I have a question, what are you trying to accomplish? If you want to be able to generate a listing of just the privilege grant statements. you can do that with the latest release of myschema by adding the -g <authfile> option. Myschema will write all of the REVOKE and GRANT statements to the <authfile> for you. Art Art S. Kagel AdvanceDataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Thu, Jan 7, 2010 at 6:00 AM, Dirk Moolman <dirk.moolman@gmail.com> wrote: > I need some help with a (simple ?) query. > > Query: (just and example) > ====== > > select case> > when tabauth[1,1] = 's' and tabauth[2,2] = 'u' and tabauth[3,3] = > '-' and tabauth[4,4] = 'i' and tabauth[5,5] = 'd' then 'Select, Update, > Insert, Delete' > > end > from systabauth; > > The problem is I have hardcoded this for 1 condition: if flags are > "su-id" > > There can be many possible combinations: > su-i- > su--- > su--d > .... > .... > etc. > > Is there a way to simplify this query to cater for all possible conditions, > or do you have to hardcode for every possible combination ? > > Dirk > > --0016e6db2d7bfc5304047c90f85a > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517402b944c70c6047c91a186

Something dirty like the below will work - you might want to parse the output to tidy up tabs, trailing commas and so on, or modify for case etc: select unique case when lower(tabauth[1,1]) = "s" then "Select," end, case when lower(tabauth[2,2]) = "u" then "Update," end, case when tabauth[3,3] = "*" then "Columns," end, case when lower(tabauth[4,4]) = "i" then "Insert," end, case when lower(tabauth[5,5]) = "d" then "Delete," end, case when lower(tabauth[6,6) = "x" then "Index," end, case when lower(tabauth[7,7]) = "a" then "Alter," end, case when lower(tabauth[8,8]) = "r" then "Refs," end, case when lower(tabauth[9,9]) = "n" then "Priv," end,tabauth from systabauth

Our system generates new tables every month. Sometimes it happens that some of the tables don't have the correct privileges, and we get tickets on our names to add / fix those privileges. I have a script to query the privileges on tables (sometimes there will be more than one, for example: where tabname matches "billing*") I just wanted to make it a little neater (more user friendly) so that anyone running the script would understand the output, without having to understand the flags. From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 2010/01/07 01:47 PM Subject: Re: Select with case-statement [18593] Sent by: ids-bounces@iiug.org I don't think you can get away without including all cases separately. But, I have a question, what are you trying to accomplish? If you want to be able to generate a listing of just the privilege grant statements. you can do that with the latest release of myschema by adding the -g <authfile> option. Myschema will write all of the REVOKE and GRANT statements to the <authfile> for you. Art Art S. Kagel AdvanceDataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Thu, Jan 7, 2010 at 6:00 AM, Dirk Moolman <dirk.moolman@gmail.com> wrote: > I need some help with a (simple ?) query. > > Query: (just and example) > ====== > > select case> > when tabauth[1,1] = 's' and tabauth[2,2] = 'u' and tabauth[3,3] = > '-' and tabauth[4,4] = 'i' and tabauth[5,5] = 'd' then 'Select, Update, > Insert, Delete' > > end > from systabauth; > > The problem is I have hardcoded this for 1 condition: if flags are > "su-id" > > There can be many possible combinations: > su-i- > su--- > su--d > .... > .... > etc. > > Is there a way to simplify this query to cater for all possible conditions, > or do you have to hardcode for every possible combination ? > > Dirk > > --0016e6db2d7bfc5304047c90f85a > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517402b944c70c6047c91a186 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. --0016e6d976201d5e27047c93a1d5

My apologies. I haven't posted on this forum for 3 years. I see my "reply format" is incorrect. I will correct this in my future posts. Dirk ---------- Forwarded message ---------- From: Dirk Moolman <dirk.moolman@gmail.com> Date: Thu, Jan 7, 2010 at 4:10 PM Subject: Re: Select with case-statement [18593] To: ids@iiug.org Our system generates new tables every month. Sometimes it happens that some of the tables don't have the correct privileges, and we get tickets on our names to add / fix those privileges. I have a script to query the privileges on tables (sometimes there will be more than one, for example: where tabname matches "billing*") I just wanted to make it a little neater (more user friendly) so that anyone running the script would understand the output, without having to understand the flags. From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 2010/01/07 01:47 PM Subject: Re: Select with case-statement [18593] Sent by: ids-bounces@iiug.org I don't think you can get away without including all cases separately. But, I have a question, what are you trying to accomplish? If you want to be able to generate a listing of just the privilege grant statements. you can do that with the latest release of myschema by adding the -g <authfile> option. Myschema will write all of the REVOKE and GRANT statements to the <authfile> for you. Art Art S. Kagel AdvanceDataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Thu, Jan 7, 2010 at 6:00 AM, Dirk Moolman <dirk.moolman@gmail.com> wrote: > I need some help with a (simple ?) query. > > Query: (just and example) > ====== > > select case> > when tabauth[1,1] = 's' and tabauth[2,2] = 'u' and tabauth[3,3] = > '-' and tabauth[4,4] = 'i' and tabauth[5,5] = 'd' then 'Select, Update, > Insert, Delete' > > end > from systabauth; > > The problem is I have hardcoded this for 1 condition: if flags are > "su-id" > > There can be many possible combinations: > su-i- > su--- > su--d > .... > .... > etc. > > Is there a way to simplify this query to cater for all possible conditions, > or do you have to hardcode for every possible combination ? > > Dirk > > --0016e6db2d7bfc5304047c90f85a > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517402b944c70c6047c91a186 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. --0016e6d63f534410e9047c93aff8

Here's a script using myschema: #!/usr/bin/ksh myschema -d somedatabase -t $1 -g /tmp/auth$$ >/dev/null 2>&1 cat /tmp/auth$$ rm /tmp/auth$$ Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Thu, Jan 7, 2010 at 9:10 AM, Dirk Moolman <dirk.moolman@gmail.com> wrote: > Our system generates new tables every month. Sometimes it happens that some > of the tables don't have the correct privileges, and we get tickets on our > names to add / fix those privileges. > > I have a script to query the privileges on tables (sometimes there will be > more than one, for example: where tabname matches "billing*") > > I just wanted to make it a little neater (more user friendly) so that > anyone > running the script would understand the output, without having to > understand > the flags. > > From: "Art Kagel" <art.kagel@gmail.com> > To: ids@iiug.org > Date: 2010/01/07 01:47 PM > Subject: Re: Select with case-statement [18593] > Sent by: ids-bounces@iiug.org > > I don't think you can get away without including all cases separately. But, > I have a question, what are you trying to accomplish? If you want to be > able to generate a listing of just the privilege grant statements. you can > do that with the latest release of myschema by adding the -g <authfile> > option. Myschema will write all of the REVOKE and GRANT statements to the > <authfile> for you. > > Art > > Art S. Kagel > AdvanceDataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > 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 Thu, Jan 7, 2010 at 6:00 AM, Dirk Moolman <dirk.moolman@gmail.com> > wrote: > > > I need some help with a (simple ?) query. > > > > Query: (just and example) > > ====== > > > > select case> > > > when tabauth[1,1] = 's' and tabauth[2,2] = 'u' and tabauth[3,3] = > > '-' and tabauth[4,4] = 'i' and tabauth[5,5] = 'd' then 'Select, Update, > > Insert, Delete' > > > > end > > from systabauth; > > > > The problem is I have hardcoded this for 1 condition: if flags are > > "su-id" > > > > There can be many possible combinations: > > su-i- > > su--- > > su--d > > .... > > .... > > etc. > > > > Is there a way to simplify this query to cater for all possible > conditions, > > or do you have to hardcode for every possible combination ? > > > > Dirk > > > > --0016e6db2d7bfc5304047c90f85a > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001517402b944c70c6047c91a186 > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e6d976201d5e27047c93a1d5 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517475742a19c7f047c950199

----- Forwarded by Dc Dirk Moolman/South Africa/IBM on 2010/01/08 09:16 AM ----- From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 2010/01/07 05:49 PM Subject: Re: Select with case-statement [18611] Sent by: ids-bounces@iiug.org >Here's a script using myschema: > >#!/usr/bin/ksh >myschema -d somedatabase -t $1 -g /tmp/auth$$ >/dev/null 2>&1 >cat /tmp/auth$$ >rm /tmp/auth$$ > >Art > >Art S. Kagel >Advanced DataTools (www.advancedatatools.com) >IIUG Board of Directors (art@iiug.org) Thank you !

Here is one way of evaluating each flag position independently, joining them in a comma separated list, and removing the trailing comma. select replace( case when lower(tabauth[1]) = 's' then 'Select, ' else '' end || case when lower(tabauth[2]) = 'u' then 'Update, ' else '' end || case when tabauth[3] = '*' then 'Columns, ' else '' end || case when lower(tabauth[4]) = 'i' then 'Insert, ' else '' end || case when lower(tabauth[5]) = 'd' then 'Delete, ' else '' end || case when lower(tabauth[6]) = 'x' then 'Index, ' else '' end || case when lower(tabauth[7]) = 'a' then 'Alter, ' else '' end || case when lower(tabauth[8]) = 'r' then 'Refs, ' else '' end || case when lower(tabauth[9]) = 'n' then 'Priv, ' else '' end || 'ZZZZZ' ,', ZZZZZ','') from systabauth > Dirk Moolman wrote... >I need some help with a (simple ?) query. > >Query: (just and example) >====== > >select case> >when tabauth[1,1] = 's' and tabauth[2,2] = 'u' and tabauth[3,3] = >'-' and tabauth[4,4] = 'i' and tabauth[5,5] = 'd' then 'Select, Update, >Insert, Delete' > >end >from systabauth; > >The problem is I have hardcoded this for 1 condition: if flags are >"su-id" > >There can be many possible combinations: >su-i- >su--- >su--d >... >... >etc. > >Is there a way to simplify this query to cater for all possible conditions, >or do you have to hardcode for every possible combination ? > >Dirk