Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Syntax Error in query

    Hi:
    Can any one please help me in creating query. Criteria is that:

    If LstVldAccount="OtherAccount 1" then match tech_id.corp with Val(Left([LastValidTech].[OtherAcct1],5
    If LstVldAccount="OtherAccount 2" then match tech_id.corp with Val(Left([LastValidTech].[OtherAcct2],5
    If LstVldAccount="OtherAccount 3 then match tech_id.corp with Val(Left([LastValidTech].[OtherAcct3],5

    If LstVldAccount is empty and tbl_ValidDisputes.LstVldTech also dont match with tech_id.tech then it still show values from tbl_ValidDisputes

    I try to write it first but it gives syntax error:

    SELECT tbl_ValidDisputes.TicketNum, tbl_ValidDisputes.OtherAcct1, tbl_ValidDisputes.OtherAcct2,tbl_ValidDisputes.Lst VldAccount, tbl_ValidDisputes.OtherAcct3, tech_id.TECH, tech_id.TECHCONT, tech_id.CORP FROM tbl_ValidDisputes LEFT JOIN tech_id ON IIF(LstVldAccount="OtherAccount 1", Val(Left([LastValidTech].[OtherAcct1],5)), IIF(LstVldAccount = "OtherAccount 2", Val(Left([LastValidTech].[OtherAcct2],5)), IIF(LstVldAccount = "OtherAccount 3", Val(Left([LastValidTech].[OtherAcct3],5)), 0)))=tech_id.CORP and tbl_ValidDisputes.LstVldTech=tech_id.tech

    Thanks in advance.

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    You are trying to join a table called tbl_ValidDisputes with a table called tech_id. But in your criterion you introduce a field from a different table called LastValidTech. Perhaps LastValidTech should really be tbl_ValidDisputes ?

    Also, whats the bit at the end supposed to do "...AND tbl_ValidDisputes.LstVldTech=tech_id.tech" ? Is it supposed to deal with the case where LstVldAccount="" or LstVldAccount IS NULL ? In which case this too should be nested in you IIF expression.

    I wonder if your table design is appropriate ? I'm not entirely sure what you are trying to do but having three columns called OtherAccout 1-3 always raises an eyebrow.

    hth
    Chris

  3. #3
    Join Date
    Jul 2006
    Posts
    157
    Sorry this is a query where syntax error is:

    SELECT tbl_ValidDisputes.TicketNum, tbl_ValidDisputes.OtherAcct1,tbl_ValidDisputes.Oth erAcct2, tbl_ValidDisputes.OtherAcct3, tbl_ValidDisputes.LstVldAccount, tech_id.TECH, tech_id.TECHCONT, tech_id.CORP FROM tbl_ValidDisputes LEFT JOIN tech_id ON IIF (tbl_ValidDisputes.LstVldAccount="OtherAccount 1", Val(Left([tbl_ValidDisputes].[OtherAcct1],5), IIF(tbl_ValidDisputes.LstVldAccount = "OtherAccount 2", Val(Left([tbl_ValidDisputes].[OtherAcct2],5), IIF(tbl_ValidDisputes.LstVldAccount = "OtherAccount 3", Val(Left([tbl_ValidDisputes].[OtherAcct3],5), 0))) =tech_id.CORP and tbl_ValidDisputes.LstVldTech=tech_id.tech

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your query reads (in part):

    Code:
    ...LEFT JOIN tech_id ON 
    
    IIF 
      (
      tbl_ValidDisputes.LstVldAccount="OtherAccount 1", 
      Val(Left([tbl_ValidDisputes].[OtherAcct1],5), 
      IIF
        (
        tbl_ValidDisputes.LstVldAccount = "OtherAccount 2",
        Val(Left([tbl_ValidDisputes].[OtherAcct2],5), 
        IIF
          (
          tbl_ValidDisputes.LstVldAccount = "OtherAccount 3", 
          Val(Left([tbl_ValidDisputes].[OtherAcct3],5), 
          0
          )
        )
      ) 
    =tech_id.CORP...
    let's consider the case where
    tbl_ValidDisputes.LstVldAccount="OtherAccount 1"
    and
    [tbl_ValidDisputes].[OtherAcct1] = 1234567890


    your SQL now reads
    ...LEFT JOIN tech_id ON 12345 = tech_id.CORP...

    was that your plan?


    izy

    forgot to mention... your parenthesis are up the creek as well. three missing ) in the Val expressions
    Last edited by izyrider; 10-01-06 at 10:11.
    currently using SS 2008R2

  5. #5
    Join Date
    Jul 2006
    Posts
    157
    >your SQL now reads
    >...LEFT JOIN tech_id ON 12345 = tech_id.CORP...

    Yes this is the one i need

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    my joins look something like
    ...LEFT JOIN tbl1 ON tbl2.fieldX = tbl1.fieldY...

    if you are sure that 12345 = tbl1.fieldY is where you want to be, perhaps you could drop the JOIN idea and use
    WHERE tbl1.fieldY = iif(......)
    forget that - you NEED a valid JOIN since you have two tables in your query

    don't forget to close the Val(Left()) parentheses

    izy
    Last edited by izyrider; 10-01-06 at 11:19.
    currently using SS 2008R2

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Defo agree with Chris & Izy. Three fields in a table named Col1, Col2 and Col3 are usually an indication of an unnormalised design. This can cause all sorts of difficulties. The requirement to implement a conditional statement in a FROM clause is an excellent example of one such difficulty.

    Have a nose at the linked article. Although reading the whole thing would be a Very Good Thing in particular have a look at first normal form:
    http://r937.com/relational.html

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    mutish - I thought I reconised your handle and that I might have resonded to another question of yours so I had a search. I noticed this post of yours:
    http://www.dbforums.com/showthread.p...72#post6231272

    I wish I had seen this earlier - no the sytax of the query is wrong. As with the conditional statement in a FROM clause, this problem is caused by an unnormalised schema. There is (as there often is) a SQL kludge however it makes me think even more that you would benefit from reading the article I linked to.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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