none
Check For Null in Where Clause When Column is Numeric

    Question

  • I think my question is general enough to not require posting the DDL. I am and will be writing queries for  menus that have two comboboxes.  I  currently have a menu where the user can make a selection from either DBLookupComboBox1, DBLookupComboBox2, or both.  Each combobox is filled from tables.  In the below query snippet, :DBLookupComboBox1 and :DBLookupComboBox2 are parameters containing the values of the selections. They contain column values from the tables and are used in the query.  The selections are blank for no selection. There is no “none” selection. The first combobox has an alphanumeric value. The second has a numeric value. I have to check for null on both.  The first WHERE clause with alphanumeric works.  How would I check the second box with a numeric value for null?

    SELECT TT.DOCTOR,  TP.Patient_No, 
    FROM treat TT
    Inner Join typepatient TP on TT.ednum = TP.Ednum
    Where (:DBLookupComboBox1 Is Null) or (BL.ChargeName = :DBLookupComboBox1)   
    And (:DBLookupComboBox2 Is Null) or (TT.Dr_ID = :DBLookupComboBox2)   
      

    I’ve tried without success:

    And  (IsNull(:DBLookupComboBox2,0) = 0) or (TT.Dr_ID =:DBLookupComboBox2)    

    I did get it to work by initializing the value to zero when the menu first loads and then checking for zero in the query. I would like to see if I can just check it in the query.

    Let me know if you need the DDL or more info.

    Thanks


    • Edited by DavidWIII Tuesday, July 16, 2013 9:00 PM
    Tuesday, July 16, 2013 8:05 PM

Answers

  • If there is no selection then you want all records returned, correct?

    Try

    Where 
    BL.ChargeName Like IsNull(:DBLookupComboBox1, '%')

    Good luck

    Tim Mills-Groninger

    • Marked as answer by DavidWIII Friday, July 19, 2013 8:00 PM
    Tuesday, July 16, 2013 8:49 PM
  • You need to enclose each set between parenthesis, otherwise you will be appling a diff logic.

    ...
    where
        (
        (:DBLookupComboBox1 Is Null) or (BL.ChargeName = :DBLookupComboBox1)
        )
        And
        (
        (:DBLookupComboBox2 Is Null) or (TT.Dr_ID = :DBLookupComboBox2)
        );


    AMB

    Some guidelines for posting questions...


    Wednesday, July 17, 2013 4:57 PM
  • Hi DavidWIII,

    First, I want to inform that this forum is to discuss SQL Server related question and we have little knowledge on C# codes (or VB.NET codes), if you have concern about how to pass data from C# codes (or VB.NET codes) to SQL Server, we can post the question to Windows Form forum. Now ,let’s focus on the SQL Server question. According to the error message, this issue can occur if “:DBLookupComboBox2” is an invalid parameter, how do you get this parameter? Please debug your codes and make sure it is valid. Additionally, I made an example about how to check whether an int data or a varchar data is null with ISNULL function, you can refer to them:

    --ISNULL ( check_expression , replacement_value )
    /*
    If the parameter is null, then replace it with another value
    */
    declare @i int;
    select ISNULL(@i,10);
    -- Result
    -- 10
    declare @vc varchar(10);
    select ISNULL(@vc,'Not Null');
    -- Result
    -- Not Null


    Allen Li
    TechNet Community Support

    • Marked as answer by DavidWIII Friday, July 19, 2013 8:00 PM
    Thursday, July 18, 2013 2:05 AM

