SQL Statement with an if statement??

[dpwsmw]

I have this insert, first of all it doesnt work, maybe I need a Join statement, But is there an easier way to write this, cause it will not work, any help would be appreciated.

Thanks in advance


Insert into [dbo].[tblArTransHeader] (
TransId,
TransType,
BatchId,
CustId,
ShipToName,
ShipToAddr1,
ShipToAddr2,
ShipToCity,
ShipToRegion,
ShipToPostalCode,
TermsCode,
TaxableYN,
InvcNum,
InvcDate,
Rep1Id,
Rep1Pct,
GLPeriod,
FiscalYear,
TaxGrpID,
NonTaxSubtotal,
DistCode,
CurrencyID,
SumHistPeriod
)

Select Arecvid,
'1',
'######',
SubscriberNumber,
[Name],
addr1,
addr2,
city,
state,
zip,
terms,
taxable,
NINVC,
InvoiceDate,
slsrep1,
rep1pct,
[MO],
YR,
taxloc,
Debit,
distcode,
currency,
[MO]

from TST.dbo.ACMARVIEW1

if TST.dbo.ACMARVIEW1.flatrate <> 0
Insert into dbo.tblArTransDetail (
TransID,
EntryNum,
PartId,
[Desc],
AddnlDesc,
TaxClass,
GLAcctSales,
GLAcctCOGS,
GLAcctInv,
QtyOrdSell,
UnitsSell,
QtyShipSell,
UnitPriceSell
)

Select Arecvid,
'1',
'MONTHLYFEE',
'Transaction Fee',
left (FlatRateCharge,12),
'0',
'5001001',
'4001001',
'5001001',
NumFlatRate,
'EACH',
NumFlatRate,
FlatRate
from TST.dbo.ACMARVIEW1

if TST.dbo.ACMARVIEW1.rate <> 0
Insert into dbo.tblArTransDetail (
TransID,
EntryNum,
PartId,
[Desc],
TaxClass,
GLAcctSales,
GLAcctCOGS,
GLAcctInv,
QtyOrdSell,
UnitsSell,
QtyShipSell,
UnitPriceSell
)
Select Arecvid,
'2',
'MONTHLYFEE',
'Percentage Fee',
'0',
'5001001',
'4001001',
'5001001',
RateCharge,
'EACH',
RateCharge,
Rate
from TST.dbo.ACMARVIEW1
if TST.dbo.ACMARVIEW1.maximusrate <> 0
Insert into dbo.tblArTransDetail (
TransID,
EntryNum,
PartId,
[Desc],
TaxClass,
GLAcctSales,
GLAcctCOGS,
GLAcctInv,
QtyOrdSell,
UnitsSell,
QtyShipSell,
UnitPriceSell
)
Select Arecvid,
'3',
'MAXFEE',
'Maximum Fee',
'0',
'5001001',
'4001001',
'5001001',
'1',
'EACH',
'1',
MaximusRate
from TST.dbo.ACMARVIEW1

if TST.dbo.ACMARVIEW1.delinquencycharge <> 0
Insert into dbo.tblArTransDetail (
TransID,
EntryNum,
PartId,
[Desc],
TaxClass,
GLAcctSales,
GLAcctCOGS,
GLAcctInv,
QtyOrdSell,
UnitsSell,
QtyShipSell,
UnitPriceSell
)
Select Arecvid,
'4',
'DELFEE',
'Delinquency Charge',
'0',
'5001001',
'4001001',
'5001001',
'1',
'EACH',
'1',
delinquencyCharge
from TST.dbo.ACMARVIEW1

 

[DotNetGnat]

consider using CASE statements...

-DNG

 

[dpwsmw]

I tried this case statement and got this error:

Server: Msg 8133, Level 16, State 1, Line 1
None of the result expressions in a CASE specification can be NULL.

Basically what I want it to do is if the
if TST.dbo.ACMARVIEW1.flatrate <> 0 then goto the next insert and then the next if statement is
if TST.dbo.ACMARVIEW1.rate <> 0
then goto the next if statement and so on, any ideas??




Insert into dbo.tblArTransDetail (
TransID,
EntryNum,
PartId,
[Desc],
AddnlDesc,
TaxClass,
GLAcctSales,
GLAcctCOGS,
GLAcctInv,
QtyOrdSell,
UnitsSell,
QtyShipSell,
UnitPriceSell
)

Select Arecvid,
'1',
'MONTHLYFEE',
'Transaction Fee',
left (FlatRateCharge,12),
'0',
'5001001',
'4001001',
'5001001',
NumFlatRate,
'EACH',
NumFlatRate,
case
when FlatRate <> 0 then null
end
from TST.dbo.ACMARVIEW1

 

[amrita418]

If this a one time load then maybe you can run each of these statements using a where clause. Using case is painful in your case.

Try something like:

Insert into dbo.tblArTransDetail (TransID, EntryNum, PartId,
[Desc], AddnlDesc, TaxClass, GLAcctSales, GLAcctCOGS,
GLAcctInv, QtyOrdSell, UnitsSell, QtyShipSell, UnitPriceSell
)
Select Arecvid,
       '1',
       'MONTHLYFEE',
       'Transaction Fee',
       left (FlatRateCharge,12),
       '0',
       '5001001',
       '4001001',
       '5001001',
       NumFlatRate,
       'EACH',
       NumFlatRate
from   TST.dbo.ACMARVIEW1
where  TST.dbo.ACMARVIEW1.flatrate <> 0

similar code for other scripts.

But if this is a script that you run often then here is how you need to code it. (only a sample to get you started).

Insert into dbo.tblArTransDetail (TransID, EntryNum, PartId, [Desc])
Select Arecvid,
    case when TST.dbo.ACMARVIEW1.flatrate <> 0 then '1'
         when TST.dbo.ACMARVIEW1.rate <> 0 then '2'
         when TST.dbo.ACMARVIEW1.maximusrate <> 0 then '3'
         when TST.dbo.ACMARVIEW1.delinquencycharge <> 0 then '4' else null end,
    case when TST.dbo.ACMARVIEW1.flatrate <> 0 or TST.dbo.ACMARVIEW1.rate <> 0 then 'Monthly Fee'
         when TST.dbo.ACMARVIEW1.maximusrate <> 0 then 'Max Fee'
         when TST.dbo.ACMARVIEW1.delinquencycharge <> 0 then 'Del Fee' else null end,
    case when TST.dbo.ACMARVIEW1.flatrate <> 0 then 'Transaction Fee'
         when TST.dbo.ACMARVIEW1.rate <> 0 then 'Percentage Fee'
         when TST.dbo.ACMARVIEW1.maximusrate <> 0 then 'Maximum Fee'
         when TST.dbo.ACMARVIEW1.delinquencycharge <> 0 then 'Delinquency Charge' else null end
from TST.dbo.ACMARVIEW1

Regards,
AA

 

[dpwsmw]

I dont understand this part
case when TST.dbo.ACMARVIEW1.flatrate <> 0 then '1'
when TST.dbo.ACMARVIEW1.rate <> 0 then '2'
when TST.dbo.ACMARVIEW1.maximusrate <> 0 then '3'
when TST.dbo.ACMARVIEW1.delinquencycharge <> 0 then '4' else null end,

why am I changing the numbers?

 

[DotNetGnat]

that is equivalent to the if conditions you were having in your original query and you changed values in your query based on the conditions...so that same thing is done here with the case statements...

-DNG