none
Access DB Pass through query not working RRS feed

  • Question

  • Hi,

    I created a pass through query in Access (please see below query #1) and I get a failure "Data Type "m" does not match a defined Type name." I'm creating a pass through because the Tetadata view that has this information has millions of rows and I just need a rolling three months. When I create a direct link query and then select query off of that, it works, (please see query #2 below) but it won't work if I create a pass through query.

    Could someone please let me know why it's failing? I'm 

    query #1

    SELECT  PlanCapacityId, PlantName, DeprecatedPlantName, PlanId,
      TimePeriodId, CapacityCode, SystemName, ModuleName, PETROCapacityDescription,
      RowrhsMin, RowrhsMax, Used, LimitCost, StartDateTime, EndDateTime,
      CaseName, BaseCase, NAME, PlanType, EffectiveStartDate, EffectiveEndDate
    FROM SCM_REFINERY_FUNCTIONAL_VW_P.vwLPPlanCapacityDetail
    where plantname = 'ESE' and startdatetime between (DateAdd("m",-3,Date())) And Date()))

    query #2

    SELECT [PETRO Capacity Detail].PlanCapacityId, [PETRO Capacity Detail].PlantName, [PETRO Capacity Detail].DeprecatedPlantName, [PETRO Capacity Detail].PlanId, [PETRO Capacity Detail].TimePeriodId, [PETRO Capacity Detail].CapacityCode, [PETRO Capacity Detail].SystemName, [PETRO Capacity Detail].ModuleName, [PETRO Capacity Detail].PETROCapacityDescription, [PETRO Capacity Detail].RowrhsMin, [PETRO Capacity Detail].RowrhsMax, [PETRO Capacity Detail].Used, [PETRO Capacity Detail].LimitCost, [PETRO Capacity Detail].StartDateTime, [PETRO Capacity Detail].EndDateTime, [PETRO Capacity Detail].CaseName, [PETRO Capacity Detail].BaseCase, [PETRO Capacity Detail].NAME, [PETRO Capacity Detail].PlanType, [PETRO Capacity Detail].EffectiveStartDate, [PETRO Capacity Detail].EffectiveEndDate
    FROM [PETRO Capacity Detail]
    GROUP BY [PETRO Capacity Detail].PlanCapacityId, [PETRO Capacity Detail].PlantName, [PETRO Capacity Detail].DeprecatedPlantName, [PETRO Capacity Detail].PlanId, [PETRO Capacity Detail].TimePeriodId, [PETRO Capacity Detail].CapacityCode, [PETRO Capacity Detail].SystemName, [PETRO Capacity Detail].ModuleName, [PETRO Capacity Detail].PETROCapacityDescription, [PETRO Capacity Detail].RowrhsMin, [PETRO Capacity Detail].RowrhsMax, [PETRO Capacity Detail].Used, [PETRO Capacity Detail].LimitCost, [PETRO Capacity Detail].StartDateTime, [PETRO Capacity Detail].EndDateTime, [PETRO Capacity Detail].CaseName, [PETRO Capacity Detail].BaseCase, [PETRO Capacity Detail].NAME, [PETRO Capacity Detail].PlanType, [PETRO Capacity Detail].EffectiveStartDate, [PETRO Capacity Detail].EffectiveEndDate
    HAVING ((([PETRO Capacity Detail].PlantName)="ESE") AND (([PETRO Capacity Detail].StartDateTime) Between (DateAdd("m",-3,Date())) And Date()))
    ORDER BY [PETRO Capacity Detail].StartDateTime DESC;

    Thursday, October 10, 2019 9:09 PM

All replies

  • Could someone please let me know why it's failing?

    Hi Inspirz,

    Quite a reduction from query #1 to query #2.

    I think the problem lies in NAME. In query #2 you write it as  [PETRO Capacity Detail].NAME. For the interpreter it is "clear" that it is the field NAME in table [PETRO Capacity Detail].

    But NAME is a reserved word (never to be used for other goals!), and in query #1 it can refer to many other things. Probably the interpreter decides that NAME is the form's name.

    The real reason for failure is probably too complex. In such cases mostly the arguments of used functions are marked as malefactor.

    You better replace "NAME" by e.g. "DetailName". A quick workaround could be to include the tablename before "NAME", as in query #2.

    Imb.

    Thursday, October 10, 2019 9:45 PM
  • My guess is that Teradata does not know how to handle the VBA functions DateAdd() and Date().  You'll need to use the Teradata equivalents if you want to use a passthrough query.  I think there is an ADD_MONTHS function in Teradata that might do what you want.

    -Bruce

    Thursday, October 10, 2019 10:12 PM
  • Hi Inspirz,

    Other than reserved names as mentioned by others, try removing the external brackets from.....like this

    startdatetime between DateAdd("m",-3,Date()) And Date()

    for query #1.

    hth


    • Edited by AccessVandal Monday, October 14, 2019 4:06 AM add
    Monday, October 14, 2019 4:05 AM
  • For a PT query, you must use the syntax of the database server.

    If that is SQL Server, the language is T-SQL, thus replace:

    startdatetime between DateAdd("m", -3, Date()) And Date()

    with:

    startdatetime Between DateAdd(month, -3, GetDate()) And GetDate()


    Gustav Brock

    Monday, October 14, 2019 7:53 AM