1) Null Value In Foreign Key 2) Extra Value To Indicate All The Values In PK Table Apply

Feb 27, 2007

I have two tables  1) tblCustomer (ID, Name, City)  2) tblemp (ID, NAME, Dept.ID, tblcustomer.ID)

Both the tables have ID as PK
A emp can be either assigned a) All customers b)single customer c) NO customer

Pls note:- there will never be 2 or 3 customer linked to emp (my actuall requirement tables are different but to explain i am using the above tables)

I know how to assign single customer......but had problem how to link all customers and "no customer"

Please tell me if the following solution is right?
1) I will manually insert a record in tblCustomer with id 0 as " all customers" and will not allow the user to delete it
2) store null in FK if it is "no customer"


Also please tell me is it ok to store one more value in tblCustomer as -1 and take it as "No customer"

I have a DDL in the Employee page which should be displaying the names of allthe customers with 2 extra values "ALL CUSTOMERS" and "SELECT"  Select is the default value in DDL which says "NO CUSTOMER" selected yet

Thank you
Sara

View 10 Replies


ADVERTISEMENT

Transact SQL :: How To Apply Foreign Keys On Top Of A Common Lookup Table

Apr 29, 2015

I have an issue where i am mandated to enforce RI on an applications database (a good thing). but I have several common lookup tables where many of the "codes" reside for many different code types. I also have the mandate that i cannot change the underlying DDL to make composite keys to match the codes table PK. I am currently looking at creating indexed views on top of the Codes table to seperate the logical tables it contains. This is several hundred views.

I do know that I do not want to write several hundred triggers to enforce RI. Table schema below, the CdValue column is the column that is used throughout the hundreds of tables that use this codes table, and their corresponding column is not named the same.

