retrieve data but separate columns

[rozzay]

Hi I AM TRYING TO RETRIEVE INFORMATION FOR THE COLUMN NAME HOWEVER WOULD LIKE TO SEE THE 1ST QUERY IN 1 COLUMN AND THE 2ND QUERY IN ANOTHER COLUMN(NAME2) HOWEVER THE BELOW QUERY COMBINES THE SEARCH INTO 1 COLUMN. THANKS IN ADVANCE.

SELECT NAME as "NAME1" FROM GIS40.ENVELOPE WHERE ENVELOPE_ID IN (SELECT VALUE FROM GIS40.ENVELOPE_PARMS WHERE NAME='NextEnvelope')
UNION
SELECT NAME as "NAME2" FROM GIS40.ENVELOPE WHERE ENVELOPE_ID IN (SELECT ENVELOPE_ID FROM GIS40.ENVELOPE_PARMS)

 

[SantaMufasa]

Rozzay,

Try this:

SELECT NAME as "NAME1"[B][I], null name2[/I][/B] FROM GIS40.ENVELOPE WHERE ENVELOPE_ID IN (SELECT VALUE FROM GIS40.ENVELOPE_PARMS WHERE NAME='NextEnvelope')
UNION
SELECT [B][I]null name1, [/I][/B]NAME as "NAME2" FROM GIS40.ENVELOPE WHERE ENVELOPE_ID IN (SELECT ENVELOPE_ID FROM GIS40.ENVELOPE_PARMS);

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[rozzay]

I tried this and the 2nd column Name2 was generated however NAME2 column has values of 'null'. I would like to retrieve the info from the query below into the 2nd column Name2.

SELECT NAME as "NAME2" FROM GIS40.ENVELOPE WHERE ENVELOPE_ID IN (SELECT ENVELOPE_ID FROM GIS40.ENVELOPE_PARMS)

 

[SantaMufasa]

So, what I hear you saying is that you simply want all of the values that result from the First SELECT to appear under "NAME1" and all of the values that result from the Second SELECT to appear under "NAME2", correct?

If that is the case, then this code should do what you want:

select name1, name2
  from (SELECT rownum rn, NAME as "NAME1"
          FROM GIS40.ENVELOPE
         WHERE ENVELOPE_ID IN (SELECT VALUE
                                 FROM GIS40.ENVELOPE_PARMS
                                WHERE NAME='NextEnvelope')) a,
       (SELECT rownum rn, NAME as "NAME2"
          FROM GIS40.ENVELOPE
         WHERE ENVELOPE_ID IN (SELECT ENVELOPE_ID
                                 FROM GIS40.ENVELOPE_PARMS)) b
 where a.rn(+) = b.rn;

Note: Leave the "(+)" on the "a.rn" side if "a" has less rows than "b"; if that is not the case, then move the "(+)" to the other side to read:

...where a.rn = b.rn(+);

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]