none
Where clause error RRS feed

  • Question

  • SELECT  
    TRK_NUM,
    DT,
    DEL_DOC_NUM ,
    IST_WR_DT,
    IST_STORE_CD,
    IST_SEQ_NUM,
    RESULT_CD,
    STOP#,
    SEQ#,
    EST_ARRV_TIME,
    ACT_ARRV_TIME,
    CONF_CD,
    ORIG_EST_ARRV_TIME,
    PREV_EST_ARRV_TIME
    FROM [MFITRAIN]..INV.TRK_STOP
    --WHERE  IST_WR_DT BETWEEN '08-01-2003' AND '08-31-2003'
    UNION ALL 
    SELECT
     cast(A.TRUCKID as varchar)as TRK_NUM  ,
    B.DELIVERYDATE AS DT ,
    B.ORDERID AS DEL_DOC_NUM1,
    A.DATE AS IST_WR_DT,
    C.InventLocationId AS IST_STORE_CD,
    cast(B.SEQUENCE  as varchar)as IST_SEQ_NUM,
    CAST(B.STATUS AS VARCHAR) AS RESULT_CD, 
    'AX' as STOP#1,
    'AX' as SEQ#1,
    'AX' as EST_ARRV_TIME1,
    'AX' as ACT_ARRV_TIME1,
    'AX' as CONF_CD1,
    'AX' as ORIG_EST_ARRV_TIME1,
    'AX' AS PREV_EST_ARRV_TIME1
    
    FROM   IDBRPLMAX01.AX61.DBO.mfiDSDeliveryRunTable A     
    
    INNER JOIN IDBRPLMAX01.AX61.DBO.mfiDSDeliveryRunOrderTable  B ON B.RECID=A.RECID  
    INNER JOIN IDBRPLMAX01.AX61.DBO.mfiDSTruckTable C ON C.RECID= A.RECID

    With the above query i am getting an error

    Msg 8114, Level 16, State 5, Line 1


    Error converting data type varchar to numeric.

    Any ideas why? If i comment out where clause it works fine...


    FM

    Monday, March 26, 2012 5:27 PM

