Validation

Question

Validation

farooq.hbs

SSCrazy

Points: 2083
More actions
July 14, 2011 at 8:31 am

#254555

Hi All
I need a help.. I want to validate the Entry into a column with another column..I will give an example
S.no Account_Code Account_description Account_type
1 11150 Total_owner Asset
2 50000 Long Term liablities Liabilities
------------------------------------------------------------------
i need to validate as if i want to insert new record For Asset as 111111 then account_type should not be Liabilities
If Account_code starts with 1 then asset
If Account_code starts with 5 then Liabilities
If Account_code starts with 3 then equalities
How to validate this

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Are all the inserts going into the same table?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

If you only ever will have the three account types, then you can do this in the code:

INSERT INTO MTable (Account_Code, Account-type)

SELECT Account_Code,

CASE LEFT(Account_Code,1)

WHEN '1' THEN 'Asset'

WHEN '5' THEN 'Liabilities'

WHEN '3' THEN 'Equalities'

END

FROM MyOriginalTable

Any more than three, and I would recommend creating a table with the account codes and account types in it, and joining to it in order to get the correct account type for any given account code.

John

Edit: added a FROM clause to my code.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

John Mitchell-245523 (7/14/2011)
If you only ever will have the three account types, then you can do this in the code:
INSERT INTO MTable (Account_Code, Account-type)
SELECT Account_Code,
CASE LEFT(Account_Code,1)
WHEN '1' THEN 'Asset'
WHEN '5' THEN 'Liabilities'
WHEN '3' THEN 'Equalities'
END
FROM MyOriginalTable
Any more than three, and I would recommend creating a table with the account codes and account types in it, and joining to it in order to get the correct account type for any given account code.
John
Edit: added a FROM clause to my code.

Hmm, this is a T-SQL solution and is not really how it would be done in SSIS.

If all of the data is going into a single table, I'd use a derived column for the Asset, Liabilities stuff and then just pump that into the table (hence my original question).

If different tables, a derived column + conditional split should do the job.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

We don't have all the information we need. If the source and destination were both tables in databases on the same server, then that's exactly how I'd do it. But if we are dealing with text files or Oracle databases or something like that, then I think what you suggest would be better.

John

Phil Parkin SSC Guru Points: 246962 More actions · Answer 5

Ah indeed. I just assumed that this would not be SQL Server to SQL Server, I admit.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

farooq.hbs SSCrazy Points: 2083 More actions · Answer 6

thanks Buddy!!

How to implement this in store procedure......???

Lyk using this validation

IF EXISTS ( SELECT 1 FROM MY TABLE WHERE ACCOUNT_CODE = 10000 AND ACCOUNT_TYPE = ASSETS)

LYK THIS VALIDATION BEFORE INSERTING INTO TABLE

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

So that we don't have to guess, please explain exactly what you are trying to do. Phil has already asked one question. What are your source and destination, and what are the table definitions?

Thanks

John