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
 

 

NULL fields should be displayed as "not yet allocated&q

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Muthukumar.PLindia

New User


Joined: 03 Jul 2006
Posts: 51
Location: Chennai

PostPosted: Wed Nov 01, 2006 8:25 pm    Post subject: NULL fields should be displayed as "not yet allocated&q
Reply with quote

hi..
If i want to dispaly all the rows in the table.. in which null fields should be displayed as "not yet allocated"
HOw this canbe done using a query
Back to top
View user's profile Send private message

MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Wed Nov 01, 2006 8:42 pm    Post subject: Re: Null value display
Reply with quote

You can use NULLIF function to do that

Code:


SELECT NULLIF('NOT YET ALLOCATED',Column_name) FROM Table   
WHERE Column_name IS NULL                                           

                                                                     


Where Column_name is column which has Null values.
Back to top
View user's profile Send private message
Muthukumar.PLindia

New User


Joined: 03 Jul 2006
Posts: 51
Location: Chennai

PostPosted: Wed Nov 01, 2006 10:29 pm    Post subject:
Reply with quote

thanks rasheed
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed Nov 01, 2006 10:54 pm    Post subject: Re: NULL fields should be displayed as "not yet allocat
Reply with quote

Muthukumar.PLindia,

Here is an example of what you need to do. There is no short cut that I know of.
You are going to have to name each column you want displayed, and for every column that can be null you need to code a ?COELESCE?. The coalesce returns the first non-null value in the argument list, so if the column is null, coalesce returns the value in the second argument, or ?NOT YET ALLOCATED?. One more item, for any column that is not in ?CHAR? form, you must cast it into ?CHAR?. The DIGITS function will do that for you, or you can use the ?CAST? function.

Code:

DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL                         
  (                                                             
       NAME                    CHAR(30)  NOT NULL,               
       TITLE                   CHAR(10),                         
       SALARY_GRADE             DEC(2)                           
   )                                                             
;                                                               
INSERT INTO SESSION.TEMP_TBL                                     
  (NAME, SALARY_GRADE) VALUES ('DAVIDATK', 1);                   
INSERT INTO SESSION.TEMP_TBL                                     
  (NAME, TITLE) VALUES ('MUTHUKUMAR.PLINDIA','VP');             
INSERT INTO SESSION.TEMP_TBL                                     
  (NAME, TITLE, SALARY_GRADE) VALUES ('GEORGE BUSH','PRES',10); 
SELECT *                                                 
  FROM SESSION.TEMP_TBL                                   
;                                                         
SELECT NAME,                                             
       COALESCE(TITLE,'NOT YET ALLOCATED') AS TITLE,     
       COALESCE(DIGITS(SALARY_GRADE),'NOT YET ALLOCATED')
                AS SALARY_GRADE                           
  FROM SESSION.TEMP_TBL
;                                   


Results:

Code:

SELECT *               
  FROM SESSION.TEMP_TBL
;                       
   +------------------------------------------------------------+
   |              NAME              |   TITLE    | SALARY_GRADE |
   +------------------------------------------------------------+
 1_| DAVIDATK                       | ?          |   1          |
 2_| MUTHUKUMAR.PLINDIA             | VP         | ?            |
 3_| GEORGE BUSH                    | PRES       |  10          |
   +------------------------------------------------------------+

SELECT NAME,                                                 
       COALESCE(TITLE,'NOT YET ALLOCATED') AS TITLE,         
       COALESCE(DIGITS(SALARY_GRADE),'NOT YET ALLOCATED')   
                AS SALARY_GRADE                             
  FROM SESSION.TEMP_TBL                                     
;                                                           

   +------------------------------------------------------------------------+
   |              NAME              |       TITLE       |   SALARY_GRADE    |
   +------------------------------------------------------------------------+
 1_| DAVIDATK                       | NOT YET ALLOCATED | 01                |
 2_| MUTHUKUMAR.PLINDIA             | VP                | NOT YET ALLOCATED |
 3_| GEORGE BUSH                    | PRES              | 10                |
   +------------------------------------------------------------------------+


If you have any questions, please come back,

Dave
Back to top
View user's profile Send private message
Muthukumar.PLindia

New User


Joined: 03 Jul 2006
Posts: 51
Location: Chennai

PostPosted: Thu Nov 02, 2006 11:06 pm    Post subject:
Reply with quote

thanks a lot davidatk.. I need to thank for the efforts that you put to make me understand.

I have one more query table structure is like (studno, mark,subject)
Every student is having thier own marks for thier subject. My requirement is to fetch one record for a student in which they have scored the high marks..
like ( 123 75 english) 75 is the highst mark that 123 have scored compared to all subjects

i can do upto this
select studno,max(marks) from studtable group by studno

but how can i get the subject information.
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Fri Nov 03, 2006 2:15 am    Post subject: Re: NULL fields should be displayed as "not yet allocat
Reply with quote

Here are a couple of ways to accomplish your task, if I understand your requirement correctly. There are several more.

Code:

SELECT *                   
   FROM SESSION.STUDTABLE   
 ;                           

   +----------------------------------------+
   | STUDNO |  MARK  |       SUBJECT        |
   +----------------------------------------+
 1_|  123   |   75   | ENGLISH COMPOSITION  |
 2_|  123   |   70   | EGG COLORING         |
 3_|  123   |   68   | PROGRAMMING          |
 4_|  456   |   85   | ENGLISH COMPOSITION  |
 5_|  456   |   88   | PROGRAMMING          |
   +----------------------------------------+
                                             
                                             
SELECT STUDNO, MARK, SUBJECT         
  FROM SESSION.STUDTABLE   A1         
 WHERE MARK =                         
       (SELECT MAX(MARK)             
          FROM SESSION.STUDTABLE  A2 
         WHERE A2.STUDNO  = A1.STUDNO
        )                             
;                                     
   +----------------------------------------+
   | STUDNO |  MARK  |       SUBJECT        |
   +----------------------------------------+
 1_|  123   |   75   | ENGLISH COMPOSITION  |
 2_|  456   |   88   | PROGRAMMING          |
   +----------------------------------------+
                                             

SELECT STUDNO, MARK, SUBJECT       
  FROM SESSION.STUDTABLE           
 WHERE (STUDNO, MARK) IN           
       (SELECT STUDNO, MAX(MARK)   
          FROM SESSION.STUDTABLE   
         GROUP BY STUDNO           
        )                           
;                                   
  +----------------------------------------+
  | STUDNO |  MARK  |       SUBJECT        |
  +----------------------------------------+
1_|  123   |   75   | ENGLISH COMPOSITION  |
2_|  456   |   88   | PROGRAMMING          |
  +----------------------------------------+
Back to top
View user's profile Send private message
Muthukumar.PLindia

New User


Joined: 03 Jul 2006
Posts: 51
Location: Chennai

PostPosted: Fri Nov 03, 2006 6:42 am    Post subject:
Reply with quote

lovable piece of code.. i can easily undestand the second query...
HOwever i try to learn first type of querying through books.... I know about self join, but this is somehow tricky.Is there any specific name for first type of querying. so that i can search in books rather burdening you....
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 DFSORT MUL FIELDS tspr52 DFSORT/ICETOOL 16 Fri Mar 03, 2017 11:53 pm
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts Sort two file having same field, one ... himanshu malik DFSORT/ICETOOL 7 Thu Feb 02, 2017 10:09 am
No new posts SYNCSORT SEQNUM and FI fields. nartcr SYNCSORT 7 Sat Jan 21, 2017 4:02 am
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am


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