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
 

 

using host varible with pipe character

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

New User


Joined: 23 Aug 2006
Posts: 13

PostPosted: Thu Jun 17, 2010 3:49 pm    Post subject: using host varible with pipe character
Reply with quote

Hi,
Can we join values stored in the host variables thru !! (pipe) character in sql

For example:

select xxx
from yyyy
where
:ws1 !! :ws2 > :ws3
and other condition
:

will above work in cobol?
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Thu Jun 17, 2010 3:51 pm    Post subject:
Reply with quote

Yes, well, I'm not too sure that you will get too many answers posting a SQL question in the DFSORT forum.

Please connect eyes, fingers and brain before posting icon_rolleyes.gif
Back to top
View user's profile Send private message
sriram.mukundan

New User


Joined: 20 Nov 2007
Posts: 28
Location: chennai

PostPosted: Thu Jun 17, 2010 3:58 pm    Post subject: Re: using host varible with pipe character
Reply with quote

I dont think so, you can use this kind of operators in E-SQL. I'm quite sure like this will end in error.


Quote:
!! (pipe) character
. Its NOT pipe operator.

This is PIPE operator(||)
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Thu Jun 17, 2010 4:00 pm    Post subject: Re: using host varible with pipe character
Reply with quote

sriram.mukundan wrote:
Quote:
!! (pipe) character
. Its NOT pipe operator.

This is PIPE operator(||)

Not entirely true, at one site that I worked at, the exclamation mark was the valid PIPE operator.
Back to top
View user's profile Send private message
sushidas
Warnings : 2

New User


Joined: 23 Aug 2006
Posts: 13

PostPosted: Thu Jun 17, 2010 4:08 pm    Post subject: Reply to: using host varible with pipe character
Reply with quote

Even if i consider ||, will it work in the above query..
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Thu Jun 17, 2010 4:16 pm    Post subject: Reply to: using host varible with pipe character
Reply with quote

what happened when You tried

You do not show any column name, just host variables

it would be better to carry on the computations on host varables outside the query

Code:
if ws1 !! ws2 > ws3 then ,
    issue the query
Back to top
View user's profile Send private message
AjmalMohammed

New User


Joined: 02 Jun 2010
Posts: 10
Location: My Cubicle, Hyderabad, India

PostPosted: Thu Jun 17, 2010 4:23 pm    Post subject:
Reply with quote

expat wrote:
Yes, well, I'm not too sure that you will get too many answers posting a SQL question in the DFSORT forum.

Please connect eyes, fingers and brain before posting icon_rolleyes.gif


Isnt this the DB2 forum?
Back to top
View user's profile Send private message
sushidas
Warnings : 2

New User


Joined: 23 Aug 2006
Posts: 13

PostPosted: Thu Jun 17, 2010 4:27 pm    Post subject: Reply to: using host varible with pipe character
Reply with quote

the problem here is, the sql is something like below which runs in qmf well but giving sqlcode 100 in a cobol db2 code where i need to code it.
So i thought that rest of the compulation i will do outside of this sql and store in a host variable and use it in the below sql as i have to compare it with a column value. Using too much fuctions also give performamce problem and it better to avoid it. and so i am looking for a exact syntax or want to know if i can use it.



Code:
AND SUBSTR(CHAR(CURRENT DATE),1,4)        || '-' || 
    SUBSTR(CHAR(B.RNEWAL_DT),6,2) || '-' || 
    SUBSTR(CHAR(B.RNEWAL_DT),9,2)           
    BETWEEN CHAR(CURRENT DATE + 7 DAYS)             
      AND   CHAR(CURRENT DATE + 9 DAYS)
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jun 17, 2010 4:29 pm    Post subject:
Reply with quote

I can understand why the TS did not bother to refer to a manual,
his vocabulary is so poor he could not find the answer, even if he looked.

2.21.3 With the concatenation operator

Note 12 (12) from above link.

Besides, a pipe character is ONE vertical Bar..........
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Thu Jun 17, 2010 4:33 pm    Post subject:
Reply with quote

AjmalMohammed wrote:
expat wrote:
Yes, well, I'm not too sure that you will get too many answers posting a SQL question in the DFSORT forum.
Please connect eyes, fingers and brain before posting icon_rolleyes.gif

Isnt this the DB2 forum?


As indicated above Moved: Thu Jun 17, 2010 11:21 am by expat From DFSORT/ICETOOL to DB2
Back to top
View user's profile Send private message
AjmalMohammed

New User


Joined: 02 Jun 2010
Posts: 10
Location: My Cubicle, Hyderabad, India

PostPosted: Thu Jun 17, 2010 6:37 pm    Post subject:
Reply with quote

oops, my bad icon_redface.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Jun 18, 2010 2:31 pm    Post subject:
Reply with quote

the where clause is sh*t
and wrong for current_date between 23/dec thru 31/dec

depending on the case
I would recommend something like this :

Code:
with dts (d) as
(select current_date + 7 days from sysibm.sysdummy1
union all
select current_date + 8 days from sysibm.sysdummy1
union all
select current_date + 9 days from sysibm.sysdummy1
union all
select d - 1 year from dts
where d > current_date - 30 years)

select * from TAB1 B
, dts
where b.rnewal_dt = dts.d

or
Code:
with dts (d) as
(select current_date + 7 days from sysibm.sysdummy1
union all
select current_date + 8 days from sysibm.sysdummy1
union all
select current_date + 9 days from sysibm.sysdummy1
union all
select d - 1 year from dts
where d > current_date - 30 years)

select * from TAB1 B
where b.rnewal_dt in (select d from dts)

but there might be a problem with 29/feb
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 SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts To trim spaces in a pipe delimited da... Nish84 COBOL Programming 16 Mon Oct 10, 2016 1:54 pm
This topic is locked: you cannot edit posts or make replies. DSList (3.4) cannot display Chinese c... jacobdng TSO/ISPF 4 Wed Oct 05, 2016 2:51 pm
No new posts Format to pipe delimited with variabl... pshongal SYNCSORT 6 Wed Sep 14, 2016 2:48 pm
No new posts Continuation Character ballaswaroop SYNCSORT 8 Thu Aug 25, 2016 4:46 pm


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