Selecting null columns for script output in SQL Developer

Question

I need to output 3 null columns in my SQL statement, i'm using SQL developer Oracle 11g For example:

SELECT
  c.CID, 
  c.CName,
  null, 
  null, 
  null, 
  r.RegionID, 
  r.RegionName
FROM
  Regions r 
  INNER JOIN Branch b ON b.RegionID = r.RegionID
  INNER JOIN Countries c ON c.CID = b.CID;

So in the script output/query result window on SQL Developer shows

  CID| CName | Null |   Null  | Null | RegionID  | RegionName
  ------------------------------
  C1 | ENG   | Null  | Null   | Null | R1        | UK   
  C2 | ...   | Null  | Null   | Null | R2        | ...
  C3 | ...   | Null  | Null   | Null | R3        | ...

What would be ideal is if i could do something like:

Select
Country,
Region, 
Null as Continent,
Null as hemisphere

etc

I know this may sound bonkers but it's just for formatting purposes until the database is updated.

EDIT: Thanks for that, didn't realize it was so straight forward, the only issue is my null column is massive on the script output screen, even though it's empty, is there a way make it a more reasonable size?

Check my answer for an example - you have to CAST the NULL as, for example, VARCHAR2(1). Then it will take less space. — Przemyslaw Kruglej
– Przemyslaw Kruglej, Commented Nov 7, 2013 at 10:32

Przemyslaw Kruglej · Accepted Answer · 2013-11-07 10:31:58Z

1

Well, you can do it just the way you presented...:

SELECT
  c.CID, 
  c.CName,
  CAST(null AS VARCHAR2(1)) AS Continent, 
  null AS hemisphere, 
  null AS something, 
  r.RegionID, 
  r.RegionName
FROM
  Regions r 
  INNER JOIN Branch b ON b.RegionID = r.RegionID
  INNER JOIN Countries c ON c.CID = b.CID;

edited Nov 7, 2013 at 10:31

answered Nov 7, 2013 at 10:22

Przemyslaw Kruglej

8,1312 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hulaz Over a year ago

Thanks, that works great for the Query Result window, though in the Script Output window the null columns are huge, many times larger than the entire database, despite being empty. Any way to fix that?

Przemyslaw Kruglej Over a year ago

@Hulaz Yes, check my edited answer for an example - you have to CAST the NULL as, for example, VARCHAR2(1). Then it will take less space.

SQB · Accepted Answer · 2013-11-07 10:24:24Z

0

Actually, that should work.

SELECT
  c.CID, 
  c.CName,
  null AS country, 
  null AS Region, 
  null AS hemisphere, 
  r.RegionID, 
  r.RegionName
FROM
  Regions r 
  INNER JOIN Branch b ON b.RegionID = r.RegionID
  INNER JOIN Countries c ON c.CID = b.CID;
-- Or, if you don't have any tables yet:
SELECT
  NULL AS CID, 
  NULL AS CName,
  NULL AS country, 
  NULL AS Region, 
  NULL AS hemisphere, 
  NULL AS RegionID, 
  NULL AS RegionName
FROM dual;

answered Nov 7, 2013 at 10:24

SQB

4,1262 gold badges34 silver badges53 bronze badges

Comments

user2342436 · Accepted Answer · 2013-11-07 10:25:41Z

0

I think by showing null u mean that basically it should be empty and shown as null so u can use the query below:

Select
Country,
Region, 
''  Continent,
''  hemisphere,
from 
regions r;

answered Nov 7, 2013 at 10:25

user2342436

4823 silver badges17 bronze badges

Collectives™ on Stack Overflow

Selecting null columns for script output in SQL Developer

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related