insert default value in sql union query (1 Viewer)

kobiashi

Registered User.
Local time
Today, 16:31
Joined
May 11, 2018
Messages
258
hi

i am trying to insert a default value in my SQL union script, but it doesnt seem to like what im using, please can some one take a look and let me know if im using the wrong method please, so below is the first part of the union query and where im trying to insert the default value:

Code:
SELECT SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.MCUDate, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_DropdownLists.ListName, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.RSAttention_IDFK, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.EntryType_IDFK, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.MaximoStatus_IDFK, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.MCUTime, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.ServiceRemoval_IDFK, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Title, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.MaximoNumber, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.WorkType, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_DropdownLists_2.ListName, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_DropdownLists_3.ListName, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_DropdownLists_4.ListName, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_Vehicles.VehicleNumber, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_Vehicles_1.VehicleNumber, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_Vehicles_2.VehicleNumber, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Table_Vehicles_3.VehicleNumber, SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.Consist,
ISNULL([SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.InServiceEntryType], 'AfterAMPeak') AS SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.InServiceEntryType
FROM SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak
UNION ALL

this is what i was using

Code:
ISNULL([SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.InServiceEntryType], 'AfterAMPeak') AS SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.InServiceEntryType


im trying to union multiple select queries, im not sure if that makes a difference.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:31
Joined
Aug 30, 2003
Messages
36,118
IsNull() is a test for Null, not a replacement. I suspect you want the Nz() function. I'd also just alias with a name, not with the query first:

Nz(...) As InServiceEntryType
 

kobiashi

Registered User.
Local time
Today, 16:31
Joined
May 11, 2018
Messages
258
IsNull() is a test for Null, not a replacement. I suspect you want the Nz() function. I'd also just alias with a name, not with the query first:

Nz(...) As InServiceEntryType

Awsome, worked a treat, thank you.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:31
Joined
Aug 30, 2003
Messages
36,118
Happy to help!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:31
Joined
Feb 19, 2002
Messages
42,970
it doesn't seem to like what im using
Telling us the error message or result that you thought was incorrect would have been oh so much more helpful.

for this simple case, the Nz() offered by pbaldy is a better choice (and fixed your syntax problems) but there is nothing inherently wrong with using IsNull() INSIDE an IIf() to substitute a value. HOWEVER, the other problem was with the alias:

as SubQuery_ENG_MCU_InServiceIssues_AfterAMPeak.InServiceEntryType

1. You have to provide a way to use the result of the IsNull() so valid syntax would be:
IIf(IsNull(yourfield), "replacementvalue", yourfield) As newname. When you use an IIf() best practice always uses complete syntax so you provide a result for the true condition and also the false condition.
2. You can't qualify the alias with a table or query name. It doesn't make any sense
3. You can't alias a fieldname with it's original name. Although SQL Server does allow this, Access does not)
 

Users who are viewing this thread

Top Bottom