IF Statement Query Syntax Error

Question

I am trying to replace a foreach statement with a single SQL query, my code I want to change looks like this:

 this.Settings = new List<Setting>()
        {
            // Write all the settings from the VB
            new Setting() { Name = "NOOFORDERS", Value = null },
            new Setting() { Name = "ORDNVGREQ", Value = null },
            new Setting() { Name = "IncWendsDel", Value = null },
            new Setting() { Name = "AUTOEMAIL_INVOICE", Value = null },
            new Setting() { Name = "REQEMAIL_INVOICE", Value = null },
            new Setting() { Name = "REQEMAIL_SALES", Value = null },
            new Setting() { Name = "REQEMAIL_PICKSLIP", Value = null },
            new Setting() { Name = "REQEMAIL_DESPATCH", Value = null },
            new Setting() { Name = "CARRPARTSHIP", Value = null },
            new Setting() { Name = "DELNOTEREQ", Value = null },
            new Setting() { Name = "REQ_CARRPARTSHIP", Value = null },
            new Setting() { Name = "BUDGETREQ", Value = null },
            new Setting() { Name = "PriceList", Value = null },
            new Setting() { Name = "EmployeeRenew", Value = null },
            new Setting() { Name = "Warehouse", Value = null },
            new Setting() { Name = "EmployeeDetails", Value = null },
        };
var value = this.db.tblbus_setvalues.Where( x => x.SettingID == setting.Name && x.BusinessCode == this.Business.BusinessCode ).FirstOrDefault().Value;
switch (value.GetType())
{
    case typeof(bool):
        (value) ? setting.Value = "true" : setting.Value = "false";
        break;
    default:
        setting.Value = value.ToString();
        break;
    }
    if ( !string.IsNullOrEmpty(setting.Value) ) setting.Value = this.db.tblbus_settings.Where( x => x.SettingID == setting.Name ).FirstOrDefault().Default.ToString();

My SQL which I want to replace the above code with looks like this:

SELECT t1.SettingID, t2.Value, t1.Default
FROM `tblbus_settings` t1
LEFT JOIN `tblbus_setvalues` t2
ON t1.SettingID = t2.SettingID
WHERE `BusinessCode` = "XXX" // Dependency
AND t1.`BusType` = "CUS"

Can anyone help me write an IF statement onto this SQL query which will only display the t2.Value if the t1.Default is null?

I tried things like:

IF(t2.value == NULL) { t1.Default }
ELSE { t2.Value }

But they all give me SQL syntax errors.

wero · Accepted Answer · 2016-04-14 15:12:22Z

3

You can use the coalesce function which returns the first non-NULL value in the list, or NULL if there are no non-NULL values:

 SELECT t1.SettingID, COALESCE(t2.Value, t1.Default) FROM ...

answered Apr 14, 2016 at 15:12

wero

33.1k3 gold badges63 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jaquarh Over a year ago

This returned 141 rows where there should be 150 rows :/ Strange - investigation time but thanks for the source! I'll mark it when I can

chris.ellis Over a year ago

This is a better answer than the one I submitted, go with this :)

chris.ellis · Accepted Answer · 2016-04-14 15:11:02Z

2

Something like this?

SELECT t1.SettingID, t2.Value, t1.Default, 
CASE t1.Default IS NOT NULL
   THEN t1.Default 
   ELSE t2.Value
FROM `tblbus_settings` t1
LEFT JOIN `tblbus_setvalues` t2
ON t1.SettingID = t2.SettingID
WHERE `BusinessCode` = "XXX" // Dependency
AND t1.`BusType` = "CUS"

See this for meore https://msdn.microsoft.com/en-gb/library/ms181765.aspx

answered Apr 14, 2016 at 15:11

chris.ellis

8936 silver badges10 bronze badges

Collectives™ on Stack Overflow

IF Statement Query Syntax Error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related