Results 1 to 5 of 5

Thread: suspect syntax

  1. #1
    Join Date
    Sep 2003
    Posts
    8

    Unanswered: suspect syntax

    Hi all,

    Trying to identify whether a field value (from a single row) in one table is matched in a column (all rows) in table 2.

    Only way I can think of is using something like:
    ((SUM(table1.fieldx = table2.fieldy))=1)

    ...but its (SQL Server) throwing a error msg (170).

    How should I be writing this? Or is there a better way?

    Thanks in advance.

    Casey.

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76
    not real sure what you are asking, not sure why you have the sum in there, that will not work.

    ((SUM(table1.fieldx = table2.fieldy))=1)


    write out what you are after, such as if x = y then sum the results from both fields, well then why do you have it equal to 1.

    Just explain better so I can help.

  3. #3
    Join Date
    Sep 2003
    Posts
    8
    Originally posted by mkkmg
    not real sure what you are asking, not sure why you have the sum in there, that will not work.

    ((SUM(table1.fieldx = table2.fieldy))=1)


    write out what you are after, such as if x = y then sum the results from both fields, well then why do you have it equal to 1.

    Just explain better so I can help.
    Thanks for your reply mkkmg, I appreciate your time.

    What I'm trying to achieve is a boolean test for a value in say 'table1 col1 row X' against 'multiple (all) rows in table 2 col1'.

    eg.
    ***********
    Table 1
    Col1
    3
    ***********
    Table 2
    Col1
    2
    4
    8
    5
    9
    3
    2
    ***********

    My thought was that I could you the sum function to return a value (0/1) based on whether there was a match, and use that in a where clause to exclude/include a given record.

    Hope this illustrates my problem, if its not painfully obvious already, I don't have much experience with tsql! (I have an uncomfortable feeling I've overlooked an obvious way to solve this problem )

    Thanks again for your time,

    Cheers Casey.

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140

    Re: suspect syntax

    Originally posted by kc_casey
    Hi all,

    Trying to identify whether a field value (from a single row) in one table is matched in a column (all rows) in table 2.

    Only way I can think of is using something like:
    ((SUM(table1.fieldx = table2.fieldy))=1)

    ...but its (SQL Server) throwing a error msg (170).

    How should I be writing this? Or is there a better way?

    Thanks in advance.

    Casey.
    THIS DOES NOT WORK, ERROR 170, INCORRECT SYNTAX
    select (5=5)

    BUT THIS MIGHT WORK
    select (case 5 when 5 then 1 else 0 end)

    IN YOUR QUERY
    ((SUM(CASE table1.fieldx WHEN table2.fieldy THEN 1 ELSE 0 END))=1)

  5. #5
    Join Date
    Sep 2003
    Posts
    8

    Re: suspect syntax

    Originally posted by cvandemaele
    THIS DOES NOT WORK, ERROR 170, INCORRECT SYNTAX
    select (5=5)

    BUT THIS MIGHT WORK
    select (case 5 when 5 then 1 else 0 end)

    IN YOUR QUERY
    ((SUM(CASE table1.fieldx WHEN table2.fieldy THEN 1 ELSE 0 END))=1)
    Thankyou very much for your help cvandemaele, your exact response didn't work, apparently a aggregate function in a CASE statement isn't permitted; however in a HAVING clause it is!

    FYI
    --Performs a true/false test for 'make'
    HAVING ((SUM(CASE tblStock.Make WHEN tblDealerMake.Make THEN 1 ELSE 0 END))=0)

    Thanks again, I hope your day/evening is an enjoyable one!

    Casey.

Posting Permissions

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