View previous topic :: View next topic
|
Author |
Message |
roopannamdhari Warnings : 1 New User
Joined: 14 Sep 2006 Posts: 71 Location: Bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
what were you trying to do with the concatenation operator? |
|
Back to top |
|
|
roopannamdhari Warnings : 1 New User
Joined: 14 Sep 2006 Posts: 71 Location: Bangalore
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
Hi Roopa,
Use ESCAPE in your SQL ...
Code: |
SELECT * FROM TABLE WHERE COL1 LIKE '%||R%' ESCAPE '|';
|
|
|
Back to top |
|
|
roopannamdhari Warnings : 1 New User
Joined: 14 Sep 2006 Posts: 71 Location: Bangalore
|
|
|
|
Ashimer,
i tried but it is not working i have to extract records having '|R' in column1 using cursor.
Thanks, |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
roopannamdhari Warnings : 1 New User
Joined: 14 Sep 2006 Posts: 71 Location: Bangalore
|
|
|
|
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 |
|
|
sudhakar84
New User
Joined: 20 Jun 2008 Posts: 25 Location: chennai
|
|
|
|
check the bind |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
What is the 'AND' doing in your cursor ? Are you sure the BIND was successful ? |
|
Back to top |
|
|
roopannamdhari Warnings : 1 New User
Joined: 14 Sep 2006 Posts: 71 Location: Bangalore
|
|
|
|
Hi,
no binding issues i checked. is it possible to use '|' symbole with cursor ?
Thanks, |
|
Back to top |
|
|
roopannamdhari Warnings : 1 New User
Joined: 14 Sep 2006 Posts: 71 Location: Bangalore
|
|
|
|
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
roopannamdhari Warnings : 1 New User
Joined: 14 Sep 2006 Posts: 71 Location: Bangalore
|
|
|
|
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 |
|
|
roopannamdhari Warnings : 1 New User
Joined: 14 Sep 2006 Posts: 71 Location: Bangalore
|
|
|
|
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 |
|
|
|