All replies

  • It means that your "IST_WR_DT" is numeric and NOT a date field.

    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Monday, March 26, 2012 5:28 PM
  • What is the data type of IST_WR_DT  ?

    Monday, March 26, 2012 5:29 PM
  • I suggest to use ISO constants for dates, e.g.

    '20030801' and '20030831'

    Now, this is not necessary your problem, it may be that your columns are not compatible in types. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 26, 2012 5:31 PM
    Moderator
  • What is the datatype of IST_WR_DT?  If it is a numeric datatype (int, decimal, float, etc), then you will get this error since that will force SQL to convert the string '08-01-2003' to a number, and, of course, it can't do that.

    Tom

    Monday, March 26, 2012 5:33 PM
  • Date type for IST_WR_DT

    DATE


    FM

    Monday, March 26, 2012 5:34 PM
  • Hi Tom,

    Date type for IST_WR_DT

    DATE


    FM

    Monday, March 26, 2012 5:36 PM
  • Try WHERE  IST_WR_DT BETWEEN '08/01/2003' AND '08/31/2003'
    Monday, March 26, 2012 5:37 PM
  • Check every field that is used in this query and make sure that both tables (both parts of the union) use the same types for each field.

    Also, apply my suggestions in regards to ISO format.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 26, 2012 5:37 PM
    Moderator
  • OldJeep,

    I still get the same error with your method.


    FM

    Monday, March 26, 2012 5:39 PM
  • what is the type of IDBRPLMAX01.AX61.DBO.mfiDSDeliveryRunTable.DATE  ?
    Monday, March 26, 2012 5:39 PM
  • Try explicit conversion to DATE using CAST.

    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Monday, March 26, 2012 5:39 PM
  • What about TRK_NUM from first statement and "cast(A.TRUCKID as varchar)as TRK_NUM ,". 

    Is TRK_NUM from first portion VARCHAR. Besides, When you convert to VARCHAR, please mention for how many Characters. VARCHAR(n).

    There are times that it truncating to 1 character. If you have 10 characters, it will convert it VARCHAR(1), if you do NOT mention the length.

    Please provide the schema for all the tables. This way, it would be like try & Error!!!


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    Monday, March 26, 2012 5:41 PM
  • Please post type for each of the field:

    SELECT  
    TRK_NUM,  -- ?type
    DT, -- Type
    DEL_DOC_NUM , --?type
    IST_WR_DT,
    IST_STORE_CD,
    IST_SEQ_NUM,
    RESULT_CD,
    STOP#,
    SEQ#,
    EST_ARRV_TIME, -- type -- is it time?
    ACT_ARRV_TIME, -- type?
    CONF_CD,
    ORIG_EST_ARRV_TIME,
    PREV_EST_ARRV_TIME
    FROM [MFITRAIN]..INV.TRK_STOP
    WHERE  IST_WR_DT BETWEEN '20030801' AND '20030831'
    UNION ALL 
    SELECT
     cast(A.TRUCKID as varchar)as TRK_NUM  , 
    B.DELIVERYDATE AS DT , -- ?type
    B.ORDERID AS DEL_DOC_NUM1,
    A.DATE AS IST_WR_DT,
    C.InventLocationId AS IST_STORE_CD,
    cast(B.SEQUENCE  as varchar)as IST_SEQ_NUM,
    CAST(B.STATUS AS VARCHAR) AS RESULT_CD, 
    'AX' as STOP#1,
    'AX' as SEQ#1,
    'AX' as EST_ARRV_TIME1,
    'AX' as ACT_ARRV_TIME1,
    'AX' as CONF_CD1,
    'AX' as ORIG_EST_ARRV_TIME1,
    'AX' AS PREV_EST_ARRV_TIME1
    
    FROM   IDBRPLMAX01.AX61.DBO.mfiDSDeliveryRunTable A     
    
    INNER JOIN IDBRPLMAX01.AX61.DBO.mfiDSDeliveryRunOrderTable  B ON B.RECID=A.RECID  
    INNER JOIN IDBRPLMAX01.AX61.DBO.mfiDSTruckTable C ON C.RECID= A.RECID

    Make sure that types of the fields match in both parts of the query.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 26, 2012 5:43 PM
    Moderator
  • Date type for IST_WR_DT

    DATE


    FM

    Then the next thing I would check is the datatypes of every matching column in both sides of your UNION.  Make sure that you don't have any columns where the column is a numeric type on one side and a varchar on the other.  I see that you apparently have some columns where that is true and you are doing a cast to varchar to take care of that, maybe you missed one?

    Note, when you do a cast to varchar, always include a length otherwise you get a default length of 30.  When you declare something (either a column or a variable) as a varchar and you don't specify a length, you get a default length of 1.  But it is best to specify the length you want, even if it is the default value.  It's better documentation for your code.

    The other thing to check is that A.REC_ID, B.REC_ID, and C.REC_ID are all the same datatype.

    Tom


    • Edited by Tom Cooper Monday, March 26, 2012 6:17 PM Corrected/Expanded use of length when declaring or casting/converting to varchar
    Monday, March 26, 2012 5:47 PM
  • SELECT  
    TRK_NUM,  -- ?VCHR(5)
    DT, -- date
    DEL_DOC_NUM , --?VCHR(14)
    IST_WR_DT,
    IST_STORE_CD,
    IST_SEQ_NUM,
    RESULT_CD,
    STOP#,
    SEQ#,
    EST_ARRV_TIME, -- type -- NUM(5)
    ACT_ARRV_TIME, -- type?---NUM(5)
    CONF_CD,
    ORIG_EST_ARRV_TIME,
    PREV_EST_ARRV_TIME
    FROM [MFITRAIN]..INV.TRK_STOP
    WHERE  IST_WR_DT BETWEEN '20030801' AND '20030831'
    UNION ALL 
    SELECT
     cast(A.TRUCKID as varchar)as TRK_NUM  , 
    B.DELIVERYDATE AS DT , -- ?DATETIME  
    B.ORDERID AS DEL_DOC_NUM1,
    A.DATE AS IST_WR_DT,
    C.InventLocationId AS IST_STORE_CD,
    cast(B.SEQUENCE  as varchar)as IST_SEQ_NUM,
    CAST(B.STATUS AS VARCHAR) AS RESULT_CD, 
    'AX' as STOP#1,
    'AX' as SEQ#1,
    'AX' as EST_ARRV_TIME1,
    'AX' as ACT_ARRV_TIME1,
    'AX' as CONF_CD1,
    'AX' as ORIG_EST_ARRV_TIME1,
    'AX' AS PREV_EST_ARRV_TIME1
    
    FROM   IDBRPLMAX01.AX61.DBO.mfiDSDeliveryRunTable A     
    
    INNER JOIN IDBRPLMAX01.AX61.DBO.mfiDSDeliveryRunOrderTable  B ON B.RECID=A.RECID  
    INNER JOIN IDBRPLMAX01.AX61.DBO.mfiDSTruckTable C ON C.RECID= A.RECID
    HERE WE GO NAOMI

    FM

    Monday, March 26, 2012 5:49 PM
  • Tom,

    For CAST/CONVERT the default value is 30. I agree that we need to always specify number of chars when casting.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 26, 2012 5:49 PM
    Moderator
  • SELECT  
    TRK_NUM,  -- ?VCHR(5)
    DT, -- date
    DEL_DOC_NUM , --?VCHR(14)
    IST_WR_DT,
    IST_STORE_CD,
    IST_SEQ_NUM,
    RESULT_CD,
    STOP#,
    SEQ#,
    EST_ARRV_TIME, -- type -- NUM(5)
    ACT_ARRV_TIME, -- type?---NUM(5)
    CONF_CD,
    ORIG_EST_ARRV_TIME,
    PREV_EST_ARRV_TIME
    FROM [MFITRAIN]..INV.TRK_STOP
    WHERE  IST_WR_DT BETWEEN '20030801' AND '20030831'
    UNION ALL 
    SELECT
     cast(A.TRUCKID as varchar)as TRK_NUM  , 
    B.DELIVERYDATE AS DT , -- ?DATETIME  
    B.ORDERID AS DEL_DOC_NUM1,
    A.DATE AS IST_WR_DT,
    C.InventLocationId AS IST_STORE_CD,
    cast(B.SEQUENCE  as varchar)as IST_SEQ_NUM,
    CAST(B.STATUS AS VARCHAR) AS RESULT_CD, 
    'AX' as STOP#1,
    'AX' as SEQ#1,
    'AX' as EST_ARRV_TIME1,
    'AX' as ACT_ARRV_TIME1,
    'AX' as CONF_CD1,
    'AX' as ORIG_EST_ARRV_TIME1,
    'AX' AS PREV_EST_ARRV_TIME1
    
    FROM   IDBRPLMAX01.AX61.DBO.mfiDSDeliveryRunTable A     
    
    INNER JOIN IDBRPLMAX01.AX61.DBO.mfiDSDeliveryRunOrderTable  B ON B.RECID=A.RECID  
    INNER JOIN IDBRPLMAX01.AX61.DBO.mfiDSTruckTable C ON C.RECID= A.RECID
    HERE WE GO NAOMI

    FM

    Problem is 

    'AX' as EST_ARRV_TIME1,
    'AX' as ACT_ARRV_TIME1,

    You have them as NUMEIRC (5) in the top portion and in the second statement as CHAR.


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Monday, March 26, 2012 5:53 PM
  • Make sure that these fields are also CHAR or VARCHAR:

    STOP#,
    SEQ#,

    CONF_CD,

    ORIG_EST_ARRV_TIME,
    PREV_EST_ARRV_TIME


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Monday, March 26, 2012 5:56 PM
  • In this case, the error is clear:

    EST_ARRV_TIME, -- type -- NUM(5) ACT_ARRV_TIME, -- type?---NUM(5)

    and later you use characters for these 2 columns.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 26, 2012 5:58 PM
    Moderator
  • Tom,

    For CAST/CONVERT the default value is 30. I agree that we need to always specify number of chars when casting.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    You are correct.  I changed the reply to correct it.  Thanks.

    Tom

    • Marked as answer by Farhan1 Monday, March 26, 2012 7:57 PM
    • Unmarked as answer by Naomi NModerator Monday, March 26, 2012 8:11 PM
    Monday, March 26, 2012 6:18 PM
  • Any progress?

    Kalman Toth SQL SERVER & BI TRAINING

    Sunday, April 1, 2012 8:57 PM
    Moderator