Home » SQL & PL/SQL » SQL & PL/SQL » query to get nulll rows in between existing rows [merged]
icon9.gif  query to get nulll rows in between existing rows [merged] [message #343465] Wed, 27 August 2008 06:36 Go to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi all,

i have to get a output in this form

empno empname job mgrname hiredate
null null null null null
empno empname job mgrname hiredate
null null null null null
empno empname job mgrname hiredate
null null null null null
empno empname job mgrname hiredate
null null null null null

how do i write a query for this ??
Re: query to get nulll rows in between existing rows [message #343468 is a reply to message #343465] Wed, 27 August 2008 06:44 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Why??


Regards,
Oli
Re: query to get nulll rows in between existing rows [message #343469 is a reply to message #343468] Wed, 27 August 2008 06:45 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Olivia wrote on Wed, 27 August 2008 06:44
Why?



I was just about to ask the same question...
Re: query to get nulll rows in between existing rows [message #343471 is a reply to message #343468] Wed, 27 August 2008 06:46 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

was trying to fetch the data in that format, wanted to test something.. do u have ny idea, as to how to write the query ?
Re: query to get nulll rows in between existing rows [message #343472 is a reply to message #343471] Wed, 27 August 2008 06:47 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
SUHAS22845 wrote on Wed, 27 August 2008 06:46
was trying to fetch the data in that format, wanted to test something.. do u have ny idea, as to how to write the query ?


null -null values right?

Create table test as Select * from employees where 1=2

[Updated on: Wed, 27 August 2008 06:48]

Report message to a moderator

Re: query to get nulll rows in between existing rows [message #343475 is a reply to message #343471] Wed, 27 August 2008 06:49 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
That really did not answer the question...but any method of reverse-pivoting/un-pivoting (search here on orafaq and Google will give plenty of results) should be adaptable to meet your needs.
Re: query to get nulll rows in between existing rows [message #343476 is a reply to message #343472] Wed, 27 August 2008 06:51 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Create table test as Select * from employees where 1=2, this query would create a table with all the columns of the emp table without any data.

my requirement is :
I already have some data in emp table, and want to write a query to fetch the data in this manner :

empno empname job mgrname hiredate
null null null null null
empno empname job mgrname hiredate
null null null null null
empno empname job mgrname hiredate
null null null null null
empno empname job mgrname hiredate
null null null null null

i.e, 1st row with data, then a null row and 3rd row with data, then a null value, and so on.. Smile
Re: query to get nulll rows in between existing rows [message #343487 is a reply to message #343476] Wed, 27 August 2008 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set recsep each

Regards
Michel
Re: query to get nulll rows in between existing rows [message #343495 is a reply to message #343476] Wed, 27 August 2008 07:31 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
break on empno skip 1, then do your select. This ridiculous requirement will work in this case
Re: query to get nulll rows in between existing rows [message #343496 is a reply to message #343476] Wed, 27 August 2008 07:33 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Try something like this..


SELECT n ||chr(10)|| CASE 
WHEN 
(ROW_NUMBER() OVER(ORDER BY n) / 3  = 0 AND
ROW_NUMBER() OVER(ORDER BY N) > 1) THEN CHR(13) ELSE 'a' END 
FROM (
SELECT 1 n from dual
UNION ALL
SELECT 2 n from dual
UNION ALL
SELECT 3 n from dual
UNION ALL
SELECT 41 n from dual
UNION ALL
SELECT 51 n from dual
UNION ALL
SELECT 61 n from dual
UNION ALL
SELECT 71 n from dual
UNION ALL
SELECT 72 n from dual
) t
/




Regards,
Oli
Re: query to get nulll rows in between existing rows [message #343498 is a reply to message #343496] Wed, 27 August 2008 07:36 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
This returns:
N||CHR(10)||CASEWHEN(ROW_NUMBER()OVER(ORDE
------------------------------------------
1
2
3
41
51
61
71
72
Re: query to get nulll rows in between existing rows [message #343499 is a reply to message #343496] Wed, 27 August 2008 07:39 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
This will give you the results you desire (just sub in your tables and data) still seems like a rather odd and pointless request.

SELECT   DECODE (ex.num, 1, contact_id),
         DECODE (ex.num, 1, contact_name),
         DECODE (ex.num, 1, contact_phone)
    FROM contact,
         (SELECT 1 num
            FROM DUAL
          UNION
          SELECT 2
            FROM DUAL) ex
ORDER BY contact_id, ex.num
Re: query to get nulll rows in between existing rows [message #343503 is a reply to message #343499] Wed, 27 August 2008 07:44 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I have to admit that is pretty neat Smile, but with such a silly requirement by the original poster, Michel's answer is far easier, as well as my previous answer.
Re: query to get nulll rows in between existing rows [message #343508 is a reply to message #343503] Wed, 27 August 2008 07:55 Go to previous messageGo to next message
arunshrish
Messages: 74
Registered: May 2008
Location: Chennai
Member
Hi,
Might be using some insert trigger would help it out but not sure Confused
Re: query to get nulll rows in between existing rows [message #343512 is a reply to message #343508] Wed, 27 August 2008 07:57 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And how would that possibly help? Is this question a contest of how many responses we can get. Michel's response consisting of 15 characters works. Why do we need any more answers, especially one like that one?
Re: query to get nulll rows in between existing rows [message #343558 is a reply to message #343512] Wed, 27 August 2008 09:16 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi friends,

I thank all of u for your great help,
I am sort of confused with this query. lemme give u a clear picture.

SELECT empno,
ename,
job,
mgr,
hiredate,
level
FROM SG_emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr

This is my query which is helping me in fetchin the records in hirarchial manner, now wat i want is a null row inserted between all of these rows,
Re: query to get nulll rows in between existing rows [message #343561 is a reply to message #343512] Wed, 27 August 2008 09:20 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
This gives the desired output Cool
select
 'empno empname job mgrname hiredate'||chr(10)||
 'null null null null null'          ||chr(10)||
 'empno empname job mgrname hiredate'||chr(10)||
 'null null null null null'          ||chr(10)||
 'empno empname job mgrname hiredate'||chr(10)||
 'null null null null null'          ||chr(10)||
 'empno empname job mgrname hiredate'||chr(10)||
 'null null null null null' 
as labskaus from dual;
Re: query to get nulll rows in between existing rows [message #343582 is a reply to message #343558] Wed, 27 August 2008 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
now wat i want is a null row inserted between all of these rows,

This does change the answers.

Regards
Michel

Re: query to get nulll rows in between existing rows [message #343594 is a reply to message #343582] Wed, 27 August 2008 10:51 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Its not working, totally confused ! ! !
Re: query to get nulll rows in between existing rows [message #343598 is a reply to message #343561] Wed, 27 August 2008 11:03 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

When i try combing these two query's

i.e
SELECT DECODE (ex.num, 1, empno),
DECODE (ex.num, 1, ename),
DECODE (ex.num, 1, JOB),
DECODE (ex.num, 1, MGR),
DECODE (ex.num, 1, hiredate)
FROM sg_emp,
(SELECT 1 num
FROM DUAL
UNION
SELECT 1
FROM DUAL
UNION
SELECT 2 FROM
DUAL) ex
ORDER BY EMPNO,ENAME
(Query which actually inserts rows)

and

START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
(Hirarchial Concept)

Am not able to get the desired o/p..
Friends kindly please help me out...
Re: query to get nulll rows in between existing rows [message #343601 is a reply to message #343465] Wed, 27 August 2008 11:07 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: query to get nulll rows in between existing rows [message #343607 is a reply to message #343598] Wed, 27 August 2008 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: query to get nulll rows in between existing rows [message #343612 is a reply to message #343598] Wed, 27 August 2008 11:31 Go to previous messageGo to next message
tyler_durden
Messages: 14
Registered: August 2008
Location: http://tinyurl.com/63fmwx
Junior Member
Quote:

When i try combing these two query's
i.e
...
Am not able to get the desired o/p..
Friends kindly please help me out...



With all credit to annagel for the original idea -

test@ora>
test@ora>
test@ora> SELECT CASE y.num WHEN 2 THEN NULL ELSE x.empno END AS empno,
  2         CASE y.num WHEN 2 THEN NULL ELSE x.ename END AS ename,
  3         CASE y.num WHEN 2 THEN NULL ELSE x.job END AS job,
  4         CASE y.num WHEN 2 THEN NULL ELSE x.mgr END AS mgr,
  5         CASE y.num WHEN 2 THEN NULL ELSE x.hiredate END AS hiredate,
  6         CASE y.num WHEN 2 THEN NULL ELSE x.lvl END AS lvl
  7    FROM (SELECT empno, ename, job, mgr, hiredate, LEVEL AS lvl,
  8                 ROWNUM AS rn
  9            FROM emp
 10           START WITH mgr IS NULL
 11         CONNECT BY PRIOR empno = mgr) x,
 12         (SELECT 1 AS num FROM DUAL UNION ALL
 13          SELECT 2 FROM DUAL) y
 14  ORDER BY x.rn, y.num;

     EMPNO ENAME      JOB              MGR HIREDATE         LVL
---------- ---------- --------- ---------- --------- ----------
      7839 KING       PRESIDENT            17-NOV-81          1

      7566 JONES      MANAGER         7839 02-APR-81          2

      7788 SCOTT      ANALYST         7566 09-DEC-82          3

      7876 ADAMS      CLERK           7788 12-JAN-83          4

      7902 FORD       ANALYST         7566 03-DEC-81          3

      7369 SMITH      CLERK           7902 17-DEC-80          4

      7698 BLAKE      MANAGER         7839 01-MAY-81          2

      7499 ALLEN      SALESMAN        7698 20-FEB-81          3

      7521 WARD       SALESMAN        7698 22-FEB-81          3

      7654 MARTIN     SALESMAN        7698 28-SEP-81          3

      7844 TURNER     SALESMAN        7698 08-SEP-81          3

      7900 JAMES      CLERK           7698 03-DEC-81          3

      7782 CLARK      MANAGER         7839 09-JUN-81          2

      7934 MILLER     CLERK           7782 23-JAN-82          3


28 rows selected.

test@ora>
test@ora>


tyler_durden
Re: query to get nulll rows in between existing rows [message #343623 is a reply to message #343612] Wed, 27 August 2008 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@tyler_durden

Let OP find and post by himself.

Regards
Michel
Re: query to get nulll rows in between existing rows [message #343637 is a reply to message #343465] Wed, 27 August 2008 12:55 Go to previous messageGo to next message
rs_siva109@yahoo.co.in
Messages: 1
Registered: August 2008
Location: Chennai
Junior Member
Please try with in-line views
For example
select decode(mod(rownum,2),0,'XXX',d.ename) from
(select c.ename from
(select a.ename from emp a
union all
select b.ename from emp b) c
order by 1) d


Re: query to get nulll rows in between existing rows [message #343638 is a reply to message #343465] Wed, 27 August 2008 13:04 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
rs_siva109@yahoo.co.in,

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: query to get nulll rows in between existing rows [message #343647 is a reply to message #343612] Wed, 27 August 2008 13:34 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi friends,

I really thank you all for the great help that i have got from you, i did really learn a lot while attemping to write a query for my requirement. Thanks a lot again.

cheers
suhas
Doubt in Syntax [message #343845 is a reply to message #343465] Thu, 28 August 2008 06:03 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

I have a query like this ::

SELECT CASE y.num WHEN 2 THEN NULL ELSE x.empno END AS empno,
CASE y.num WHEN 2 THEN NULL ELSE x.ename END AS ename,
CASE y.num WHEN 2 THEN NULL ELSE x.job END AS job,
CASE y.num WHEN 2 THEN NULL ELSE x.mgr END AS mgr,
CASE y.num WHEN 2 THEN NULL ELSE x.hiredate END AS hiredate,
CASE y.num WHEN 2 THEN NULL ELSE x.lvl END AS lvl
FROM (SELECT empno, ename, job, mgr, hiredate, LEVEL AS lvl,
ROWNUM AS rn
FROM sg_emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr) x,
(SELECT 1 AS num FROM DUAL UNION ALL
SELECT 2 FROM DUAL) y
ORDER BY x.rn,y.num;
which is fetching me the required output.

i want to modify the output, so that create 2 extra null rows

so i tried writing a query like this :
SELECT CASE y.num WHEN 2 THEN NULL ELSIF
CASE y.num WHEN 3 THEN NULL ELSIF
CASE y.num WHEN 4 THEN NULL ELSE x.empno END AS empno, . . . . . .


it says missing keyword, can anyone please let me know wat is wrong with the syntax ? ?
Re: Doubt in Syntax [message #343854 is a reply to message #343845] Thu, 28 August 2008 06:20 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There is some keyword missing.

Oracle should have told you in the error message at what line and what position.

If you want additional help post the FORMATTED entire session INCLUDING the exact error message.

Re: Doubt in Syntax [message #343856 is a reply to message #343845] Thu, 28 August 2008 06:22 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
The documentation describes correct syntax. It is placed e.g. online on http://tahiti.oracle.com/. Notice that each major release has its own documentation set; also the "Search" button on the top could interest you.
Re: Doubt in Syntax [message #343865 is a reply to message #343854] Thu, 28 August 2008 06:38 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi,

SELECT CASE y.num WHEN 2 THEN NULL ELSE x.empno END AS empno,
CASE y.num WHEN 2 THEN NULL ELSE x.ename END AS ename,
CASE y.num WHEN 2 THEN NULL ELSE x.job END AS job,
CASE y.num WHEN 2 THEN NULL ELSE x.mgr END AS mgr,
CASE y.num WHEN 2 THEN NULL ELSE x.hiredate END AS hiredate,
CASE y.num WHEN 2 THEN NULL ELSE x.lvl END AS lvl
FROM (SELECT empno, ename, job, mgr, hiredate, LEVEL AS lvl,
ROWNUM AS rn
FROM sg_emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr) x,
(SELECT 1 AS num FROM DUAL UNION ALL
SELECT 2 FROM DUAL) y
ORDER BY x.rn,y.num;

This is my code, which is working fine and giving me the output, which actally displays the output in this way.

1st row is getting displayed
2nd row is null
3row is getting displayed
4th row is null
and so on

now i am actually trying to insert two more rows in the 3rd and 4th position,and then the data in 5th row, so tried writing this query

SELECT CASE y.num WHEN 2 THEN NULL ELSIF
CASE y.num WHEN 3 THEN NULL ELSIF
CASE y.num WHEN 4 THEN NULL ELSE x.empno END AS empno,
CASE y.num WHEN 2 THEN NULL ELSE x.ename END AS ename,
CASE y.num WHEN 2 THEN NULL ELSE x.job END AS job,
CASE y.num WHEN 2 THEN NULL ELSE x.mgr END AS mgr,
CASE y.num WHEN 2 THEN NULL ELSE x.hiredate END AS hiredate,
CASE y.num WHEN 2 THEN NULL ELSE x.lvl END AS lvl
FROM (SELECT empno, ename,job, mgr, hiredate, LEVEL AS lvl,
ROWNUM AS rn
FROM sg_emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr) x,
(SELECT 1 AS num FROM DUAL UNION ALL
SELECT 2 FROM DUAL ) y
ORDER BY x.rn,y.num;

Am getting a error message that is being displayed as ORA-00905, "MISSING KEYWORD", please help me out in correcting this syntax.
Re: Doubt in Syntax [message #343866 is a reply to message #343845] Thu, 28 August 2008 06:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is no ELSIF clause in the CASE statement.
Re: Doubt in Syntax [message #343867 is a reply to message #343866] Thu, 28 August 2008 06:42 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Then how do i write it ? ? do i have to change the entire logic ? please help me.
Re: Doubt in Syntax [message #343869 is a reply to message #343867] Thu, 28 August 2008 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can write it formated: please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Doubt in Syntax [message #343879 is a reply to message #343867] Thu, 28 August 2008 06:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You just need to read up on the correct syntax for the CASE statement.
Re: Doubt in Syntax [message #343881 is a reply to message #343869] Thu, 28 August 2008 07:00 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Hi all, Am sorry for not posting the formatted text.
Here is the formatted code

SELECT CASE y.num
WHEN 2 THEN NULL
ELSE x.empNo
END AS empNo,
CASE y.num
WHEN 2 THEN NULL
ELSE x.eName
END AS eName,
CASE y.num
WHEN 2 THEN NULL
ELSE x.Job
END AS Job,
CASE y.num
WHEN 2 THEN NULL
ELSE x.mgr
END AS mgr,
CASE y.num
WHEN 2 THEN NULL
ELSE x.HireDate
END AS HireDate,
CASE y.num
WHEN 2 THEN NULL
ELSE x.lvl
END AS lvl
FROM (SELECT empNo,
eName,
Job,
mgr,
HireDate,
LEVEL AS lvl,
ROWNUM AS rn
FROM sg_emp
START WITH mgr IS NULL
CONNECT BY PRIOR empNo = mgr) x,
(SELECT 1 AS num
FROM Dual
UNION ALL
SELECT 2
FROM Dual) y
ORDER BY x.rn,
y.num;
This is my code, which is working fine and giving me the output, which actally displays the output in this way.

1st row is getting displayed
2nd row is null
3row is getting displayed
4th row is null
and so on

now i am actually trying to insert two more rows in the 3rd and 4th position,and then the data in 5th row, so tried writing this query

SELECT CASE y.num WHEN 2 THEN NULL ELSIF
CASE y.num WHEN 3 THEN NULL ELSIF
CASE y.num WHEN 4 THEN NULL ELSE x.empno END AS empno,
CASE y.num WHEN 2 THEN NULL ELSE x.ename END AS ename,
CASE y.num WHEN 2 THEN NULL ELSE x.job END AS job,
CASE y.num WHEN 2 THEN NULL ELSE x.mgr END AS mgr,
CASE y.num WHEN 2 THEN NULL ELSE x.hiredate END AS hiredate,
CASE y.num WHEN 2 THEN NULL ELSE x.lvl END AS lvl
FROM (SELECT empno, ename,job, mgr, hiredate, LEVEL AS lvl,
ROWNUM AS rn
FROM sg_emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr) x,
(SELECT 1 AS num FROM DUAL UNION ALL
SELECT 2 FROM DUAL ) y
ORDER BY x.rn,y.num;

(Am not able to format this code due to errors, kindly adjust)

Am getting a error message that is being displayed as ORA-00905, "MISSING KEYWORD", please help me out in correcting this syntax.








Re: Doubt in Syntax [message #343889 is a reply to message #343881] Thu, 28 August 2008 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not formated, read the link I posted.

Regards
Michel
Re: Doubt in Syntax [message #343908 is a reply to message #343881] Thu, 28 August 2008 07:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Which part of this example on the linked page I sent you didn't you understand?
Re: Doubt in Syntax [message #343915 is a reply to message #343908] Thu, 28 August 2008 07:55 Go to previous messageGo to next message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

I did understand a few things Mr JROWBOTTOM,

but am not getting a clear idea as to how to write the query,

tried a few things such as

1) CASE y.num WHEN 2,3,4 THEN NULL ELSE x.empno END AS empno,
(trying to get the 2nd, 3rd and 4th row null)

2) CASE y.num WHEN 4 THEN NULL ELSE
CASE y.num WHEN 4 THEN NULL ELSE
CASE y.num WHEN 4 THEN NULL ELSE x.empno END AS empno,

3) CASE y.num WHEN (2,3,4) THEN NULL ELSE x.empno END AS empno,

4) CASE y.num WHEN 4 THEN NULL AND
CASE y.num WHEN 4 THEN NULL AND
CASE y.num WHEN 4 THEN NULL ELSE x.empno END AS empno,

but i am not able to get the desired output, can u please help me out in resolving this.
Re: Doubt in Syntax [message #343919 is a reply to message #343908] Thu, 28 August 2008 08:01 Go to previous messageGo to previous message
SUHAS22845
Messages: 51
Registered: August 2008
Location: BANGALORE
Member

Am sorry Mr JROWBOTTOM,

My previous post had some mistakes,

here is the correct one.

tried a few things such as

1) CASE y.num WHEN 2,3,4 THEN NULL ELSE x.empno END AS empno,
(trying to get the 2nd, 3rd and 4th row null)

2) CASE y.num WHEN 2 THEN NULL ELSE
CASE y.num WHEN 3 THEN NULL ELSE
CASE y.num WHEN 4 THEN NULL ELSE x.empno END AS empno,

3) CASE y.num WHEN (2,3,4) THEN NULL ELSE x.empno END AS empno,

4) CASE y.num WHEN 2 THEN NULL AND
CASE y.num WHEN 3 THEN NULL AND
CASE y.num WHEN 4 THEN NULL ELSE x.empno END AS empno,

but i am not able to get the desired output, can u please help me out in resolving this.
Previous Topic: update query
Next Topic: Trick question
Goto Forum:
  


Current Time: Sun Dec 04 04:34:11 CST 2016

Total time taken to generate the page: 0.08651 seconds