Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    61

    Unanswered: Whats wrong with my code

    I am able to run this query in access but when I copy it to SQL 7.0 I get all sorts of error messages:

    SELECT dbo_INCIDENTS.CASENUM, Min(dbo_INCIDENTS.INCNUM) AS OIncNum, Max(IIf([PRIMARY_CASE_REC]='Yes',[INCNUM])) AS PIncNum, Count(dbo_INCIDENTS.INCNUM) AS NumLogs
    FROM dbo_INCIDENTS
    GROUP BY dbo_INCIDENTS.CASENUM
    HAVING (((dbo_INCIDENTS.CASENUM) Is Not Null Or (dbo_INCIDENTS.CASENUM)<>''))
    ORDER BY dbo_INCIDENTS.CASENUM, Min(dbo_INCIDENTS.INCNUM);

    SQL 7.0 seems to have a problem with IIF Statement. Is there a SQL equivalent?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yup, it's CASE (which, by the way, is the sql standard)

    see CASE on Books On Line


    rudy

  3. #3
    Join Date
    Oct 2002
    Posts
    61

    Talking I figured it out!!!

    I re worked my code:

    SELECT INCIDENTS.CASENUM, INCIDENTS.PRIMARY_CASE_REC, INCIDENTS.INCNUM AS PIncNum, Min(INCIDENTS_1.INCNUM) AS OIncNum, Count(INCIDENTS.INCNUM) as NumLogs
    FROM INCIDENTS AS INCIDENTS_1 INNER JOIN INCIDENTS ON INCIDENTS_1.CASENUM = INCIDENTS.CASENUM
    GROUP BY INCIDENTS.CASENUM, INCIDENTS.PRIMARY_CASE_REC, INCIDENTS.INCNUM
    HAVING (((INCIDENTS.PRIMARY_CASE_REC)='Yes'))
    Order By INCIDENTS.CASENUM;

    It works fine. Thanks for the information Rudy!

    Thanks

Posting Permissions

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