How Do I assign a value if a value in the field is empty

50 pts.
Tags:
CASE statement
SQL
SQL Query
In my script (that is not working yet) I want to make sure if "full_value" (a field) is empty in a particular record then the value zero will be inserted. This is working in Access, but I'm trying to rebuild the query in SQL. Thank you. SELECT DISTINCT oas_balance.cmpcode, oas_balance.el1, oas_balance.el2, oas_balance.full_value, CASE WHEN balcode = 'ACTUAL' And yr = '2008' And period = '6',full_value ,0 AS Act_Amt, CASE WHEN balcode ='BUDGET' And yr = '2008' And period ='6',full_value,0) AS Bud_Amt, CASE WHEN balcode ='ACTUAL' And yr = '2008' - 1 And period = '6', full_value ,0 AS LY_Amt, CASE WHEN balcode ='ACTUAL' And period <= '6' And yr = '2008',full_value,0 AS ActYtd_Amt, CASE WHEN balcode ='BUDGET' And period <= '6' And yr = '2008',full_value,0 AS BudYTD_Amt, CASE WHEN balcode ='ACTUAL' And period <='6' And yr ='2008'-1, full_value,0 AS LYYTD_Amt, oas_balance.balcode, oas_balance.yr, oas_balance.period, oas_balance.repbasis INTO oas_testit FROM oas_balance WHERE (((oas_balance.cmpcode)="FORNAIO") AND ((oas_balance.el1)>"39999") AND ((oas_balance.balcode)="ACTUAL" Or (oas_balance.balcode)="BUDGET") AND ((oas_balance.yr)= '2008' Or (oas_balance.yr)=('2008'-1)) AND ((oas_balance.period)<='6') AND ((oas_balance.repbasis)='3'));
ASKED: August 13, 2008  12:31 AM
UPDATED: August 13, 2008  1:13 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

I’m seeing a couple of issues here.

First, I don’t see you closing your case statements.

<pre>SELECT DISTINCT …
CASE WHEN balcode = ‘ACTUAL’ AND yr = 2008 AND period = 6 THEN
full_value
END AS Act_Amt,
…</pre>

Now if the full_value has a NULL value in it, it’s a little easier to work with. You can use the IS_NULL function to see if it’s NULL and replace the value if it is. That would turn the above code into

<pre>SELECT DISTINCT …
CASE WHEN balcode = ‘ACTUAL’ AND yr = 2008 AND period = 6 THEN
IS_NULL(full_value, 0)
END AS Act_Amt,
…</pre>

Now if the value is blank, instead of NULL you need to use another CASE statement within the first case statement like this.

<pre>SELECT DISTINCT …
CASE WHEN balcode = ‘ACTUAL’ AND yr = 2008 AND period = 6 THEN
CASE WHEN full_value <> ” THEN full_value ELSE 0 END
END AS Act_Amt,
…</pre>

Now, it appears that all your data comes from a single table so you need to do a cross tab query. If you have SQL 2005 you can use the PIVOT function to swing the data around. If you have SQL 2000 or below you will need to do it the hard way.

<pre>SELECT DISTINCT oas_balance.cmpcode, oas_balance.el1, oas_balance.el2, oas_balance.full_value,
MAX(CASE WHEN balcode = ‘ACTUAL’ AND yr = 2008 AND period = 6 THEN
CASE WHEN full_value <> ” THEN full_value ELSE 0 END
END) AS Act_Amt,

FROM oas_balance
WHERE …
GROUP BY oas_balance.cmpcode, oas_balance.el1, oas_balance.el2, oas_balance.full_value
</pre>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following