Parameter Query Between Dates

[lloydmav]

Hi,

I'm converting a couple of queries that currently have Between Date Criteria.

The first query worked perfectly however my pass through query won't work when I add in the parameter instead of the date. Any ideas?

Before (Works Fine)

            WHERE
                S.SLOT_DIS_LYR_TYP = 'S'
                AND (AI.AI_REMV_DT BETWEEN '30/JUN/07' AND '30/JUN/08')
                AND AI.AI_DEL_FLG <> 'Y'
                AND DIS.EQP_CDE = 'MOLE'

After: This one is giving the error Missing Expression

            WHERE
                S.SLOT_DIS_LYR_TYP = 'S'
                AND (AI.AI_REMV_DT BETWEEN [start date] AND [end date])
                AND AI.AI_DEL_FLG <> 'Y'
                AND DIS.EQP_CDE = 'MOLE'

 

[DCrake]

Design you query and view in sql mode then enter the following above the SELECT

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT .....
WHERE
                S.SLOT_DIS_LYR_TYP = 'S'
                AND (AI.AI_REMV_DT BETWEEN [Start Date] AND [End Date])
                AND AI.AI_DEL_FLG <> 'Y'
                AND DIS.EQP_CDE = 'MOLE'

David

 

[lloydmav]

Its telling me invalid sql statement when I use that. Here is the full query

PARAMETERS [Start Date] DateTime, [End Date] DateTime;

SELECT 
    AI2.STRUC_ID,
    AI2.A_LYR_TYP, 
    AI2.A_ID, 
    AI2.A_SEQ_NO, 
    AI2.AI_INSTL_DT, 
    AI2.AI_REMV_DT, 
    T1.AI_INSTL_DT "NEW ASSET INSTALL DATE",
    T1.AI_REMV_DT "NEW ASSET REMOVE DATE",
    AI2.AI_EMP_NO,
    AI2.REGION
FROM
    (
        (
            SELECT S.SLOT_DIS_PIKID "STRUC_ID", AI.*, D.*
            FROM
                (FMS.ASSET_INSTLN AI INNER JOIN FMS.SLOT S ON AI.SLOT_DIS_LYR_TYP = S.SLOT_DIS_LYR_TYP AND AI.SLOT_DIS_PIKID = S.SLOT_DIS_PIKID)
                INNER JOIN N017.DISTRICT@DD01.WOORLD D ON D.SLOT_DISB_KEY = S.SLOT_DISB_KEY
            WHERE
                S.SLOT_DIS_LYR_TYP = 'S'
                AND (AI.AI_REMV_DT BETWEEN [Start Date] AND [End Date])
                AND AI.AI_DEL_FLG <> 'Y'
                AND S.DIS_EQP_CDE = 'MOLE'
        ) AI2
        INNER JOIN FMS.ASSET_INSTLN T1 
            ON T1.A_LYR_TYP = AI2.A_LYR_TYP
            AND T1.A_ID = AI2.A_ID
            AND T1.A_SEQ_NO = AI2.A_SEQ_NO + 1
    ) 
WHERE
    T1.AI_INSTL_DT BETWEEN [Start Date] AND [End Date]
    AND T1.AI_DEL_FLG <> 'Y'

 

[pbaldy]

Pass through to what? If it's a pass through query to SQL Server, it wouldn't know how to evaluate the bracketed criteria.