CREATE TABLE dbo.CodesTable (
PartyGrpId INT  NOT NULL
  , CdTyp  VARCHAR ( 8 ) NOT NULL
  , CompId INT  NOT NULL
  , CdValue VARCHAR ( 8 ) NOT NULL
  , CdValueDesc VARCHAR ( 255 ) NULL
  , AltValueDesc VARCHAR ( 100 ) NULL

[Code] ....

I did though run into one forum where a person brought up a great idea. Filtered Foreign Keys, what a novel concept, if it could work it would make so much less code to fix an issue like this.

ALTER TABLE dbo.BusinessStatus WITH NOCHECK
ADD CONSTRAINT FK_dbo_BusinessStatus_CodesTable FOREIGN KEY (LoanStsDtCd) REFERENCES dbo.CodesTable (CdValue) WHERE CdTyp = 'Status'

View 5 Replies View Related

Insert NULL Values To Foreign Key Fields

Jan 15, 2008

Hi all,

I have a projects table with 2 foreign key fields that both link to the same employees table because a project has a Package Engineer (PkgEngineerID) and a Contract Administrator (PkgContrAdmin). When I try to insert a record with null values for either or both of these foreign keys I get an error:

The data in row xxx was not commited. The record can't be added or changed. Referential integrity rules require a related record in table 'tblEmployees'. The transaction ended in the trigger. The batch has been aborted.

An insert statement for the above would look something like the following:

INSERT INTO tblPackages (PkgNo, PkgName, PkgEngineerID, PkgContrAdmin, PkgRemark)VALUES (1234, 'My Package', NULL, NULL, 'My Package remark')

And the create table statements are:

USE [PASSQL]GO/****** Object: Table [dbo].[tblPackages] Script Date: 01/15/2008 23:25:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblPackages]( [ID] [int] IDENTITY(1,1) NOT NULL, [PkgNo] [nvarchar](20) NULL, [PkgName] [nvarchar](255) NULL, [PkgEngineerID] [int] NULL, [PkgContrAdmin] [int] NULL, [PkgRemark] [nvarchar](255) NULL, [upsize_ts] [timestamp] NULL, CONSTRAINT [aaaaatblPackages_PK] PRIMARY KEY NONCLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[tblPackages] WITH NOCHECK ADD CONSTRAINT [FK_tblPackages_tblEmployees] FOREIGN KEY([PkgEngineerID])REFERENCES [dbo].[tblEmployees] ([ID])GOALTER TABLE [dbo].[tblPackages] CHECK CONSTRAINT [FK_tblPackages_tblEmployees]GOALTER TABLE [dbo].[tblPackages] WITH NOCHECK ADD CONSTRAINT [FK_tblPackages_tblEmployees1] FOREIGN KEY([PkgContrAdmin])REFERENCES [dbo].[tblEmployees] ([ID])GOALTER TABLE [dbo].[tblPackages] CHECK CONSTRAINT [FK_tblPackages_tblEmployees1]

And:

USE [PASSQL]GO/****** Object: Table [dbo].[tblEmployees] Script Date: 01/15/2008 23:28:01 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblEmployees]( [ID] [int] IDENTITY(1,1) NOT NULL, [EmpName] [nvarchar](255) NULL, [EmpShort] [nvarchar](255) NULL, [upsize_ts] [timestamp] NULL, CONSTRAINT [aaaaatblEmployees_PK] PRIMARY KEY NONCLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
Any ideas on how to accomplish this would be great!
Thanks in advance!

View 4 Replies View Related

SELECT INSERT INTO Other Table With Extra Values

Mar 23, 2008

Hi,
I've got a table with trialsubscriptions. When someone orders a trialsubscription he has to fill in a form. With the values in the form I like to fill a database with some extra fields from the trialsubscription table. I get those extra fields with a datareader. But when I insert I can't use the same datareader (its an insert), I can't make two datareaders because I have to close the first one etc.
Does someone had the same problem and has some example code (or make some :-)) Some keywords are also possible!
Thanks!
Roel

View 3 Replies View Related

Lookup Table Foreign Key Null

May 1, 2007

I'm using SQL Server 2000. I have an EXTERNAL_FUNDING table and an EXTERNAL_FUNDING_TYPE table. The DDL for the tables is listed below. Each EXTERNAL_FUNDING record has a type field, which is a foreign key to a record in the EXTERNAL_FUNDING_TYPE table. However, I would like for the type to be allowed to be null. When displaying the data, if the type is null, I'd like to show blank text. Here is the query I'm trying to use:
SELECT EXTERNAL_FUNDING.id, date_requested as [Request Date],source as [Source/Sponsor],EXTERNAL_FUNDING_TYPE.type AS [Type],amount as [Amount], granted as [Granted]
FROM EXTERNAL_FUNDING,EXTERNAL_FUNDING_TYPE
WHERE (project_id = @project_id) AND
(EXTERNAL_FUNDING.type = EXTERNAL_FUNDING_TYPE.id)
ORDER BY date_requested ,source,type

But this query does not return the records with NULL for the type field. Can someone explain to me how I can also return the rows with a NULL type? Thanks!

Table DDL:
CREATE TABLE [dbo].[EXTERNAL_FUNDING] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[project_id] [int] NOT NULL ,
[date_requested] [datetime] NULL ,
[source] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [int] NULL ,
[amount] [money] NULL ,
[granted] [bit] NULL ,
[date_granted] [datetime] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[EXTERNAL_FUNDING_TYPE] (
[type] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]

View 2 Replies View Related

Any Improvements To This: Cannot Apply Value Null To Property Login: Value Cannot Be Null.

Mar 26, 2007

Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.

View 1 Replies View Related

UGH! Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.

Jan 9, 2007

I know this is probably a flick of a switch but I cannot figure out which switch.  Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL.  The stored procedure queries from only one table and two columns are ignored because they are being phased out.  I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error.  I checked every column that does not allow nulls and they all have values.  I checked unique columns (ID is the only unique and is Identity=Yes in the table definition).  I checked foreign-key columns for values that are not in the foreign table and there are none.  Any ideas why do I get this? 
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

View 3 Replies View Related

Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.

Jan 17, 2008

Hi,
    I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that  there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.

View 7 Replies View Related

Cascade Delete Can Not Apply To Two Foreign Keys

Jul 31, 2004

I have a table that contains two foreign keys of two different tables. I want to build a relationship so that when either primary keys deleted in the two tables, the record in the table should be deleted. But, SQL Server does not allow me to save the relationship, it complains that the circling delete might exist. I do not know why, how can I solve this?

Table A:
ID
ProductID <foreign key>
CustomerID <foreign key>

Table Product
ProductID <primary key>

Table Customer
CustomerID <primary key>

I want to cascade delete the record in Table A when either the ProductID is deleted from Product table or the CustomerID is deleted from Customer table.

View 1 Replies View Related

Updating Values In A Table With Foreign Key Violations

Aug 25, 2004

Hello!
I have a "current" table which users update on a daily basis, it holds forecast data. This table is designed to only hold 7 days of data and is therfore always rolling over by date.
What I want to do is have a procedure that copies this "Current" table to a "History" table every day so the historical information is stored. The copy is simple, but how do I check for and over ride values that are already in the "history" table with the newest values from the "current" table because the "current" values have the possibility of changing a few times while the forecast is updated. I can define a primary key but am not sure how to check for and update the necessary values?

Any help??

Many thanks!

View 1 Replies View Related

Insert Values In Multiple Table With Foreign Key

Feb 28, 2008

Hi,

I've been working as web dev for quite sometime now but there are still few things that i would like to clarify. Hope you guys can shed lights.

I currently have several tables and all this tables are having some 1 to many relationships. I know that how i insert value into my tables are very inefficient. I do it in this manner..
-> Insert a value in TableA
-> Query the latest ID that i have inserted in Table A then
-> Insert this value in TableB. And so on and so forth.

I believe there is an efficient way to to do this but i'm not sure how? Could anyone shed me a light on this matter?

Your reply would be highly appreciated. TIA.

View 6 Replies View Related

Compressing Multiple Rows With Null Values To One Row With Out Null Values After A Pivot Transform

Jan 25, 2008

I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?

-- Ryan

View 7 Replies View Related

NULL/Varchar And Extra Bits

Jul 20, 2005

How may extra bits does a NULL column contain.And how many extra bits does a varchar column contain.(I 've worked with Ingres and in that environment it both needed 2 extrabits)Bye,Arno de Jong, The Netherlands

View 2 Replies View Related

What's The Accepted Way To Retrieve Records In A SQL Table With Null Values Using A Visual Studio 2005 Table Adapter?

Jan 21, 2008

I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database.
 I have a very simple dilemma.  In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields.
It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table.
In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters.
The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND   (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%')
If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null>
My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>.
For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null>  (accounting for about 95% of the records in the database). Thanks in advance Simon
 

View 9 Replies View Related

Alter Table Datatype With Null Values

May 7, 2015

I am trying to change a column from a decimal(18,2) to a decimal(18,3). What is the SQL command to alter this table?

SQL Command:

alter table TableName
alter column ColumnName decimal(18,3) [null]

the above command is right ?

View 6 Replies View Related

Can't Import An Access Table Due To Null Values.

Jul 20, 2005

Question: Why would I not be able to import an Access 97 table inwhich some records have null values in fields that allow null values?Wouldn't the table's design be imported first, bringing the columns'"allow nulls" attribute with it?I'm dealing with both text and numeric columns. Not all columnscontaining nulls cause an error.Thanks,Bob C.

View 2 Replies View Related

.NET Framework :: CLR Table Function With Null Values

Jun 23, 2015

I'm trying to do a Clr function wiht null values but I have an error. My Clr is like this:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Text;

[code]...

A .NET Framework error occurred during execution of user-defined routine or aggregate "function":

System.InvalidCastException: La conversión especificada no es válida.
System.InvalidCastException:
   en Microsoft.SqlServer.Server.ValueUtilsSmi.GetSqlInt16(SmiEventSink_Default sink, ITypedGettersV3 getters, Int32 ordinal, SmiMetaData metaData)
   en UserDefinedFunctions.function()

View 5 Replies View Related

Displaying NULL Values In The Destination Table

Sep 17, 2007



Dear Members,

I extracted data from a flat file using SSIS package and load them into destination table. One column contains no data. Data type of that column is varchar(9). I want to display "NULL" values in that column.

I used LOOK up transformation and used the following query.


SELECT PassThroughRouting =
CASE PassThroughRouting
WHEN 'NULL' THEN 'NULL'
END
FROM EPICWareTable

But it gives a error message.

Does any body can help me?

Thanks

View 9 Replies View Related

Filtering Out Null Values On A Matrix Table

Jan 29, 2008

Hi,

How do I filter out Null values in a matrix table

one off my columns is picking up the Null values and I would like to filter this out.

Is there a simple way of doing this as filtering it out at the Dataset level is not the ideal solution for me.

thanks

View 5 Replies View Related

Hotfix For Cannot Apply Value Null To Property Login Error Available?

Feb 24, 2006

It appears that Microsoft has fixed the "Cannot apply value null to property Login" bug in SSIS, according to this web page:

Click here for bug details

Question: is there a hotfix available for download?



View 1 Replies View Related

T-SQL (SS2K8) :: Find Null Values Between 3 Datasets In One Table

Mar 21, 2014

I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.

I have aSQL Fiddle that will allow you to visualize the schema.

The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.

select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 1'
) x
pivot
(
max(item)

[Code]...

View 2 Replies View Related

Inserting Few Recordsets Into Temp Table Containing NULL Values

Jul 20, 2005

HiI need some help on achieving the following:I wrote a querie which collects Data out of three Tables, the Resultlooks like this:SET NOCOUNT ONDECLARE @ROWINTDECLARE @CURPTNO CURSORSET @CURPTNO = CURSORFORSELECT * FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGINSELECT ONE.CYNO, ONE.ROLE, ONE.ROL_BEZ, ONE.PTNO, ONE.NOCY, TWO.TEXTAS NOCY_TEXTFROM(SELECT CY.CYNO,RE.CYNO AS RECYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW)AND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW))) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOThe Result looks like this:RS1:6313,1300,Architekt,99737505,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLRS2:2693265,100,Bauherr,99756900,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pläne selberRS3:2691919,100,Bauherr,99755058,NULL,NULL2691962,6000,Kontakt,99755058,NULL,NULLMy Problem is, that I need to have all the Resultsets in one Table atthe end.So my further undertaking was to create a Temp Table with theexpectation to receive all the resultsets in one Step.The TSQL for this looks like that:SET NOCOUNT ONCREATE TABLE #CYRE_TEMP(CYNOINT NULL,ROLEINT NULL,ROL_BEZVARCHAR (60) NULL,PTNOINT NULL ,NOCYINT NULL,TEXTVARCHAR (60) NULL)GODECLARE @CYNOINT,@ROLEINT,@ROL_BEZVARCHAR (60),@PTNOINT,@NOCYINT,@TEXTVARCHAR (60),@ROWINT,@CURPTNOCURSORSET @CURPTNO = CURSOR FORSELECT PTNO FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGININSERT INTO #CYRE_TEMP (CYNO, ROLE, ROL_BEZ, PTNO, NOCY, TEXT)VALUES(@CYNO,@ROLE ,@ROL_BEZ ,@PTNO ,@NOCY ,@TEXT)SELECT @CYNO = ONE.CYNO,@ROLE = ONE.ROLE,@ROL_BEZ = ONE.ROL_BEZ,@PTNO = ONE.PTNO,@NOCY = ONE.NOCY,@TEXT = TWO.TEXTFROM(SELECT CY.CYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROWAND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROW)) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOSELECT * FROM #CYRE_TEMPDROP TABLE #CYRE_TEMPGOAnd the Output looks like this now:Q1:NULL,NULL,NULL,NULL,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pläne selberCan someone help me getting all the 6 Rows into one Table as Output?I appreciate any available Help on this..Ssscha

View 1 Replies View Related

Checking For Null Values In A Table/Matrix Cell

Jun 12, 2007

What would be an equivalent expression for ISNULL(datafield, 0) for a table/matrix cell? I am using iif( Len().. to find out if there is something in the cell, and displaying zero in the cell if the length of cell item is 0, however am wondering if there is any better/elegant way of doing that?

View 3 Replies View Related

Error Executing A Sql Statement Against A Table With Null Date Values

Apr 10, 2008

We are just upgrading a server from 2000 to 2005 and we are getting the message below when we execute a sql statement against a table with a date field with null values:


"Error converting data type DBTYPE_DBTIMESTAMP to datetime."

View 3 Replies View Related

Data Flow Task - Have Null Values Take Table Default

Jun 6, 2006

Hi,


I have a data transform from a flat-file to a SQL server database.
Some of the flat-file fields have NULL values. The SQL table I'm
importing into does not allow NULL values in any field, but each field
has a Default value specified.


I need to have it so that if a null value comes across in a field using
the data transform, it takes the table default on import. I could of
sworn I had this working a few days ago, but I get errors now that
state I'm violating table constraints. Has anyone done this before?


Thanks
Jeff

View 5 Replies View Related

Transact SQL :: Preserve And Return NULL For Non Matching Values From A Table Valued Function

Jun 29, 2015

I have tables and a function as representated by the code below. The names  for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.

When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.

SELECT  A.CID, 
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B

CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)

[Code] ....

View 4 Replies View Related

Null Foreign Key

Jul 20, 2005

I'm having an argument with an incumbent self-taught programmer thatit is OK to use null foreign keys in database design.My take is the whole point of a foreign key is that it's not supposedto be optional, it's very definition is it's a necessary link to theparent table and part of the definition. If it's optional it shouldn'tbe part of the definition of a table and should be in a linking tableinstead.Comments?

View 10 Replies View Related

Using Foreign Key Has Null Value...

Oct 4, 2007

Hi,
I made two tables. The Policy table's rate_limit column references the RateLimit table's id as foreign key.
Here is the table definitions.

CREATE TABLE RateLimit
(
id INT CONSTRAINT PK_RateLimit_id PRIMARY KEY CLUSTERED,
name VARCHAR(20),
min INT,
max INT
);

CREATE TABLE Policy
(
id INT CONSTRAINT PK_Policy_id PRIMARY KEY CLUSTERED,
rate_limit INT CONSTRAINT FK_Policy_rate_limit FOREIGN KEY(rate_limit) REFERENCES RateLimit(id),
min INT,
max INT
);

Now, I inserted value each table;


INSERT INTO RateLimit VALUES(1,'RateLimit1',1000,10000);
INSERT INTO RateLimit VALUES(2,'RateLimit2',2000,20000);
INSERT INTO RateLimit VALUES(3,'RateLimit3',3000,30000);
INSERT INTO RateLimit VALUES(4,'RateLimit4',4000,40000);
INSERT INTO RateLimit VALUES(5,'RateLimit5',5000,50000);


INSERT INTO Policy VALUES(1,1,10,100);
INSERT INTO Policy VALUES(2,2,10,100);
INSERT INTO Policy VALUES(3,3,10,100);
INSERT INTO Policy VALUES(4,4,10,100);
INSERT INTO Policy VALUES(5,5,10,100);
INSERT INTO Policy VALUES(6,5,10,100);
INSERT INTO Policy VALUES(7,4,10,100);
INSERT INTO Policy(id,min,max) VALUES(8,10,100);



=========================================================================
SELECT A.id,B.name,B.min,B.max FROM Policy A,RateLimit B WHERE A.rate_limit=B.id;

The result of above query statement is like this.
---------------------------------------------
id rate_limit min max
---------------------------------------------
1 RateLimit1 1000 10000
2 RateLimit2 2000 20000
3 RateLimit3 3000 30000
4 RateLimit4 4000 40000
5 RateLimit5 5000 50000
6 RateLimit5 5000 50000
7 RateLimit4 4000 40000
---------------------------------------------


How can I get whole recordset using join, that is, if Policy table's rate_limit is null, return Policy's min/max otherwise, return RateLimit's min/max, just like below result.

---------------------------------------------
id rate_limit min max
---------------------------------------------
1 RateLimit1 1000 10000
2 RateLimit2 2000 20000
3 RateLimit3 3000 30000
4 RateLimit4 4000 40000
5 RateLimit5 5000 50000
6 RateLimit5 5000 50000
7 RateLimit4 4000 40000
8 10 100
---------------------------------------------

View 3 Replies View Related

Create An Extra Table (for Audit Purpose) For Every Existing Table In A Database

Mar 28, 2008

Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.

Here is my code:





Code Snippet

USE pubs

DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME = 'sales'

WHILE @TABLE_NAME IS NOT NULL
BEGIN

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_NAME = 'sales'

SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME''


SELECT * INTO @AUDIT_TABLE
FROM @TABLE_NAME

TRUNCATE TABLE @AUDIT_TABLE
ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime Char(50),AuditUser Char(50)


SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME NOT LIKE 'audit%'

END
Thanks. ..

View 6 Replies View Related

NULL Values Returned When Reading Values From A Text File Using Data Reader.

Feb 23, 2007

I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?

View 12 Replies View Related

SQL 2012 :: Explicit Columns Or NULL Foreign Keys?

Mar 19, 2014

I have a table called Appointment, for storing (you guessed it) appointments at a medical practice. If an appointment is cancelled, I want to collect a cancellation reason, so let's say that I create a second table called Cancellation which has a foreign key relating to the Appointment table's primary key, AppointmentID, and another column, Reason. Now, in order to indicate that an appointment was cancelled, I could include a Cancelled column in the Appointment table with a bit datatype, or instead I could infer that an appointment must be cancelled if it has a corresponding record in the Cancellation table.

It may be that it'd be better to store the cancellation reason in the Appointment table - But regardless, let's say I stick with the two-table solution described above, and I subsequently want to write a query to list all appointments which have been cancelled. If I had the Cancelled column in the Appointment table, I could simply query for all records in that table where that column's value was FALSE. If I went the other way and DIDN'T have a Cancelled column, I could instead write a query joining the Appointment and Cancellation tables to return all records in Appointment with a corresponding record in Cancellation.

That latter method, whilst slightly more complicated because it involves joining two tables, seems to me to be the most normalised. Instead of storing the fact that an appointment is cancelled in two different tables, that fact is only stored in the Cancellation table. Would there be a performance hit in using the two-table, 'inferred cancellation' method rather than just having a bit column in the Appointment table? Would that performance hit be enough to persuade you to use a Cancellation column in the Appointment table instead? And what about if I were to apply that method to other things associated with each appointment, e.g. Is it completed? Is it chargeable to the client or an insurance company? Is the client and in-patient or out-patient?

View 6 Replies View Related

Integration Services :: SSIS Reads Nvarchar Values As Null When Excel Column Includes Decimal And String Values

Dec 9, 2013

I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?

CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]

View 5 Replies View Related

Null Values For Datetime Values Converted To '0001-01-01'

Mar 29, 2006

Hi

can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.




If Not Row.Datum1_IsNull Then

Row.OutputDatum1 = Row.Datum1

Else

Row.OutputDatum1 = CDate(System.Convert.DBNull)

End If



Any help welcome.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved