i have created a view like this
create view xxx.GLJMSTR1 (GLAM01, GLAM02, GLBL01, GLBL02, GLCC02,
GLCC04, GLCD14, GLCD15, GLDN03, GLDY02, GLDY04, GLFL03, GLMO02,
GLMO04, GLNM02, GLNO01, GLNO02, GLNO03, GLNO04, GLNO06, GLNO07,
GLNO08, GLNO27, GLYR02, GLYR04, GLAM03, GLCC03, GLCD13, GLDN04,
GLDY03, GLMO03, GLNO05, GLNO10, GLYR03, systimestamp, loginname, id )
as select xxx.GLPMSTR.GLAM01, xxx.GLPMSTR.GLAM02, xxx.GLPMSTR.GLBL01,
xxx.GLPMSTR.GLBL02, xxx.GLPMSTR.GLCC02, xxx.GLPMSTR.GLCC04,
xxx.GLPMSTR.GLCD14, xxx.GLPMSTR.GLCD15, xxx.GLPMSTR.GLDN03,
xxx.GLPMSTR.GLDY02, xxx.GLPMSTR.GLDY04, xxx.GLPMSTR.GLFL03,
xxx.GLPMSTR.GLMO02, xxx.GLPMSTR.GLMO04, xxx.GLPMSTR.GLNM02,
xxx.GLPMSTR.GLNO01, xxx.GLPMSTR.GLNO02, xxx.GLPMSTR.GLNO03,
xxx.GLPMSTR.GLNO04, xxx.GLPMSTR.GLNO06, xxx.GLPMSTR.GLNO07,
xxx.GLPMSTR.GLNO08, xxx.GLPMSTR.GLNO27, xxx.GLPMSTR.GLYR02,
xxx.GLPMSTR.GLYR04, xxx.GLPMTRN.GLAM03, xxx.GLPMTRN.GLCC03,
xxx.GLPMTRN.GLCD13, xxx.GLPMTRN.GLDN04, xxx.GLPMTRN.GLDY03,
xxx.GLPMTRN.GLMO03, xxx.GLPMTRN.GLNO05, xxx.GLPMTRN.GLNO10,
xxx.GLPMTRN.GLYR03, xxx.GLPMTRN.systimestamp, xxx.GLPMTRN.loginname,
COALESCE(xxx.GLPMSTR.id, xxx.GLPMTRN.id ) from xxx.GLPMSTR left join
xxx.GLPMTRN on xxx.GLPMSTR.GLNO01 = xxx.GLPMTRN.GLNO01 and
xxx.GLPMSTR.GLNO03 = xxx.GLPMTRN.GLNO03 and xxx.GLPMSTR.GLNO04 =
xxx.GLPMTRN.GLNO04 and xxx.GLPMSTR.GLNO06 = xxx.GLPMTRN.GLNO06 and
xxx.GLPMSTR.GLNO02 = xxx.GLPMTRN.GLNO02 and xxx.GLPMSTR.GLNO07 =
xxx.GLPMTRN.GLNO07 and xxx.GLPMSTR.GLNO27 = xxx.GLPMTRN.GLNO27 and
xxx.GLPMSTR.GLNO08 = xxx.GLPMTRN.GLNO08
since some of the columns only exist in one table, so the view show
null value if the value not exist in the other table, how to get rid
of the null value but should be a blank? thanks
4  3895 
COALESCE(<nullable column>, ' ')
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Cannot say i like your naming convention.
You must have an Assembler lnguage background :)
xixi wrote: i have created a view like this
create view xxx.GLJMSTR1 (GLAM01, GLAM02, GLBL01, GLBL02, GLCC02,
GLCC04, GLCD14, GLCD15, GLDN03, GLDY02, GLDY04, GLFL03, GLMO02,
--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands
this is how i create the view now
create view NJIPD.GLJMSTR1 (GLAM01, GLAM02, GLBL01, GLBL02, GLCC02,
GLCC04, GLCD14, GLCD15, GLDN03, GLDY02, GLDY04, GLFL03, GLMO02,
GLMO04, GLNM02, GLNO01, GLNO02, GLNO03, GLNO04, GLNO06, GLNO07,
GLNO08, GLNO27, GLYR02, GLYR04, GLAM03, GLCC03, GLCD13, GLDN04,
GLDY03, GLMO03, GLNO05, GLNO10, GLYR03, systimestamp, loginname, id )
as select NJIPD.GLPMSTR.GLAM01, NJIPD.GLPMSTR.GLAM02,
NJIPD.GLPMSTR.GLBL01, NJIPD.GLPMSTR.GLBL02, NJIPD.GLPMSTR.GLCC02,
NJIPD.GLPMSTR.GLCC04, NJIPD.GLPMSTR.GLCD14, NJIPD.GLPMSTR.GLCD15,
NJIPD.GLPMSTR.GLDN03, NJIPD.GLPMSTR.GLDY02, NJIPD.GLPMSTR.GLDY04,
NJIPD.GLPMSTR.GLFL03, NJIPD.GLPMSTR.GLMO02, NJIPD.GLPMSTR.GLMO04,
NJIPD.GLPMSTR.GLNM02, NJIPD.GLPMSTR.GLNO01, NJIPD.GLPMSTR.GLNO02,
NJIPD.GLPMSTR.GLNO03, NJIPD.GLPMSTR.GLNO04, NJIPD.GLPMSTR.GLNO06,
NJIPD.GLPMSTR.GLNO07, NJIPD.GLPMSTR.GLNO08, NJIPD.GLPMSTR.GLNO27,
NJIPD.GLPMSTR.GLYR02, NJIPD.GLPMSTR.GLYR04,
COALESCE(NJIPD.GLPMTRN.GLAM03,' '), COALESCE(NJIPD.GLPMTRN.GLCC03,'
'), COALESCE(NJIPD.GLPMTRN.GLCD13,' '),
COALESCE(NJIPD.GLPMTRN.GLDN04,' '), COALESCE(NJIPD.GLPMTRN.GLDY03,'
'), COALESCE(NJIPD.GLPMTRN.GLMO03,' '),
COALESCE(NJIPD.GLPMTRN.GLNO05,' '), COALESCE(NJIPD.GLPMTRN.GLNO10,'
'), COALESCE(NJIPD.GLPMTRN.GLYR03,' '), NJIPD.GLPMTRN.systimestamp,
NJIPD.GLPMTRN.loginname, COALESCE(NJIPD.GLPMSTR.id, NJIPD.GLPMTRN.id )
from NJIPD.GLPMSTR left join NJIPD.GLPMTRN on NJIPD.GLPMSTR.GLNO01 =
NJIPD.GLPMTRN.GLNO01 and NJIPD.GLPMSTR.GLNO03 = NJIPD.GLPMTRN.GLNO03
and NJIPD.GLPMSTR.GLNO04 = NJIPD.GLPMTRN.GLNO04 and
NJIPD.GLPMSTR.GLNO06 = NJIPD.GLPMTRN.GLNO06 and NJIPD.GLPMSTR.GLNO02 =
NJIPD.GLPMTRN.GLNO02 and NJIPD.GLPMSTR.GLNO07 = NJIPD.GLPMTRN.GLNO07
and NJIPD.GLPMSTR.GLNO27 = NJIPD.GLPMTRN.GLNO27 and
NJIPD.GLPMSTR.GLNO08 = NJIPD.GLPMTRN.GLNO08
but it gives me error SQL0171N The data type, length or value of
argument "2" of routine "SYSIBM.COALESCE" is incorrect.
SQLSTATE=42815
Xixi,
Some of your columns are not strings. You will need to use e.g. 0 in
thsoe cases instead of space.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: robert |
last post by:
running 8.1.7 server, 8.1.6 client.
i *thought* inner join should not return nulls, but not only that,
but i get way more rows than i'm expecting.
assume:
order table:
order_number
|
by: Bodza Bodza |
last post by:
I'm having an argument with an incumbent self-taught programmer that
it is OK to use null foreign keys in database design.
My take is the whole point of a foreign key is that it's not supposed...
|
by: Geremy |
last post by:
Hi
Consider two tables
id1 code1
----------- -----
1 a
2 b
3 c
id2 code2 value
|
by: Larry Peeters |
last post by:
Hi,
I have created a very simple query to link two tables on 4 fields.
However, in certain cases, one of the fields used to link the table
may contain nulls (in both tables, so this should still...
|
by: Douglas Buchanan |
last post by:
I cannot access certain column values of a list box using code.
I have a list box 'lstPrv' populated by the query below.
SELECT tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,...
|
by: Ian |
last post by:
Hi everyone,
I have been working on a database to overhaul the maintenance job
system here, and Ihave hit a problem when developing a query. I have a
table listing the details of each job and a...
|
by: ADezii |
last post by:
Null as it relates to database development is one of life's little mysteries and a topic of total confusion for novices who venture out into the database world. A Null Value is not zero (0), a zero...
|
by: prabirchoudhury |
last post by:
hey all i am having problem with NULL values to replace with, i have been using all the way to replace NULL value but non of those working for me. NOT NULL field is replacing with abut not the NULL...
|
by: prabirchoudhury |
last post by:
CRITERIA;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
