Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: 0 rows, conversion error

    When I run the query between the asterisks it returns no rows. When I run it while wrapped in a bigger query and it is now the subquery, and comparing the "trailer_date" field to a date (and --1 and --2 are uncommented) SQL Server throws a character to datetime conversion error. There are, of course, values in the table that when pieced together like this would give such an error, but that is why I have the WHERE clause. There are NO rows returned in the subquery, how can adding the comparison make any difference?

    Also, you will notice the --1 and --2, when these are commented like they are in this post the query runs, with 0 rows returned. If they are uncommented and the larger query executed, I get the char to datetime error.

    I makes no sense to me. There must be something changing in the execution plan that is not evident in the query.


    select a.*
    from (
    --*********************
    SELECT CAST(SUBSTRING(t.trailer, 1,2) + '/' + SUBSTRING(t.trailer, 3,2) + '/2010 00:00:00' AS DATETIME) as "trailer_date"
    ,
    m.arrival_date as "Schedule Date", substring(t.trailer, 5,4) as "Time",
    SUBSTRING(t.trailer, 9,7) as "Pickup #", 'I' as "Type", '' as "Preload", m.probill as "Barilla #",
    --1 ISNULL(m.ib_oid, t.trailer_owner) as "Order #" ,
    --2 t.ckin_drvlic_num as "Consolidation ID",
    m.carrier as "Carrier",
    '' as "Customer", CAST(m.total_wgt as INT) as "Weight", x.cmt as "Notes",
    t.carrier as "Trailer/Wave ID" ,
    CAST(ISNULL(c.tag_count,'0') AS VARCHAR) as "Pallet Info",
    T.whse_id, '' as Pln
    FROM trailer_t t
    LEFT OUTER JOIN recvunit_t r ON r.trailer = t.trailer
    LEFT OUTER JOIN ibom_f m ON m.ib_oid = r.ib_oid
    LEFT OUTER JOIN (SELECT ib_oid ,MAX(cmt) as cmt FROM ibxo_f GROUP BY ib_oid) x ON m.ib_oid = x.ib_oid
    LEFT OUTER JOIN (SELECT d.ib_oid, CEILING(SUM(d.ord_qty / p.qty2in3)) as tag_count
    FROM ibod_f d (nolock), pm_f p (nolock)
    WHERE p.sku = d.sku
    GROUP BY d.ib_oid) c ON m.ib_oid = c.ib_oid
    WHERE SUBSTRING(t.loc, 1, 2) = 'IN'
    AND LEN(t.trailer) = 15
    AND NOT (SUBSTRING(t.trailer, LEN(t.trailer) - 1, 2) = 'AA' OR SUBSTRING(t.trailer, LEN(t.trailer) - 1, 2) = 'AB')
    AND NOT t.trailer like '%SAMPLE%'
    AND LEN(t.trailer) = 15
    --*************************
    ) a
    WHERE a.[date] between getdate() AND getdate() - 1

  2. #2
    Join Date
    Aug 2003
    Posts
    3
    To clarify:

    This runs with 0 rows returned:
    SELECT CAST(SUBSTRING(t.trailer, 1,2) + '/' + SUBSTRING(t.trailer, 3,2) + '/2010 00:00:00' AS DATETIME) as "Date"
    ,
    m.arrival_date as "Schedule Date", substring(t.trailer, 5,4) as "Time",
    SUBSTRING(t.trailer, 9,7) as "Pickup #", 'I' as "Type", '' as "Preload", m.probill as "Barilla #",
    ISNULL(m.ib_oid, t.trailer_owner) as "Order #" ,
    t.ckin_drvlic_num as "Consolidation ID",
    m.carrier as "Carrier",
    '' as "Customer", CAST(m.total_wgt as INT) as "Weight", x.cmt as "Notes",
    t.carrier as "Trailer/Wave ID" ,
    CAST(ISNULL(c.tag_count,'0') AS VARCHAR) as "Pallet Info",
    T.whse_id, '' as Pln
    FROM trailer_t t
    LEFT OUTER JOIN recvunit_t r ON r.trailer = t.trailer
    LEFT OUTER JOIN ibom_f m ON m.ib_oid = r.ib_oid
    LEFT OUTER JOIN (SELECT ib_oid ,MAX(cmt) as cmt FROM ibxo_f GROUP BY ib_oid) x ON m.ib_oid = x.ib_oid
    LEFT OUTER JOIN (SELECT d.ib_oid, CEILING(SUM(d.ord_qty / p.qty2in3)) as tag_count
    FROM ibod_f d (nolock), pm_f p (nolock)
    WHERE p.sku = d.sku
    GROUP BY d.ib_oid) c ON m.ib_oid = c.ib_oid
    WHERE SUBSTRING(t.loc, 1, 2) = 'IN'
    AND LEN(t.trailer) = 15
    AND NOT (SUBSTRING(t.trailer, LEN(t.trailer) - 1, 2) = 'AA' OR SUBSTRING(t.trailer, LEN(t.trailer) - 1, 2) = 'AB')
    AND NOT t.trailer like '%SAMPLE%'

    **********************************
    this gives char to datetime conversion error:
    *********************************

    select a.*
    from (
    --*********************
    SELECT CAST(SUBSTRING(t.trailer, 1,2) + '/' + SUBSTRING(t.trailer, 3,2) + '/2010 00:00:00' AS DATETIME) as "Date"
    ,
    m.arrival_date as "Schedule Date", substring(t.trailer, 5,4) as "Time",
    SUBSTRING(t.trailer, 9,7) as "Pickup #", 'I' as "Type", '' as "Preload", m.probill as "Barilla #",
    ISNULL(m.ib_oid, t.trailer_owner) as "Order #" ,
    t.ckin_drvlic_num as "Consolidation ID",
    m.carrier as "Carrier",
    '' as "Customer", CAST(m.total_wgt as INT) as "Weight", x.cmt as "Notes",
    t.carrier as "Trailer/Wave ID" ,
    CAST(ISNULL(c.tag_count,'0') AS VARCHAR) as "Pallet Info",
    T.whse_id, '' as Pln
    FROM trailer_t t
    LEFT OUTER JOIN recvunit_t r ON r.trailer = t.trailer
    LEFT OUTER JOIN ibom_f m ON m.ib_oid = r.ib_oid
    LEFT OUTER JOIN (SELECT ib_oid ,MAX(cmt) as cmt FROM ibxo_f GROUP BY ib_oid) x ON m.ib_oid = x.ib_oid
    LEFT OUTER JOIN (SELECT d.ib_oid, CEILING(SUM(d.ord_qty / p.qty2in3)) as tag_count
    FROM ibod_f d (nolock), pm_f p (nolock)
    WHERE p.sku = d.sku
    GROUP BY d.ib_oid) c ON m.ib_oid = c.ib_oid
    WHERE SUBSTRING(t.loc, 1, 2) = 'IN'
    AND LEN(t.trailer) = 15
    AND NOT (SUBSTRING(t.trailer, LEN(t.trailer) - 1, 2) = 'AA' OR SUBSTRING(t.trailer, LEN(t.trailer) - 1, 2) = 'AB')
    AND NOT t.trailer like '%SAMPLE%'
    --*************************
    ) a
    WHERE a.[date] between getdate() AND getdate() - 1


    *************************************
    commenting 2 lines in the original SELECT makes the big query run
    *************************************
    select a.*
    from (
    --*********************
    SELECT CAST(SUBSTRING(t.trailer, 1,2) + '/' + SUBSTRING(t.trailer, 3,2) + '/2010 00:00:00' AS DATETIME) as "Date"
    ,
    m.arrival_date as "Schedule Date", substring(t.trailer, 5,4) as "Time",
    SUBSTRING(t.trailer, 9,7) as "Pickup #", 'I' as "Type", '' as "Preload", m.probill as "Barilla #",
    ISNULL(m.ib_oid, t.trailer_owner) as "Order #" ,
    t.ckin_drvlic_num as "Consolidation ID",
    m.carrier as "Carrier",
    '' as "Customer", CAST(m.total_wgt as INT) as "Weight", x.cmt as "Notes",
    t.carrier as "Trailer/Wave ID" ,
    CAST(ISNULL(c.tag_count,'0') AS VARCHAR) as "Pallet Info",
    T.whse_id, '' as Pln
    FROM trailer_t t
    LEFT OUTER JOIN recvunit_t r ON r.trailer = t.trailer
    LEFT OUTER JOIN ibom_f m ON m.ib_oid = r.ib_oid
    LEFT OUTER JOIN (SELECT ib_oid ,MAX(cmt) as cmt FROM ibxo_f GROUP BY ib_oid) x ON m.ib_oid = x.ib_oid
    LEFT OUTER JOIN (SELECT d.ib_oid, CEILING(SUM(d.ord_qty / p.qty2in3)) as tag_count
    FROM ibod_f d (nolock), pm_f p (nolock)
    WHERE p.sku = d.sku
    GROUP BY d.ib_oid) c ON m.ib_oid = c.ib_oid
    WHERE SUBSTRING(t.loc, 1, 2) = 'IN'
    AND LEN(t.trailer) = 15
    AND NOT (SUBSTRING(t.trailer, LEN(t.trailer) - 1, 2) = 'AA' OR SUBSTRING(t.trailer, LEN(t.trailer) - 1, 2) = 'AB')
    AND NOT t.trailer like '%SAMPLE%'
    --*************************
    ) a
    WHERE a.[date] between getdate() AND getdate() - 1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •