|
View previous topic :: View next topic
|
| Author |
Message |
Muthukumar.PLindia
New User
Joined: 03 Jul 2006
Posts: 51
Location: Chennai
|
|
|
|
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 |
|
 |
MFRASHEED
Active User
Joined: 14 Jun 2005
Posts: 186
Location: USA
|
|
|
|
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 |
|
|
Muthukumar.PLindia
New User
Joined: 03 Jul 2006
Posts: 51
Location: Chennai
|
|
|
|
| thanks rasheed |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
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 |
|
|
Muthukumar.PLindia
New User
Joined: 03 Jul 2006
Posts: 51
Location: Chennai
|
|
|
|
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 |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
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 |
|
|
Muthukumar.PLindia
New User
Joined: 03 Jul 2006
Posts: 51
Location: Chennai
|
|
|
|
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 |
|
|
|
|
