CODEWITHSUNDEEP
sqlms-access
M_kul
M_kul

Reputation: 173

How to pass a Null as int type in SQL in MS Access

I have the following SQL query. Please see line marked with ****. I would essentially like to pass that column as INT type but with null values for the first sub query so that I can insert integers into the column with the second sub query.

How can this be done? 

select 
    fq.fk_spEngineerID, 
    fq.spQuoteID,
    fq.spOrderID, 
    fq.BU,
    fq.spProductTypeID, 
    fq.fk_spCompanyID, 
    fq.fk_spOfficeID
From 
    (Select 
         fk_spEngineerID, 
         spQuoteID,
         ****0 as spOrderID,**** 
         BU, 
         spProductTypeID, 
         fk_spCompanyID, 
         fk_spOfficeID, 
     From 
         QuotestatsSubQuery 
     Union All 
     Select 
         fk_spEngineerID,
         NULL as spQuoteID,
         spOrderID,
         BU, 
         spProductTypeID, 
         fk_spCompanyID, 
         fk_spOfficeID 
     From  
         OrderstatsSubQuery) as fq 
Group By  
    fq.fk_spEngineerID,
    fq.spQuoteID,
    fq.spOrderID,  
    fq.BU, 
    fq.spProductTypeID, 
    fq.fk_spCompanyID, 
    fq.fk_spOfficeID;

Upvotes: 1

Views: 1174

Answers (3)

MtwStark
MtwStark

Reputation: 4058

you can simply do this:

select * 
from (
  -- ADD THIS SELECT/WHERE 1=0 TO DEFINE TABLE STRUCTURE
  select 
    fk_spEngineerID, 
    spQuoteID,
    0 as spOrderID,
    BU, 
    spProductTypeID, 
    fk_spCompanyID, 
    fk_spOfficeID, 
   from QuotestatsSubQuery 
   WHERE 1=0  -- THIS RETURNS NO RECS BUT YOU HAVE DEFINED TABLE STRUCTURE
   Union All 
   Select 
     fk_spEngineerID, 
     spQuoteID,
     NULL as spOrderID,
     BU, 
     spProductTypeID, 
     fk_spCompanyID, 
     fk_spOfficeID, 
   From QuotestatsSubQuery 
   Union All 
   Select 
     fk_spEngineerID,
     NULL as spQuoteID,
     spOrderID,
     BU, 
     spProductTypeID, 
     fk_spCompanyID, 
     fk_spOfficeID 
   From OrderstatsSubQuery
) as fq 
Group By  
  fq.fk_spEngineerID,
  fq.spQuoteID,
  fq.spOrderID,  
  fq.BU, 
  fq.spProductTypeID, 
  fq.fk_spCompanyID, 
  fq.fk_spOfficeID;

Hope this helps

Upvotes: 0

M_kul
M_kul

Reputation: 173

In case Anyone is looking for solution to similar problem the following solves the issue : 

select 
    fq.fk_spEngineerID, 
    fq.spQuoteID,
    fq.spOrderID, 
    fq.BU,
    fq.spProductTypeID, 
    fq.fk_spCompanyID, 
    fq.fk_spOfficeID
From (
    Select 
        fk_spEngineerID, 
        spQuoteID,
        Null as spOrderID, 
        BU, 
        spProductTypeID, 
        fk_spCompanyID, 
        fk_spOfficeID, 
From QuotestatsSubQuery 
Union All 
     Select 
        fk_spEngineerID,
        NULL as spQuoteID,
        CINT(spOrderID),
        BU, 
        spProductTypeID, 
        fk_spCompanyID, 
        fk_spOfficeID 
    From OrderstatsSubQuery
) as fq 
Group By  
    fq.fk_spEngineerID,
    fq.spQuoteID,
    fq.spOrderID,  
    fq.BU, 
    fq.spProductTypeID, 
    fq.fk_spCompanyID, 
    fq.fk_spOfficeID;

Upvotes: 2

Utsav
Utsav

Reputation: 8103

(1) 0 as column_name will assume it is integer. To go an step ahead, you could cast it as Integer (Not sure hot to do it in msaccess).

(2) Your code is missing a comma before 0. Let me know if you get any error running this.

select 
    fq.fk_spEngineerID, 
    fq.spQuoteID,
    fq.spOrderID, 
    fq.BU,
    fq.spProductTypeID, 
    fq.fk_spCompanyID, 
    fq.fk_spOfficeID
From (
    Select 
        fk_spEngineerID, 
        spQuoteID,
       NULL as spOrderID,
        BU, 
        spProductTypeID, 
        fk_spCompanyID, 
        fk_spOfficeID, 
From QuotestatsSubQuery 
Union All 
     Select 
        fk_spEngineerID,
        NULL as spQuoteID,
        spOrderID,
        BU, 
        spProductTypeID, 
        fk_spCompanyID, 
        fk_spOfficeID 
    From OrderstatsSubQuery
) as fq 
Group By  
    fq.fk_spEngineerID,
    fq.spQuoteID,
    fq.spOrderID,  
    fq.BU, 
    fq.spProductTypeID, 
    fq.fk_spCompanyID, 
    fq.fk_spOfficeID;

Upvotes: 0

Related Questions