All replies

  • If there is no selection then you want all records returned, correct?

    Try

    Where 
    BL.ChargeName Like IsNull(:DBLookupComboBox1, '%')

    Good luck

    Tim Mills-Groninger

    • Marked as answer by DavidWIII Friday, July 19, 2013 8:00 PM
    Tuesday, July 16, 2013 8:49 PM
  • Thanks. I think that will work. I'll have to try it tomorrow.

    I made a mistake copying and pasting in my question. BL.ChargeName in the second Where clause should have another table and column name.

    I edited my question. It should be:

    SELECT TT.DOCTOR,  TP.Patient_No, 
    FROM treat TT
    Inner Join typepatient TP on TT.ednum = TP.Ednum
    Where (:DBLookupComboBox1 Is Null) or (BL.ChargeName = :DBLookupComboBox1)   
    And (:DBLookupComboBox2 Is Null) or (TT.Dr_ID = :DBLookupComboBox2)   

    • Edited by DavidWIII Tuesday, July 16, 2013 9:01 PM mistake in question
    Tuesday, July 16, 2013 8:59 PM
  • Tim,

    Yes, if no selection I want all of the records.  If there is a selection, it would be all records with TT.Dr_ID = :DBLookupComboBox2. After correcting my mistake, I believe this is what you suggested:

    TT.Dr_ID Like IsNull(:DBLookupComboBox2, '%')

    I get the error "Invalid argument" with that.
    Wednesday, July 17, 2013 2:57 PM
  • CREATE TABLE [dbo].[treat] (
    [ID] int NOT NULL,
    [Ednum] numeric(10, 0) NOT NULL,
    [Doctor] char(50) NULL,
    [Title] varchar(50) NULL,
    [PDC] char(6) NULL,
    [Dr_ID] numeric(10, 0) NULL,
    [ED_Attending] char(50) NULL,
    [Psych_Attending] char(50) NULL DEFAULT (''''),
    [Attending_Title] varchar(50) NULL,
    [Attending_DEA] char(16) NULL,
    [Attending_ID] numeric(10, 0) NULL DEFAULT (0),
    [E_C] char(3) NULL,
    [Admit_Discharge] char(10) NULL,
    [Legal] varchar(30) NULL,
    [Discharge] varchar(50) NULL,
    [Follow] varchar(50) NULL,
    [Appointment] varchar(50) NULL,
    [Contact] varchar(50) NULL,
    [Commitx] char(30) NULL,
    [Appointment_Date] char(10) NULL,
    [Appointment_Time] char(10) NULL,
    [Appointment_Period] char(10) NULL,
    [Recommendation] char(30) NULL,
    [Resident] char(30) NULL,
    [Clinician] char(30) NULL,
    [Clinician_Title] varchar(50) NULL,
    [Clinician_ID] numeric(10, 0) NULL,
    [DoctorN] char(30) NULL,
    [AttendingN] char(30) NULL,
    [Recertification] char(10) NULL,
    [COSIGNER] varchar(50) NULL DEFAULT (''''),
    [COSIGNER_ID] numeric(10, 0) NULL DEFAULT (0),
    [COSIGNER_TITLE] varchar(50) NULL,
    [REVIEWED_ALLERGIES] varchar(50) NULL,
    [REVIEWED_ALLERGIES_BY] varchar(50) NULL,
    [REVIEWED_MEDICATIONS] varchar(50) NULL,
    [REVIEWED_MEDICATIONS_BY] varchar(50) NULL,
    CONSTRAINT [PK__treat]
    PRIMARY KEY NONCLUSTERED ([ID] ASC)
    WITH ( PAD_INDEX = OFF,
    FILLFACTOR = 80,
    IGNORE_DUP_KEY = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    DATA_COMPRESSION = NONE )
     ON [PRIMARY]
    )
    ON [PRIMARY]
    WITH (DATA_COMPRESSION = NONE);
    GO
    ALTER TABLE [dbo].[treat] SET (LOCK_ESCALATION = TABLE);
    GO
    CREATE TABLE [dbo].[TypePatient] (
    [ednum] int NOT NULL,
    [BackgroundID] int NOT NULL,
    [Patient_No] varchar(50) NULL,
    [Last_Name] varchar(30) NULL,
    [First_Name] varchar(30) NULL,
    [DATE_] datetime NULL,
    [Time] datetime NULL,
    [Time2] datetime NULL,
    [Visit_Type] char(30) NULL,
    [Visit_Name] char(30) NULL,
    [Sign] numeric(10, 0) NULL,
    [Signdate] datetime NULL,
    [Asign] numeric(10, 0) NULL DEFAULT (0),
    [Asigndate] datetime NULL,
    [ADateTime] datetime NULL,
    [NDateTime] datetime NULL,
    [AdmitDate] datetime NULL,
    [DischargeDate] datetime NULL,
    [AdmitEdnum] int NULL,
    [DischargeEdnum] int NULL,
    [sent] int NULL DEFAULT (0),
    [locked] int NULL DEFAULT (0),
    [AID] int NULL,
    [CoSign] char(3) NULL,
    [NOTE_DESCRIPTION] varchar(50) NULL,
    [TIMELOCK] numeric(10, 0) NULL,
    [ADMIN_UNLOCK] int NULL,
    [CSIGN] numeric(10, 0) NULL DEFAULT (0),
    [CSIGNDATE] datetime NULL,
    [GROUP_NOTE] numeric(10, 0) NULL,
    [CSIGNDATED] datetime NULL,
    [END_TIME] datetime NULL,
    [START_TIME] datetime NULL,
    [system_id] numeric(10, 0) NULL DEFAULT (1),
    [location_id] numeric(10, 0) NULL,
    [HL7_EVENT_ID] numeric(10, 0) NULL,
    [PID_ADDRESS] varchar(100) NULL,
    [PID_CITY] varchar(50) NULL,
    [PID_COUNTY] varchar(4) NULL,
    [PID_DOB] varchar(10) NULL,
    [PID_EPATIENTID] varchar(20) NULL,
    [PID_HPHONE] varchar(40) NULL,
    [PID_IPATIENTID] varchar(20) NULL,
    [PID_MARITAL] varchar(1) NULL,
    [PID_PATIENTACCOUNTNO] varchar(50) NULL,
    [PID_SSN] varchar(16) NULL,
    [PID_STATE] varchar(2) NULL,
    [PID_WPHONE] varchar(40) NULL,
    [PID_ZIPCODE] varchar(10) NULL,
    [PV1_ADMISSIONTYPE] varchar(2) NULL,
    [PV1_ADMITDATETIME] varchar(26) NULL,
    [PV1_ADMITSOURCE] varchar(3) NULL,
    [PV1_ADMITTINGDOCTOR] varchar(60) NULL,
    [PV1_ASSIGNEDLOCATION] varchar(80) NULL,
    [PV1_ATTENDINGDOCTOR] varchar(60) NULL,
    [PV1_DISCHARGEDATETIME] varchar(26) NULL,
    [PV1_DISCHARGEDISPOSITION] varchar(3) NULL,
    [PV1_DISCHARGELOCATION] varchar(50) NULL,
    [PV1_FINANCIALCLASS] varchar(50) NULL,
    [PV1_HOSPITALSERVICE] varchar(3) NULL,
    [PV1_MCID] varchar(50) NULL,
    [PV1_PATIENTCLASS] varchar(1) NULL,
    [PV1_PATIENTTYPE] varchar(2) NULL,
    [PV1_PRIORLOCATION] varchar(80) NULL,
    [PV1_REFERRINGDOCTOR] varchar(60) NULL,
    [PV1_VIPINDICATOR] varchar(2) NULL,
    [BACKGROUNDID2] numeric(10, 0) NULL,
    [EPISODE_ID] numeric(10, 0) NULL,
    [RX_MESSAGE_ID] int NULL,
    [COMMENT] varchar(500) NULL,
    [VERSION_NO] numeric(10, 0) NULL,
    [SMOKING_STATUS] varchar(50) NULL,
    [SMOKING_STATUS_CODE] smallint NULL,
    [SH_TIMELINE] smallint NULL,
    [INITIALIZED] int NULL,
    CONSTRAINT [PK__TypePatient]
    PRIMARY KEY NONCLUSTERED ([ednum] ASC)
    WITH ( PAD_INDEX = OFF,
    FILLFACTOR = 80,
    IGNORE_DUP_KEY = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    DATA_COMPRESSION = NONE )
     ON [PRIMARY],
    CONSTRAINT [FK__TypePatient@BackgroundID__background]
    FOREIGN KEY ([BackgroundID])
    REFERENCES [dbo].[background] ( [ID] )
    )
    ON [PRIMARY]
    WITH (DATA_COMPRESSION = NONE);
    GO
    ALTER TABLE [dbo].[TypePatient] SET (LOCK_ESCALATION = TABLE);
    GO

    I will also need to run a similiar query on other tables. The query I posted is only an excerpt of a much longer query. I think I posted enough to show what I need.
    Wednesday, July 17, 2013 3:14 PM
  • You need to enclose each set between parenthesis, otherwise you will be appling a diff logic.

    ...
    where
        (
        (:DBLookupComboBox1 Is Null) or (BL.ChargeName = :DBLookupComboBox1)
        )
        And
        (
        (:DBLookupComboBox2 Is Null) or (TT.Dr_ID = :DBLookupComboBox2)
        );


    AMB

    Some guidelines for posting questions...


    Wednesday, July 17, 2013 4:57 PM
  • I still get "Invalid argument" with the paranthesis. I've been told that Is Null should still work with a numeric column. I'm using Fast Reports. It might be using a value to designate no selection. If that is the case, it must be a different no selection value than the alphanumeric column in the other combobox. The alphanumeric is apparently null.

    I'm still figuring out Fast Reports and am trying to view the value of the combobox with no selection. It's using pascal which I am also not familiar with. I didn't pick it. 

    For now I'll just initialize to zero and check for that in the query.

    Thanks to all that tried to help.

    Wednesday, July 17, 2013 7:21 PM
  • Hi DavidWIII,

    First, I want to inform that this forum is to discuss SQL Server related question and we have little knowledge on C# codes (or VB.NET codes), if you have concern about how to pass data from C# codes (or VB.NET codes) to SQL Server, we can post the question to Windows Form forum. Now ,let’s focus on the SQL Server question. According to the error message, this issue can occur if “:DBLookupComboBox2” is an invalid parameter, how do you get this parameter? Please debug your codes and make sure it is valid. Additionally, I made an example about how to check whether an int data or a varchar data is null with ISNULL function, you can refer to them:

    --ISNULL ( check_expression , replacement_value )
    /*
    If the parameter is null, then replace it with another value
    */
    declare @i int;
    select ISNULL(@i,10);
    -- Result
    -- 10
    declare @vc varchar(10);
    select ISNULL(@vc,'Not Null');
    -- Result
    -- Not Null


    Allen Li
    TechNet Community Support

    • Marked as answer by DavidWIII Friday, July 19, 2013 8:00 PM
    Thursday, July 18, 2013 2:05 AM
  • I wish I was using C# or VB.Net. The problem has turned out to be an issue not for this forum. I marked the replies as helpful because they helped me narrow it down.

    Thank you.

    Friday, July 19, 2013 7:56 PM