Solved

Check Constraint and NULL in list of values

Posted on 2006-11-06
6
1,530 Views
Last Modified: 2008-02-01
Examine the following check constraint...

ORTHDNTC_TRCTN_DVC_IND INDICATOR with default NULL
      constraint C_ORTHDNTC_TRCTN_D check (ORTHDNTC_TRCTN_DVC_IND is
null or (ORTHDNTC_TRCTN_DVC_IND in ('N','Y',NULL) and
ORTHDNTC_TRCTN_DVC_IND = upper(ORTHDNTC_TRCTN_DVC_IND))),

The column is based on domain INDICATOR, has permissable values of "Y", "N" and null, and defaults to null. My DBA is telling me that DB2 does not like NULL in the list of values. My modeling tool, PowerDesigner 12.0, is telling me this is OK.

So, can the list of values contain NULL? Of not, how do I tell DB2 that the permissable values are "Y", "N" and null?
0
Comment
Question by:dlmedici
  • 3
  • 2
6 Comments
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 31 total points
Comment Utility
hi

you don't have to put the NULL inside the in list since you check if the value is null before you check the in list

momi
0
 

Author Comment

by:dlmedici
Comment Utility
Momi,

Yes, that is what I thought, too. But, PowerDesigner is telling me I have an inconsistency in check parameters, stating, "The values entered in the check parameters tab are inconsistent for numeric and string data types: default does not respect minimum and maximum values, or default does not belong to list of values, or values in list are not included in minimum and maximum values, or minimum is greater than maximum value. Check parameters must be defined consistently." So, PowerDesigner is expecting the NULL in the list of values.

What gives?
0
 
LVL 13

Expert Comment

by:ghp7000
Comment Utility
your first check is to make sure it is null, the second check is to make it is YN or null, it is the OR clause that is causing the error to be raised. Re phrase the check condition to eliminate the OR clause
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:dlmedici
Comment Utility
ghp7000,

Can you please elaborate a bit? I cannot see any reason why DB2 should not be able to parse that simple OR. It appears to be quite elemental.
0
 
LVL 13

Expert Comment

by:ghp7000
Comment Utility
0
 
LVL 13

Accepted Solution

by:
ghp7000 earned 94 total points
Comment Utility
simply put if first condition=null             =unknown
second condition=null and third condition=true (unknown and true=unknown)

result=unknown or unknown=unknown
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
db2 z/os V10 access path 7 325
Check file/object size on AS400 3 153
RPG Free Prototypes using  *LDA data 5 149
Getting missing rows 16 31
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now