Re: IF statement in SQL?

On Wed, 09 Nov 2005 19:20:28 GMT, bakedmike via SQLMonster.com wrote:

(snip)
>What I want to say is
>
>If Acct_Type = E
>INSERT INTO db2.dbo.Accounts (List all the fields in the table)
> SELECT List of Fields in the Table FROM db1.dbo.Accounts WHERE db1.
>dbo.Accounts.ACCT_ID
>NOT IN (Limitors) AND (db1.dbo.ACCOUNTS.ACCT_ID IN
>(SELECT db2.dbo.EMPLOYEE.EID FROM db2.dbo.EMPLOYEE))
>If Acct_Type = C
>INSERT INTO db2.dbo.Accounts (List of fields in table)
> SELECT List of Fields in Table FROM db1.dbo.Accounts WHERE db1.dbo.
>Accounts.ACCT_ID
>NOT IN (Limitors) AND (db1.dbo..ACCOUNTS.ACCT_ID IN
>(db2.dbo.COMPANY.CID FROM db2.dbo.COMPANY))

Hi bakedmike,

But what is this "Acct_Type"? Is it a variable? In that case, it should
be prefixed with @, and the rest of the code is just fine.

Or is Acct_Type a column in the Accounts table, and should the decision
to match against either the COMPANY or the EMPLOYEE table me made on a
per-row basis? In that case, try:

INSERT INTO db2.dbo.Accounts (List of fields in table)
SELECT List of Fields in Table
FROM db1.dbo.Accounts
WHERE db1.dbo.Accounts.ACCT_ID NOT IN (Limitors)
-- The line above is bad syntax and won't run.
-- I hope you just made a mistake when copying.
AND ( ( db1.dbo.AcctType = 'C'
AND db1.dbo.ACCOUNTS.ACCT_ID IN
(SELECT db2.dbo.COMPANY.CID
FROM db2.dbo.COMPANY))
OR ( db1.dbo.AcctType = 'E'
AND db1.dbo.ACCOUNTS.ACCT_ID IN
(SELECT db2.dbo.COMPANY.CID
FROM db2.dbo.EMPLOYEE)))

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.