| View previous topic :: View next topic |
| Author |
Message |
timfoster
Beginner
Joined: 08 Jul 2004
Posts: 12
Topics: 5
|
 Posted: Tue Aug 22, 2006 8:34 pm Post subject: trying to output a single quote from db2 |
 |
|
Folks,
I have some db2 unload jobs and I need to actually include a single qoute in the file it creates. Is there a way to do this in db2.
what I am trying to do is something like
| Code: |
select ''', first_name from tablea;
|
and the output would be
now of course the sql doesn't actually work when running it using the db2 unload utility. Is there a way to get a single qoute?
and one other question
I have a select like
| Code: |
select CHAR(SUBSTR(ACCTNO,1,4)) from tablea;
|
and the output is
I expected that using the char function would get rid of the extra byte. Is there something I am missing here.
thanks
Tim |
|
| Back to top |
|
 |
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Wed Aug 23, 2006 2:01 am Post subject: |
|
|
Hello Tim,
to get a single quote as a literal in DB2, you have to double it. Since this doubled quote has to be embraced by quotes, you actually have to code four quotes. To acchieve, that this selected literal is close to another value, you have to concatenate them with
| Code: |
SELECT '''' !! first_name from tablea; or
SELECT '''' CONCAT first_name from tablea;
|
To your other question: Assuming, that ACCTNO is defined as a dec(4,0) value, your query wouldn't work, since SUBSTR is a function on a character value. Try
SELECT SUBSTR(CHAR(ACCTNO),1,4)
and the result should be as desired.
regards
Christian |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Aug 23, 2006 7:31 am Post subject: |
|
|
timfoster,
Use the CHAR function to avoid the extra 2 bytes when unloading.
try this
| Code: |
SELECT CHAR('''')
,first_name
from tablea;
|
| CZerfas wrote: |
To your other question: Assuming, that ACCTNO is defined as a dec(4,0) value, your query wouldn't work, since SUBSTR is a function on a character value. Try
SELECT SUBSTR(CHAR(ACCTNO),1,4)
and the result should be as desired.
|
CHAR function would work but it would put a decimal at the end. You can use DIGITS scalar function which does not put the decimal at the end.
| Code: |
SELECT DIGITS(ACCTNO)
from tablea;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
timfoster
Beginner
Joined: 08 Jul 2004
Posts: 12
Topics: 5
|
| Posted: Wed Aug 23, 2006 7:25 pm Post subject: |
|
|
actually the field acctno is a char(25). Assume that the field contains
1815&&&4070S80086
the following commands when run using the bb2 unload utility give the following output
substr(acctno,13,5)
output - 80086.
where the period is 00 in hex
substr(acctno,1,4)
output - 1815.
where the period is 00 in hex again.
I have tried putting the char() around it but it hasn't prevented the blank character. Now I do have a number of tables in the query and the one I am getting the acctno from is joined using a left outer join but I can't imagine that makes any difference except that I have noticed when the acctno is null because of the outer join that the last character is actually a ? mark instead of a period.
again thanks for all the help |
|
| Back to top |
|
|
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Thu Aug 24, 2006 3:42 am Post subject: |
|
|
As you mentioned, the '.' stands for the NULL indicator DB2 unloads. You can change this by coding
select VALUE(SUBSTR(ACCTNO,1,4), ' ')
Then DB2 knows that the output field cannot become nullable and omits the x'00'.
regards
Christian |
|
| Back to top |
|
|
shekar123
Advanced
Joined: 22 Jul 2005
Posts: 528
Topics: 90
Location: Bangalore India
|
| Posted: Thu Aug 24, 2006 4:37 am Post subject: |
|
|
CZerfas,
If you code ,then you will get 2 extra bytes at the begining:
| Code: |
SELECT VALUE(SUBSTR(ACCTNO,13,5),' ') FROM TABLE;
|
OUTPUT
So make use of the CHAR Function to remove the 2 extra bytes:
| Code: |
SELECT CHAR(VALUE(SUBSTR(ACCTNO,13,5),' ')) FROM TABLE;
|
OUTPUT
timfoster,
Check this info for VALUE Funtion:
| Code: |
----------
| |
VALUE ( expression ,expression )
The schema is SYSIBM.
The VALUE function returns the first argument that is not null.
VALUE is a synonym for COALESCE.
|
Check this links for COALESCE Funtion:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/3.2.13?SHELF=&DT=20010718164132&CASE=
_________________
Shekar
Grow Technically |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Aug 24, 2006 4:52 am Post subject: |
|
|
| Quote: |
actually the field acctno is a char(25). Assume that the field contains
|
If the column is defined as Character , and you are picking only 5 bytes I don't see as to how you got the additional byte at the end. May be it is a part of the next column you are selecting?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
shekar123
Advanced
Joined: 22 Jul 2005
Posts: 528
Topics: 90
Location: Bangalore India
|
| Posted: Thu Aug 24, 2006 8:22 am Post subject: |
|
|
Kolusu,
If i run the Query in SPUFI i can proper output but if i run the same query for unloading data i get a Period (.) at the end .Why is the difference coming up , can u please help me ?
SPUFI
| Code: |
SELECT SUBSTR(ACCTNO,13,5) FROM TABLE;
---------+---------+---------+---------+---------+---------+---------+---------+
---------+---------+---------+---------+---------+---------+---------+---------+
80086
|
UNLOADED DATASET
| Code: |
//SYSIN DD *
SELECT SUBSTR(ACCTNO,13,5) FROM TABLE;
/*
80086. |
_________________
Shekar
Grow Technically |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Aug 24, 2006 8:33 am Post subject: |
|
|
| Quote: |
If i run the Query in SPUFI i can proper output but if i run the same query for unloading data i get a Period (.) at the end .Why is the difference coming up , can u please help me ?
|
shekar123,
If you looked at the SYSPUNCH dataset you would have found the reason as to why you got the extra byte. The extra byte is because you have defined your column to have nulls. Define your table column as
| Code: |
(ACCT_NO CHAR(25) NOT NULL WITH DEFAULT)
|
and now unload the table and see if you get the extra byte.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
shekar123
Advanced
Joined: 22 Jul 2005
Posts: 528
Topics: 90
Location: Bangalore India
|
| Posted: Thu Aug 24, 2006 9:59 am Post subject: |
|
|
Kolusu,
Great answer and a lot of my basics have been cleared by this post.Thanks.
_________________
Shekar
Grow Technically |
|
| Back to top |
|
|
timfoster
Beginner
Joined: 08 Jul 2004
Posts: 12
Topics: 5
|
| Posted: Thu Aug 24, 2006 12:16 pm Post subject: |
|
|
Folks, thanks for the replies. The column in question was defined as null and I am not able to change this. The value() function worked to convert it so I have it now without the . at the end.
thanks again for the help |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
