Inserting a text value into Null records in a Grid

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inserting a text value into Null records in a Grid

Author

Topic

mamz001
Starting Member

3 Posts

Posted - 2014-02-11 : 06:01:35

Hi,

I have written up a grid consisting of properties and units.
The way it works is we have properties, and within properties there are units. They are two seperate tables.

Some properties do not have any units so in the unit reference (UN_UREF) column for those records which do not have units and are NULL I would like to insert the text 'NO UNIT'.

Please see below for my SQL for the grid which works fine.

create or replace view VWC_PROPMKUNIT_TEST AS ( SELECT
PR_SNAM, PR_NAME, PR_ADD1, PR_ADD2, PR_ADD3, PR_ADD4, PR_ADD5, PR_ADD6,
PR_POST, PR_OWN, UN_UREF, UN_NAME, UN_GFA
FROM PROP
LEFT JOIN UNIT
ON PR_SNAM=UN_BREF);

All I need to do now is insert 'NO UNIT' within the Unit Reference column where it is NULL.
Hope someone can help.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2014-02-11 : 10:20:50

see below:


create view VWC_PROPMKUNIT_TEST AS 
 SELECT
PR_SNAM, PR_NAME, PR_ADD1, PR_ADD2, PR_ADD3, PR_ADD4, PR_ADD5, PR_ADD6,
PR_POST, PR_OWN, case when UN_UREF is null  then 'NOT UNIT' ELSE UN_UREF end, UN_NAME, UN_GFA
FROM PROP 
LEFT JOIN UNIT
ON PR_SNAM=UN_BREF

Im not sure if that is what are you looking for but that is my understanding.

--------------------------
Joins are what RDBMS's do for a living

mamz001
Starting Member

3 Posts

Posted - 2014-02-12 : 04:33:16

I ran that SQL query but then I got an error message,says ORA-00998:must name this expression with a column alias.

I ran exactly what you wrote

create view VWC_PROPMKUNIT_TEST AS
SELECT
PR_SNAM, PR_NAME, PR_ADD1, PR_ADD2, PR_ADD3, PR_ADD4, PR_ADD5, PR_ADD6,
PR_POST, PR_OWN, case when UN_UREF is null then 'NOT UNIT' ELSE UN_UREF end, UN_NAME, UN_GFA
FROM PROP
LEFT JOIN UNIT
ON PR_SNAM=UN_BREF;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 05:14:20

try


SELECT
PR_SNAM, PR_NAME, PR_ADD1, PR_ADD2, PR_ADD3, PR_ADD4, PR_ADD5, PR_ADD6,
PR_POST, PR_OWN, case when UN_UREF is null then 'NOT UNIT' ELSE UN_UREF end AS UnitRef, UN_NAME, UN_GFA
FROM PROP 
LEFT JOIN UNIT
ON PR_SNAM=UN_BREF;

if it still doesnt work please try your luck at www.dbforums.com
This is a mS SQL Server forum and there're not much experts on Oracle here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mamz001
Starting Member

3 Posts

Posted - 2014-02-12 : 05:52:48

Thanks for your help it has worked

Thanks for your help too xhostx.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 06:12:08

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources