IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

wild-card characters in cursor


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
roopannamdhari
Warnings : 1

New User


Joined: 14 Sep 2006
Posts: 71
Location: Bangalore

PostPosted: Tue Jul 22, 2008 11:20 am
Reply with quote

Hi,

I am using cursor in a db2-program

Table description:-
column1 ==> char (40)
column2 ==> integer

column1 COLUMN2
|AANBC|R1234|XABD 1234

Cursor Query which I am using:-
[img]Select column1, column2 from table1
Where
Column1 like "%|R%" [/img]

This query is working in QMF and fetching 100 records
but in pgm it is giving 0 records

I tried removing pipe symbol "|" from query and it is working fine:
[img]Select column1,column2 from table1
where
column1 like "%R%" [/img]

Means it is not accepting "|" .Please let me know the reason

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 Jul 22, 2008 11:56 am
Reply with quote

what were you trying to do with the concatenation operator?
Back to top
View user's profile Send private message
roopannamdhari
Warnings : 1

New User


Joined: 14 Sep 2006
Posts: 71
Location: Bangalore

PostPosted: Tue Jul 22, 2008 12:01 pm
Reply with quote

Hi,

It is the way data stored in column1 (EX: |ABS|R1234|XABC ) . I have to extract records having "|R" in column1 so using where condition column1 like "%|R%".

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

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jul 22, 2008 2:04 pm
Reply with quote

Hi Roopa,

Use ESCAPE in your SQL ...

Code:


SELECT * FROM TABLE WHERE COL1 LIKE '%||R%' ESCAPE '|';

Back to top
View user's profile Send private message
roopannamdhari
Warnings : 1

New User


Joined: 14 Sep 2006
Posts: 71
Location: Bangalore

PostPosted: Tue Jul 22, 2008 2:18 pm
Reply with quote

Ashimer,

i tried but it is not working i have to extract records having '|R' in column1 using cursor.

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

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jul 22, 2008 2:26 pm
Reply with quote

Roopa,

Can you post the query which you have used ???
Cos i tried with the above posted query and i got the desired results ...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Jul 22, 2008 2:36 pm
Reply with quote

Packed-decimal data in char columns,
special char used to sub-divide columns.
vsam to db2 conversion? easy: each field is a column (char column, sometimes varchar), each vsam record becomes a row.

I see no need to worry about this kind of technology replacing me. It will probably implode due to the weight of BS.
Back to top
View user's profile Send private message
roopannamdhari
Warnings : 1

New User


Joined: 14 Sep 2006
Posts: 71
Location: Bangalore

PostPosted: Tue Jul 22, 2008 2:51 pm
Reply with quote

Ashimer,

Here is the query

EXEC SQL
DECLARE TABLE_CURSOR
CURSOR FOR
SELECT COLUMN2,
COLUMN1
FROM TABLE1
WHERE
COLUMN1 LIKE '%||R%' ESCAPE '|' AND
ORDER BY COLUMN2
FOR FETCH ONLY
END-EXEC

it is not fetching any records but the same query retriving records when executed in qmf.And one more i need to add ,it is not program issue i tried with out '|' symbol (like '%R%' ) it is retriving records.

thanks,
Back to top
View user's profile Send private message
sudhakar84

New User


Joined: 20 Jun 2008
Posts: 25
Location: chennai

PostPosted: Tue Jul 22, 2008 3:00 pm
Reply with quote

check the bind
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jul 22, 2008 3:03 pm
Reply with quote

What is the 'AND' doing in your cursor ? Are you sure the BIND was successful ?
Back to top
View user's profile Send private message
roopannamdhari
Warnings : 1

New User


Joined: 14 Sep 2006
Posts: 71
Location: Bangalore

PostPosted: Tue Jul 22, 2008 3:03 pm
Reply with quote

Hi,
no binding issues i checked. is it possible to use '|' symbole with cursor ?

Thanks,
Back to top
View user's profile Send private message
roopannamdhari
Warnings : 1

New User


Joined: 14 Sep 2006
Posts: 71
Location: Bangalore

PostPosted: Tue Jul 22, 2008 3:06 pm
Reply with quote

Ashimer,

Sorry it is my typo. Actual query is

EXEC SQL
DECLARE TABLE_CURSOR
CURSOR FOR
SELECT COLUMN2,
COLUMN1
FROM TABLE1
WHERE
COLUMN1 LIKE '%||R%' ESCAPE '|'
ORDER BY COLUMN2
FOR FETCH ONLY
END-EXEC

No AND operator

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

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jul 22, 2008 3:16 pm
Reply with quote

Hi,

You can use this in cursor also ... i tried with normal SELECT and CURSOR and is working fine for me ...

what are the Sqlcodes which you are getting after OPEN and fetch ?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Jul 22, 2008 3:17 pm
Reply with quote

Quote:

no binding issues i checked. is it possible to use '|' symbole with cursor


for a few weeks now, I have had the feeling that people are posting rather silly questions and continue to ignore anything posted.

as in intentionally trying to provoke a poster who is attempting to get thru to the OP. never works. we have 5 threads now that fall into that category.

If someone has so little knowledge that he must twice ask the same question that has been answered - twice - , i do not trust him to 'check his bind and insure tha there are no issues'. Besides the silliness in his cursor declaration 'AND' , would cause a keyword problem with order by.

it is hard not to say nasty things about people who either attempt to appear 'hard headed' or are just to stupid to read. This is like dealing with politcians. Politicians keep asking the same question until they get an answer they like.
Back to top
View user's profile Send private message
roopannamdhari
Warnings : 1

New User


Joined: 14 Sep 2006
Posts: 71
Location: Bangalore

PostPosted: Tue Jul 22, 2008 3:21 pm
Reply with quote

Hi,
i am not getting any error but it is not retriving any records.successfull execution with retrival of Zero record.

same query when executed in qmf retriving records .

whether it is retriving any records for you?

Thanks,
Back to top
View user's profile Send private message
roopannamdhari
Warnings : 1

New User


Joined: 14 Sep 2006
Posts: 71
Location: Bangalore

PostPosted: Tue Jul 22, 2008 3:31 pm
Reply with quote

Dick,

Have a look on my post which I posted soon after that query. For your reference I have pasted that message below.


EXEC SQL
DECLARE TABLE_CURSOR
CURSOR FOR
SELECT COLUMN2,
COLUMN1
FROM TABLE1
WHERE
COLUMN1 LIKE '%||R%' ESCAPE '|'
ORDER BY COLUMN2
FOR FETCH ONLY
END-EXEC

No AND operator
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Substring number between 2 characters... DFSORT/ICETOOL 2
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts Reading dataset in Python - New Line ... All Other Mainframe Topics 22
No new posts Count the number of characters in a f... CA Products 1
No new posts Generate output lines (SYSIN card for... DFSORT/ICETOOL 4
Search our Forums:

Back to Top