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
 

 

wild-card characters in cursor

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: wild-card characters in cursor
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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: 21
Location: chennai

PostPosted: Tue Jul 22, 2008 3:00 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    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 Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
This topic is locked: you cannot edit posts or make replies. Sort card to replace text using cross... Susanta SYNCSORT 32 Tue Sep 13, 2016 4:17 pm
No new posts Creating a dynamic sort card in JCL u... ChitraChhabra DFSORT/ICETOOL 4 Wed Aug 03, 2016 6:15 pm
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am


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