Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
add debit to case statement
select *,
case resultcode
when 'Canceled' then 'Payment Processed'
when 'Completed' then 'Payment Processed'
when 'confirmed' then 'Payment Processed'
when 'Denied' then 'Payment Processed'
when 'Pending' then 'Payment Processed'
when 'unconfirmed' then 'Payment Processed'
when '' then 'Payment Processed'
when 'Reversed' then 'Refunded'
end as resultcodetype
,debit - convert(money, paymentfee) as 'NetAmount' from payments where isnumeric(paymentfee) = 1
this query works, but I want to add another condition to resultcodetype
when resultcode='' and (debit>=0 or debit has no value) then 'Payment Processed'
when resultcode='' and debit<0 then 'Refunded'
case resultcode
when 'Canceled' then 'Payment Processed'
when 'Completed' then 'Payment Processed'
when 'confirmed' then 'Payment Processed'
when 'Denied' then 'Payment Processed'
when 'Pending' then 'Payment Processed'
when 'unconfirmed' then 'Payment Processed'
when '' then 'Payment Processed'
when 'Reversed' then 'Refunded'
end as resultcodetype
,debit - convert(money, paymentfee) as 'NetAmount' from payments where isnumeric(paymentfee) = 1
this query works, but I want to add another condition to resultcodetype
when resultcode='' and (debit>=0 or debit has no value) then 'Payment Processed'
when resultcode='' and debit<0 then 'Refunded'
Asked:
Who is Participating?
select *,
case
when resultcode = 'Canceled' then 'Payment Processed'
when resultcode = 'Completed' then 'Payment Processed'
when resultcode = 'confirmed' then 'Payment Processed'
when resultcode = 'Denied' then 'Payment Processed'
when resultcode = 'Pending' then 'Payment Processed'
when resultcode = 'unconfirmed' then 'Payment Processed'
when (resultcode = '' and (debit>=0 or debit is null)) then 'Payment Processed'
when (resultcode = '' and (debit < 0)) then 'Refunded'
when resultcode = '' then 'Payment Processed'
when resultcode = 'Reversed' then 'Refunded'
end as resultcodetype
I think you almost provided the answer unless I'm missing something:
select *,
case resultcode
when 'Canceled' then 'Payment Processed'
when 'Completed' then 'Payment Processed'
when 'confirmed' then 'Payment Processed'
when 'Denied' then 'Payment Processed'
when 'Pending' then 'Payment Processed'
when 'unconfirmed' then 'Payment Processed'
when '' and (debit>=0 or debit is null) then 'Payment Processed'
when '' and debit<0 then 'Refunded'
--when '' then 'Payment Processed' -- I think this no longer makes sense. Please verify.
when 'Reversed' then 'Refunded'
end as resultcodetype
,debit - convert(money, paymentfee) as 'NetAmount' from payments where isnumeric(paymentfee) = 1
select *,
case resultcode
when 'Canceled' then 'Payment Processed'
when 'Completed' then 'Payment Processed'
when 'confirmed' then 'Payment Processed'
when 'Denied' then 'Payment Processed'
when 'Pending' then 'Payment Processed'
when 'unconfirmed' then 'Payment Processed'
when '' and (debit>=0 or debit is null) then 'Payment Processed'
when '' and debit<0 then 'Refunded'
--when '' then 'Payment Processed' -- I think this no longer makes sense. Please verify.
when 'Reversed' then 'Refunded'
end as resultcodetype
,debit - convert(money, paymentfee) as 'NetAmount' from payments where isnumeric(paymentfee) = 1
I believe
case
when debit >= 0 or debit = 'null'
then 'Payment Processed
when debit <= 0
then 'Refunded'
is what you need
case
when debit >= 0 or debit = 'null'
then 'Payment Processed
when debit <= 0
then 'Refunded'
is what you need
debit may be null or debit may be '' or debit may be a alphanumeric value
if this is the case, then I want resultcodetype to be 'Payment Processed'
if this is the case, then I want resultcodetype to be 'Payment Processed'
use ISNUMERIC to check whether debit is numeric or not
when ((resultcode = '') and ((Debit is null) or (Debit = '') or (not ISNUMERIC(Debit)) ) then
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
select *,
case
when resultcode = 'Canceled' then 'Payment Processed'
when resultcode = 'Completed' then 'Payment Processed'
when resultcode = 'confirmed' then 'Payment Processed'
when resultcode = 'Denied' then 'Payment Processed'
when resultcode = 'Pending' then 'Payment Processed'
when resultcode = 'unconfirmed' then 'Payment Processed'
when resultcode = '' and (debit is null or ISNUMERIC(debit) = 0 or cast(debit as money) >=0) then 'Payment Processed'
when resultcode = '' and debit is not null and ISNUMERIC(debit) = 1 and CAST(debit as money)<0 then 'Refunded'
--when resultcode = '' then 'Payment Processed' -- I think this no longer makes sense. Please verify.
when resultcode = 'Reversed' then 'Refunded'
end as resultcodetype
,debit - convert(money, paymentfee) as 'NetAmount' from payments where isnumeric(paymentfee) = 1