View previous topic :: View next topic
|
Author |
Message |
sushidas Warnings : 2 New User
Joined: 23 Aug 2006 Posts: 13
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
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 |
|
Back to top |
|
|
sriram.mukundan
New User
Joined: 20 Nov 2007 Posts: 28 Location: chennai
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
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 |
|
|
sushidas Warnings : 2 New User
Joined: 23 Aug 2006 Posts: 13
|
|
|
|
Even if i consider ||, will it work in the above query.. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
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 |
|
|
AjmalMohammed
New User
Joined: 02 Jun 2010 Posts: 10 Location: My Cubicle, Hyderabad, India
|
|
|
|
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 |
Isnt this the DB2 forum? |
|
Back to top |
|
|
sushidas Warnings : 2 New User
Joined: 23 Aug 2006 Posts: 13
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
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 |
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 |
|
|
AjmalMohammed
New User
Joined: 02 Jun 2010 Posts: 10 Location: My Cubicle, Hyderabad, India
|
|
|
|
oops, my bad |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
|