SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 constraining insert into
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

iainiow
Starting Member

2 Posts

Posted - 01/19/2010 :  06:27:16  Show Profile  Reply with Quote
Hi everyone,

Im having a bit of a mental block and a bit of noob logic.

I am trying to perform an insert into statement within a stored procedure. the result of my code is an error 515 "cannot insert null in to column..."

The basic logic I need to apply is:

Insert in to table "holding" one row where the sum of "amount" is greater than zero, the type is "CD" and the mac_code is in "ST1" or "ST2".

I also need to apply a value from another table to a field in the inserted row..

Not to difficult one would presume!

Here is my code:

INSERT INTO holding
(Import_Record_Type, Transaction_Date,Mac_Code, CAN, Ref_No, Ref_No2, amount, Fund, Type, User_Code, Source_Code,
Area_Code,location_code,narrative, pay_amt_base, VAT_code)

SELECT
60,MAX(transaction_date), 'SCSH1',
(select next_count from sequence where SEQUENCE_CODE = 'X0001'),
'91350943011','BANKING,Sum(tran_amt_base)*1,'05','CD','SV','XX','BTCC','SCALES1',
S BANKING ' ++ CONVERT(VARCHAR(19),DATEADD(S,max(transaction_date),'01-Jan-1970'),103) , Sum(tran_amt_base)*-1, 'AN'

FROM aim_t_holding

WHERE (tran_amt_base >0 AND mac_code in ('ST1','ST2') and Type = ('CD'))

If a matching combination of mac code, amount and type exists in the table the code performs the insert just fine. if it cant find an entry, i would expect it to not even attempt the insert. It seems to want to try the insert regardless but errors with: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Transaction_Date', table 'Aim_Test.dbo.HOLDING'; column does not allow nulls. INSERT fails.

Any ideas how i can straighten this out??

manythanks




Kristen
Test

United Kingdom
22403 Posts

Posted - 01/19/2010 :  06:34:42  Show Profile  Reply with Quote
Try running the SELECT (without the INSERT INTO) and see what data you get (and add more "debug" columns if you need to). That might shed some light on it.

Add

HAVING MAX(transaction_date) IS NOT NULL

after the WHERE to only get the "broken" ones.

Edited by - Kristen on 01/19/2010 06:35:22
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000