Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Column having values <STRING1>

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Nov 08, 2011 9:14 pm    Post subject: Column having values <STRING1>
Reply with quote

Hi all,

I have got a requirement to find the column names belong to a particular creator say DBAT which has values as %<STRING2> % cosidering the string2 can be anywhere in the column

Is there any better idea rather creating a Dynamic sql which I had thought of??

I thought to list down all columns from SYSIBM.SYSCOLUMNS then using the output of first query thought of running another dynamic Sql statement to search for the occurence of the string %<string2>% So the count(*) would tell fields having the string will be greater or equak to one

If anyone can guide me for a better approach will be helpful

Thanks
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Nov 08, 2011 9:41 pm    Post subject:
Reply with quote

in your where clause for the syscolumn select, only select char col as large or larger than you need to contain your search-string.

personally, i perfer the POSITION function POS(....) GT 0 to find the strings in each column. the pos value could be an offset for any substr function to update the column.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Nov 09, 2011 5:46 pm    Post subject:
Reply with quote

Hi Dbz,

Thanks.

My string is something like '%&%.%'
But not sure how far POS will be effective sorry for not being more expressive on my String
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Nov 09, 2011 6:55 pm    Post subject:
Reply with quote

can't use wildcards in the search key for POSITION or POSSTR.

though you could construct a lot of LIKE predicates,
being a bit&bytes type, i would

as you are pounding down thru the sysibmsyscolumns table cursor,
(sort by table, col pos, col len)
compare cursor_table to current_table in FROM clause
IF table name different,
  1. combine all the stored
    1. SELECT's
    2. the FROM
    3. the WHERE/AND clauses
  2. write the sql to file
  3. CLEAR ALL build areas

end-if


i would take your searchkey mask, and isolate the literal searchkey(s) from the wildcards.
i recently did this kind of thing and found that allowing only one type of wildcard made things easier.

parse/unstring the searchkey
generate a POS for each
then build parenthetically enclosed AND/OR's
if you only have one searchkey part, then you only need one POS
problem is if you have more than one, the second can not start until the first is found/not found.
Code:

WHERE POSSTR(column,part1) > 0
      AND
      POSSTR(column,part2) = 0
   OR (
      POSSTR(column,part1) > 0
      AND
      POSSTR(column,part2) > POSSTR(column,part1)
      AND
      POSSTR(column,part3) = 0
      )
   OR (
      POSSTR(column,part1) > 0
      AND
      POSSTR(column,part2) > POSSTR(column,part1)
      AND
      POSSTR(column,part3) > POSSTR(column,part2)
      AND     
      POSSTR(column,part4) = 0
      )


My code could generate unlimited sql for unlimited parts.
but that would get silly.
you would go out of your mind doing this by hand,
but you can generate a file containing all this sql,
then in batch, execute the sql, then parse the output sysouts
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Nov 09, 2011 7:00 pm    Post subject:
Reply with quote

l would strongly advise againt
executing the sql in an interactive session/ in the foreground.

and,
keep in mind, the part1, part2 ...etc need to be replaced with an apostrophied-enclosed string/field contents.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Nov 09, 2011 7:06 pm    Post subject:
Reply with quote

I was planning to combine the Sql with some Rexx and execute
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us