[shambhavi Contributions: 0Level 1☆]

I am having issues making this SQL work, looks right to me…any help is appreciated.

CASE {custcol_item_category} WHEN ‘Other’ THEN {fxamount} WHEN ‘Maintenance – Renewal’ AND ({revrecenddate} – {revrecstartdate} = 0) THEN ‘0’ ELSE (({fxamount}/({revrecenddate} – {revrecstartdate})) * 365) WHEN ‘License – Term’ THEN {fxamount} WHEN ‘Maintenance – New’ AND ({revrecenddate} – {revrecstartdate} = 0) THEN ‘0’ ELSE (({fxamount}/({revrecenddate} – {revrecstartdate})) * 365) WHEN ‘License – Perpetual’ AND {revrectemplate.name} != ‘License Immediate’ THEN ‘0’ ElSE WHEN ({revrecenddate} – {revrecstartdate} = 0) THEN ‘0’ ELSE (({fxamount}/({revrecenddate} – {revrecstartdate})) * 365) CASE WHEN {revrectemplate.name} = ‘License Immediate’ THEN {vsoeallocation} END
This is a cached copy. Click here to see the original post.

[RaysterJo Contributions: 0Level 1☆]

I am not totally clear on the entire statement, but here is a possible solution. Netsuite requires a CASE for each when and a termination of each CASE.

CASE WHEN {custcol_item_category} IN (‘License – Perpetual’)

THEN CASE WHEN {revrectemplate.name} = ‘License Immediate’

THEN {vsoeallocation}

ELSE CASE WHEN {revrecenddate} = {revrecstartdate}

THEN 0

ELSE (({fxamount}/({revrecenddate} – {revrecstartdate})) * 365)

END

END

ELSE CASE WHEN {custcol_item_category} IN (‘Other’, ‘License – Term’)

THEN {fxamount}

ELSE CASE WHEN {revrecenddate} = {revrecstartdate}

THEN 0

ELSE CASE WHEN {custcol_item_category} IN (‘Maintenance – Renewal’)

THEN (({fxamount}/({revrecenddate}-{revrecstartdate})) * 365)

ELSE CASE WHEN {custcol_item_category} IN (‘Maintenance – New’)

THEN (({fxamount}/({revrecenddate} – {revrecstartdate})) * 365)

ELSE 0

END

END

END

END

END

[Mondray Contributions: 0Level 1☆]

CASE WHEN {custcol_item_category} in (‘Other’ , ‘License – Term’ ) then {fxamount}

WHEN {custcol_item_category} in (‘Maintenance – Renewal’ , ‘Maintenance – New’ ) AND ({revrecenddate} – {revrecstartdate} = 0) then ‘0’

WHEN {custcol_item_category} = ‘License – Perpetual’ AND {revrectemplate.name} ‘License Immediate’ then ‘0’

WHEN {revrectemplate.name} = ‘License Immediate’ then {vsoeallocation}

WHEN ({revrecenddate} – {revrecstartdate} = 0) then ‘0’

ELSE (({fxamount}/({revrecenddate} – {revrecstartdate})) * 365)

END

This is how I would interpret the initial sql.

[shambhavi Contributions: 0Level 1☆]

Thanks guys…this is a life saver!!

[Author]

Posts