Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Unanswered: Query works in QMF, not Microsoft Query

    Hi all - hope you can help.
    I have a query that returns an error....
    SQL0206N "BENS.BEN_NEW_REVENUE_AMT" is not valid in the context where it is used. SQLSTATE=42703

    when run in Microsoft Query but works fine in QMF for windows.

    Unfortunately I need it to run in the former. Any ideas how I can correct it? Thanks all for the help


    SELECT
    P.PROJECT_ID,
    P.PROJECT_TITLE,
    BENS.BEN_NEW_REVENUE_AMT
    FROM
    PPODPOD.PROJECT P
    LEFT JOIN
    (
    SELECT
    PB.PROJECT_ID,
    MAX(CASE
    WHEN PB.BENEFIT_TYPE_ID = 1 THEN PB.AMT
    ELSE NULLIF(0.00,0.00)
    END) AS BEN_NEW_REVENUE_AMT
    FROM
    PPODPOD.PROJECT_BENEFIT PB
    GROUP BY
    PB.PROJECT_ID
    ) BENS
    ON
    P.PROJECT_ID = BENS.PROJECT_ID
    ORDER BY P.PROJECT_ID ASC
    WITH UR

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up

    Quote Originally Posted by Redbear View Post
    Hi all - hope you can help.
    I have a query that returns an error....
    SQL0206N "BENS.BEN_NEW_REVENUE_AMT" is not valid in the context where it is used. SQLSTATE=42703

    when run in Microsoft Query but works fine in QMF for windows.

    Unfortunately I need it to run in the former. Any ideas how I can correct it? Thanks all for the help


    SELECT
    P.PROJECT_ID,
    P.PROJECT_TITLE,
    BENS.BEN_NEW_REVENUE_AMT
    FROM
    PPODPOD.PROJECT P
    LEFT JOIN
    (
    SELECT
    PB.PROJECT_ID,
    MAX(CASE
    WHEN PB.BENEFIT_TYPE_ID = 1 THEN PB.AMT
    ELSE NULLIF(0.00,0.00)
    END) AS BEN_NEW_REVENUE_AMT
    FROM
    PPODPOD.PROJECT_BENEFIT PB
    GROUP BY
    PB.PROJECT_ID
    ) BENS
    ON
    P.PROJECT_ID = BENS.PROJECT_ID
    ORDER BY P.PROJECT_ID ASC
    WITH UR
    I don't see any mistakes in DB2 part.
    Has to work.

  3. #3
    Join Date
    Apr 2013
    Posts
    2

    Afraid not

    Has to work? Afraid it doesn't, hence my post here.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I agree with Lenny.

    But, Redbear,
    What do you mean by "Microsoft Query"?
    Is it a product name?
    Or, do you want to say "run the query from DB2 Microsoft (Windows) client provided by IBM"?
    Or, do you want to say "run the query from some other Microsoft (Windows) client"?
    Or, others?
    Last edited by tonkuma; 04-25-13 at 13:40. Reason: Add more concrete examples

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Microsoft Query is an unfortunately named product! It is part of Microsoft Office.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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