Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 Select Statement

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

New User


Joined: 19 Dec 2004
Posts: 24
Location: hyderabad

PostPosted: Thu Dec 20, 2007 11:30 pm    Post subject: DB2 Select Statement
Reply with quote

Hi,

Can some one help me to write select statement to retrive some data from table using Below conditions.

Select Field1, Field2
From Table
Where Field3 = 'Temp' and
Field4 contains PASSWORD, P/WORD, PASSWD, PASSWRD, PWORD, P\WORD, PSWRD, ####, ****, P/W, P/ WORD, P / WORD, P /WORD, P\ WORD, P \ WORD, P \WORD,

Here Field4 is 200 bytes and it contains memo's Text and i want retrive the Field1 and Field2 data if Field4 Text contains any of the strings above.
Back to top
View user's profile Send private message

Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 965
Location: Richmond, Virginia

PostPosted: Thu Dec 20, 2007 11:40 pm    Post subject:
Reply with quote

Read up on IN and Like, then try it and let us know.
Back to top
View user's profile Send private message
socker_dad

Active User


Joined: 05 Dec 2006
Posts: 147
Location: Columbia, MO

PostPosted: Fri Dec 21, 2007 3:40 am    Post subject: Reply to: DB2 Select Statement
Reply with quote

Try this

Code:

SELECT FIELD1, FIELD2
   FROM TABLE
  WHERE FIELD3 = 'TEMP'
  AND FIELD4 IN ('PASSWORD', 'P/WORD', 'PASSWD', 'PASSWRD',
          'PWORD', 'P\WORD', 'PSWRD', '####', '****',
          'P/W', 'P/ WORD', 'P / WORD', 'P /WORD', 'P\ WORD',
          'P \ WORD', 'P \WORD')
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Dec 21, 2007 8:58 am    Post subject:
Reply with quote

By calling the field "memo Text" I jumped to thinking this is MS Access where they don't use CHAR/VARCHAR/BLOB, but rather TEXT and MEMO.

Too bad SQL doesn't understand regular expressions so well. Please correct me if I am wrong on that. Then you could just do a match of the field to a regex.
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 965
Location: Richmond, Virginia

PostPosted: Fri Dec 21, 2007 6:22 pm    Post subject:
Reply with quote

I think there is a regular expression function - look it up.
Back to top
View user's profile Send private message
venkatarao

New User


Joined: 19 Dec 2004
Posts: 24
Location: hyderabad

PostPosted: Fri Dec 21, 2007 10:10 pm    Post subject: Reply to: DB2 Select Statement
Reply with quote

it is not worked out.

May be because of Field4 is defined as Varchar in my table.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Sun Dec 23, 2007 12:35 pm    Post subject: Re: Reply to: DB2 Select Statement
Reply with quote

venkatarao wrote:
it is not worked out.

May be because of Field4 is defined as Varchar in my table.


Try this to look for a substring within a string.

Code:
SELECT FIELD1, FIELD2
   FROM TABLE
  WHERE FIELD3 = 'TEMP'
  AND (POSSTR(FIELD4,'PASSWORD') <> 0
          OR POSSTR(FIELD4,'P/WORD') <> 0
          OR POSSTR(FIELD4,'PASSWD') <> 0
          OR POSSTR(FIELD4,'PASSWRD') <> 0
          OR POSSTR(FIELD4,'####') <> 0
          OR POSSTR(FIELD4,'P/WORD') <> 0
          OR POSSTR(FIELD4,'P/W') <> 0
          OR POSSTR(FIELD4,'P/ WORD') <> 0
          OR POSSTR(FIELD4,'P / WORD') <> 0
          OR POSSTR(FIELD4,'P /WORD') <> 0
          OR POSSTR(FIELD4,'P \WORD') <> 0
          OR POSSTR(FIELD4,'P\ WORD') <> 0
          OR POSSTR(FIELD4,'P \ WORD') <> 0
          OR POSSTR(FIELD4,'P \WORD') <> 0)
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 WER268A OUTREC STATEMENT : SYNTAX E... frozenblood87 SYNCSORT 12 Sat Aug 26, 2017 9:45 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts Suppress value reported by BREAK in D... Learncoholic DFSORT/ICETOOL 3 Wed Aug 16, 2017 6:03 pm
No new posts ON 2 AND EVERY 1 - Statement ??? UmeySan COBOL Programming 2 Tue Jul 25, 2017 1:20 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us