CASE setting column to NOT NULL

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

SQL Server 2005 Forums

Transact-SQL (2005)

CASE setting column to NOT NULL

Author

Topic

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-02-18 : 07:55:41

Hi, i want to set a column to not null if there is a Y in another column. How do i do this.

Basically this is what i want it to say.
If is_parent_product = 'Y' THEN parent_product_id should always have a value in it. If is_parent_product = 'N' then parent_product_id can be null

select CASE WHEN upper(a.IS_PARENT_PRODUCT) = 'Y' THEN a.PARENT_PRODUCT_ID is not null
ELSE a.PARENT_PRODUCT_ID is null END as PARENT_PRODUCT_ID

from dbo.INITIAL_INTERNATIONAL_CATALOG a , dbo.LKP_INTERNATIONAL_CATALOGS b
where a.Country = b.Country
and a.supplier = b.supplier_id

Can someone please help

Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 08:05:49

[code]select
CASE WHEN upper(a.IS_PARENT_PRODUCT) = 'Y' THEN a.PARENT_PRODUCT_ID ELSE null END as PARENT_PRODUCT_ID
from
dbo.INITIAL_INTERNATIONAL_CATALOG a , dbo.LKP_INTERNATIONAL_CATALOGS b
where
a.Country = b.Country
and a.supplier = b.supplier_id[/code]

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-02-18 : 08:10:09

Thanks,

this works, but i need to state that if the is_parent_product = 'Y' Then the parent_product_id column needs to be NOT NULL for that row.

Can i do that?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-18 : 08:13:32

quote:
Originally posted by sgandhi

Thanks,

this works, but i need to state that if the is_parent_product = 'Y' Then the parent_product_id column needs to be NOT NULL for that row.

Can i do that?

What did you get when you execute the above suggesstion?

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-18 : 08:27:48

if IS_PARENT_PRODUCT ='y' ur getting the parent_product_id if it id no then pass the value as 0 then it will not be null
CASE WHEN upper(a.IS_PARENT_PRODUCT) = 'Y' THEN a.PARENT_PRODUCT_ID ELSE 0 END as PARENT_PRODUCT_ID

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-02-18 : 08:27:51

I get a null even if the IS_PARENT_CODE is 'Y' but thats cos there is not value there. I want it to error or soemthing to ensure a value is put in

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-02-18 : 08:31:59

but i want it to be null if the is_parent_product is 'N' not a 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-18 : 08:35:50

quote:
Originally posted by sgandhi

but i want it to be null if the is_parent_product is 'N' not a 0

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 08:41:51

did you mean this?


UPDATE dbo.INITIAL_INTERNATIONAL_CATALOG
SET PARENT_PRODUCT_ID=CASE WHEN upper(IS_PARENT_PRODUCT) = 'Y' THEN PARENT_PRODUCT_ID ELSE NULL END

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-02-18 : 08:45:13

This is what it currently looks like
IS_PARENT_PRODUCT PARENT_PRODUCT_ID
Y NULL
Y NULL
N NULL
N NULL
N NULL

This is what i want, which it will do. if there is a product_id in the table its loading from. But what i want it to check for is, that if the is_parent product is set to Y then i want it to ensure that there is a value in the product_id column, else i want to to be null.
If the parent_product is set to Y but there is no parent_product_id it should error.

Maybe i need to put some other kind of validation. If so, how do i do that

IS_PARENT_PRODUCT PARENT_PRODUCT_ID
Y 123
Y 123
N NULL
N NULL
N NULL

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-02-18 : 08:46:02

Yes but i think ineed some validation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 08:48:16

quote:
Originally posted by sgandhi

Yes but i think ineed some validation.

what validation?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-18 : 08:50:24

quote:
Originally posted by sgandhi

This is what it currently looks like
IS_PARENT_PRODUCT PARENT_PRODUCT_ID
Y NULL
Y NULL
N NULL
N NULL
N NULL

This is what i want, which it will do. if there is a product_id in the table its loading from. But what i want it to check for is, that if the is_parent product is set to Y then i want it to ensure that there is a value in the product_id column, else i want to to be null.
If the parent_product is set to Y but there is no parent_product_id it should error.

Maybe i need to put some other kind of validation. If so, how do i do that

IS_PARENT_PRODUCT PARENT_PRODUCT_ID
Y 123
Y 123
N NULL
N NULL
N NULL

How do you get the value 123 as PARENT_PRODUCT_ID is null?

Madhivanan

Failing to plan is Planning to fail

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-02-18 : 08:51:30

thats just an example of what i want to output to be. Currently it is null

quote:
Originally posted by madhivanan

quote:
Originally posted by sgandhi

This is what it currently looks like
IS_PARENT_PRODUCT PARENT_PRODUCT_ID
Y NULL
Y NULL
N NULL
N NULL
N NULL

This is what i want, which it will do. if there is a product_id in the table its loading from. But what i want it to check for is, that if the is_parent product is set to Y then i want it to ensure that there is a value in the product_id column, else i want to to be null.
If the parent_product is set to Y but there is no parent_product_id it should error.

Maybe i need to put some other kind of validation. If so, how do i do that

IS_PARENT_PRODUCT PARENT_PRODUCT_ID
Y 123
Y 123
N NULL
N NULL
N NULL

How do you get the value 123 as PARENT_PRODUCT_ID is null?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 08:53:03

then specify the rule which makes the value 123.

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-02-18 : 08:56:55

The number will come from the file, and if the number is not there then it should fail.

quote:
Originally posted by visakh16

then specify the rule which makes the value 123.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 09:03:56

which file ? You never mentioned this..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 09:05:06

quote:
Originally posted by sgandhi

The number will come from the file, and if the number is not there then it should fail.

quote:
Originally posted by visakh16

then specify the rule which makes the value 123.

how are transfering data from file? ssis or openrowset or linked server?

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-02-18 : 09:21:31

OK, let me explain the whole scenario.

I have a whole bunch of different file formats (XML, XLS, CSV etc) which i am using SSIS to load into a temporary table.

From this table i am writing a stored proc to insert the data into another table, applying business rules to it and doing validation.
The TMP table data is being loaded to is called dbo.INITIAL_INTERNATIONAL_CATALOG
The Table i am finally loading too is called dbo.INTERNATIONAL_CATALOG

Now the scenario is, that if a rule is not meet (in this case if IS_PARENT_PRODUCT = 'Y' but there is no value in the 'PARENT_PRODUCT_ID' the row should be rejected and row should be sent to a reject table.

Hmm, this makes me think i need to put some kind of validation in there.

Hope this helps

quote:
Originally posted by visakh16

quote:
Originally posted by sgandhi

The number will come from the file, and if the number is not there then it should fail.

quote:
Originally posted by visakh16

then specify the rule which makes the value 123.

how are transfering data from file? ssis or openrowset or linked server?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 09:29:19

It seems like it will be very easy to do this validation in ssis. just use a conditional task in your ssis package which checks for this rule. based on value of IS_PARENT_PRODUCT you will have two outputs. connect first output to step that populates INITIAL_INTERNATIONAL_CATALOG and connect other o/p to reject table population step.

sgandhi
Posting Yak Master

115 Posts

Posted - 2009-02-18 : 09:36:54

ok i'll look into that. thanks

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources