how to use IIf In Select / Insert Ststement 1

[andypower]

i m developing an application in vb 6 and sql server 7
i want to copy some records from Voucher table to another table-AcCashBook. i used insert and select query but i want to use iff condition in select statement
plz help me in building this query

StrSQL = "Insert Into AcCashBook(Transactionid,LedgerName,Trans_date,Particulars,VoucherType,VoucherNo,ReceiptAmt,PaymentAmt,Narration) "
StrSQL = StrSQL & "Select Transactionid,'" & strAccountName & "',VoucherDate,AccountName,VoucherType,VoucherNo,iif(Debit<>0,0,Debit),iif(Credit<>0,0,Credit),Narration From Voucher,Accountcode"
StrSQL = StrSQL & "where LedgerName<>" & AccCode & " and VoucherNo in (select VoucherNo from voucher where LedgerName=" & AccCode & ") group by TransactionId,VoucherDate,AccountName,VoucherType,VoucherNo,Debit,Credit,Narration

 

[r937]

IIF should be replaced by CASE

change: IIF(condition,truevalue,falsevalue)

to: CASE WHEN condition THEN truevalue ELSE falsevalue END

that said, there may be a problem

you have iif(Debit<>0,0,Debit)

this says "if Debit is not 0 the answer is 0, otherwise (Debit is 0) the answer is Debit"

in other words, 0 no matter what

so you don't need CASE, just use a 0

unless you actually meant something else?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)