Posted - 01/19/2010 : 06:27:16
| 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 next_count from sequence where SEQUENCE_CODE = 'X0001'),
S BANKING ' ++ CONVERT(VARCHAR(19),DATEADD(S,max(transaction_date),'01-Jan-1970'),103) , Sum(tran_amt_base)*-1, 'AN'
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??