locked
Error when using where statement RRS feed

  • Question

  • I am receiving the error

    'An expression of non-boolean type specified in a context where a condition is expected, near 'nlmastm'.'

    When I try and run

    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    cnl.scheme.nlmastm nlmastm
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    jcl.scheme.nlmastm nlmastm
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    fnl.scheme.nlmastm nlmastm
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    CNG.scheme.nlmastm nlmastm
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    BRL.scheme.nlmastm nlmastm
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    CNP.scheme.nlmastm nlmastm
    WHERE cnl.scheme.nlmastm nlmastm<>"99-9999-99-9999" & jcl.scheme.nlmastm nlmastm <>"99-9999-99-9999" & Fnl.scheme.nlmastm nlmastm <>"99-9999-99-9999" & cng.scheme.nlmastm nlmastm <>"99-9999-99-9999" & BRL.scheme.nlmastm nlmastm <>"99-9999-99-9999" & cnp.scheme.nlmastm nlmastm <>"99-9999-99-9999" 
    Any ideas?

    Monday, July 18, 2016 12:39 PM

Answers

  • nlmastm.nominal_code,nlmastm.description
    FROM
    cnl
    .scheme.nlmastm nlmastm
    WHERE
    cnl
    .scheme.nlmastm<>'99-9999-99-9999'

    Once again - when you define an alias for a table, you must use that alias to reference columns from that table. You define the alias nlmastm, so you must use that alias in the where clause.  You did this correctly in the column list. I'll point out that cnl.schema.nlmastm is a table name, not a column name. So the comparison to the literal isn't allowed even if you had not defined the alias.
    • Marked as answer by JaK82 Monday, July 18, 2016 2:40 PM
    Monday, July 18, 2016 2:12 PM

All replies

  • SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    CNP.scheme.nlmastm nlmastm
    WHERE cnl.scheme.nlmastm nlmastm<>"99-9999-99-9999" & jcl.scheme.nlmastm nlmastm <>"99-9999-99-9999" & Fnl.scheme.nlmastm nlmastm <>"99-9999-99-9999" & cng.scheme.nlmastm nlmastm <>"99-9999-99-9999" & BRL.scheme.nlmastm nlmastm <>"99-9999-99-9999" & cnp.scheme.nlmastm nlmastm <>"99-9999-99-9999"

    Once you define an alias for a table, you must use that alias to reference the columns within it; you cannot define an alias in a where clause. Copy and paste will not remove what is valid syntax for a column in the select list but which is not valid in the where clause.  And it appears that you are attempting to define a where clause that applies to all of the rows in the union.  You can't do that with this approach - it would be better to add the appropriate where clauses to each of the individual statements in the union to "encourage" the optimizer. 

    And you should NOT use the double quote character as a string delimiter; this usage requires a specific connection setting that can become a problem with certain features. 

    And lastly, the ampersand is not a substitute for AND.  The ampersand is a bit-wise operator.  Of course, I assume that you are running a query against a sql server database, so perhaps your db engine is OK with these.

    Monday, July 18, 2016 1:52 PM
  • Thanks Scott,

    I changed it to 

    nlmastm.nominal_code, nlmastm.description
    FROM
    cnl.scheme.nlmastm nlmastm
    WHERE
    cnl.scheme.nlmastm<>'99-9999-99-9999'
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    jcl.scheme.nlmastm nlmastm
    WHERE
    jcl.scheme.nlmastm<>'99-9999-99-9999'
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    fnl.scheme.nlmastm nlmastm
    WHERE
    fnl.scheme.nlmastm<>'99-9999-99-9999'
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    cng.scheme.nlmastm nlmastm
    WHERE
    cng.scheme.nlmastm<>'99-9999-99-9999'
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    brl.scheme.nlmastm nlmastm
    WHERE
    brl.scheme.nlmastm<>'99-9999-99-9999'
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    cnp.scheme.nlmastm nlmastm
    WHERE
    cnp.scheme.nlmastm<>'99-9999-99-9999'

    and the error i get now is it 

    The multi-part identifier "cnp.scheme.nlmastm" could not be bound.
    The multi-part identifier "brl.scheme.nlmastm" could not be bound.
    The multi-part identifier "cng.scheme.nlmastm" could not be bound.
    The multi-part identifier "fnl.scheme.nlmastm" could not be bound.
    The multi-part identifier "jcl.scheme.nlmastm" could not be bound.
    The multi-part identifier "cnl.scheme.nlmastm" could not be bound.

    Thanks

    Monday, July 18, 2016 1:57 PM
  • nlmastm.nominal_code,nlmastm.description
    FROM
    cnl
    .scheme.nlmastm nlmastm
    WHERE
    cnl
    .scheme.nlmastm<>'99-9999-99-9999'

    Once again - when you define an alias for a table, you must use that alias to reference columns from that table. You define the alias nlmastm, so you must use that alias in the where clause.  You did this correctly in the column list. I'll point out that cnl.schema.nlmastm is a table name, not a column name. So the comparison to the literal isn't allowed even if you had not defined the alias.
    • Marked as answer by JaK82 Monday, July 18, 2016 2:40 PM
    Monday, July 18, 2016 2:12 PM
  • Thanks Scott,

    Ive changed it to the below,  I am still receiving could not be bound errors

    SELECT nlmastm.nominal_code, nlmastm.description
    FROM
    cnl.scheme.nlmastm nlmastm
    WHERE
     (nltmastm.nominal_code<>'99-9999-99-9999') 
    
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    jcl.scheme.nlmastm nlmastm
    WHERE
     (nltmastm.nominal_code<>'99-9999-99-9999') 
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    fnl.scheme.nlmastm nlmastm
    WHERE
     (nltmastm.nominal_code<>'99-9999-99-9999') 
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    cng.scheme.nlmastm nlmastm
    WHERE (nltmastm.nominal_code<>'99-9999-99-9999') 
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    brl.scheme.nlmastm nlmastm
    WHERE
     (nltmastm.nominal_code<>'99-9999-99-9999') 
    
    UNION ALL
    
    SELECT
    nlmastm.nominal_code, nlmastm.description
    FROM
    cnp.scheme.nlmastm nlmastm
    WHERE
     (nltmastm.nominal_code<>'99-9999-99-9999') 


    Monday, July 18, 2016 2:26 PM
  • Thanks was a typo
    Monday, July 18, 2016 2:40 PM