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 Changeman get multiple "Browse C... elixir1986 Compuware & Other Tools 4 Fri Jul 14, 2017 1:48 am
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am
No new posts Include/omit with "ALL" pa... mgl DFSORT/ICETOOL 6 Wed Jun 14, 2017 2:31 pm
No new posts Release quantity that was allocated v... Auryn JCL & VSAM 5 Thu May 18, 2017 6:02 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm


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