November 8, 2011 at 6:14 am
Hello all,
I'm having trouble trying to get the required results with the following SQL code:
SELECT bh_bildat, bh_bilnum, bh_biltot, bh_type, bh_payamt, bh_biltot - bh_payamt AS 'Amount O/S'
FROM cabilhis
WHERE (bh_matter = '00067603')
This is basically from an Invoice table which shows the invoice type (bh_type - This will either be 'b' for bill or 'a' for credit note), invoice amount (bh_biltot) and amount paid (bh_payamt).
I have been trying to achieve a new column that if bh_type = 'b' and the bill has been paid in full to return 'Yes' and if not paid in full to return 'No'. If bh_type does not equal 'b', I want to return nothing, so ' '.
So to clarify:
IF bh_type = 'b' and 'Amount O/S' = '0' THEN return 'Yes'
IF bh_type = 'b' and 'Amount O/S' > '0' THEN return 'No'
IF bh_type <> 'b' THEN return ' '
I would be grateful for any help on this.
Thanks in advance
November 8, 2011 at 6:20 am
matt_scott1984 (11/8/2011)
Hello all,I'm having trouble trying to get the required results with the following SQL code:
SELECT bh_bildat, bh_bilnum, bh_biltot, bh_type, bh_payamt, bh_biltot - bh_payamt AS 'Amount O/S'FROM cabilhis
WHERE (bh_matter = '00067603')
This is basically from an Invoice table which shows the invoice type (bh_type - This will either be 'b' for bill or 'a' for credit note), invoice amount (bh_biltot) and amount paid (bh_payamt).
I have been trying to achieve a new column that if bh_type = 'b' and the bill has been paid in full to return 'Yes' and if not paid in full to return 'No'. If bh_type does not equal 'b', I want to return nothing, so ' '.
So to clarify:
IF bh_type = 'b' and 'Amount O/S' = '0' THEN return 'Yes'
IF bh_type = 'b' and 'Amount O/S' > '0' THEN return 'No'
IF bh_type <> 'b' THEN return ' '
I would be grateful for any help on this.
Thanks in advance
Check cast() and convert()
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 8, 2011 at 6:22 am
You would do this with a case statement, the case statement syntax goes like this:
Case When <condition> Then <expression> Else <expression> End
Applying the above, your statement should go like this:
case when bh_type = 'b' and (bh_biltot - bh_payamt) = '0' THEN 'Yes' else
case when IF bh_type = 'b' and (bh_biltot - bh_payamt) > '0' THEN 'No' else
case when bh_type <> 'b' THEN return ' ' else ''
end
end
end
basically a nested Case statement. I have not tested this code, so you may get some syntax errors, however this is the way to go.
Hope this helps.
Cheers
Vasan
November 8, 2011 at 6:33 am
Yes, we have no DDL or sample data, so it's difficult to visualise and impossible to test. However, are those nested statements necessary? This should work:
case when bh_type = 'b' and (bh_biltot - bh_payamt) = '0' THEN 'Yes'
when bh_type = 'b' and (bh_biltot - bh_payamt) > '0' THEN 'No'
when bh_type <> 'b' THEN ' '
else ''
end
John
November 8, 2011 at 6:47 am
Yes, John's version is simpler, you dont need a nested Case statement.
Cheers
Vasan
November 8, 2011 at 8:14 am
Thanks all.
John's post worked fine.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply