Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Posts
    12

    Exclamation Unanswered: In operator with parameter

    Hi I have a problem with the following query

    SQL := PARAMETERS [Which Currency do you require ?] Text, [What start date do you require ?] DateTime, [How many days forward ?] Short, [Which Department do you want ?] Text, [Which NOSTRO do you require ?] Text;
    SELECT DISTINCTROW TSY_CASHFLOWS.TCF_CASHFLOW_DATE, TSY_CASHFLOWS.TCF_PAID, TSY_CASHFLOWS.TCF_DEPT, TSY_CASHFLOWS.TCF_CURRENCY, TSY_CASHFLOWS.TCF_NOSTRO, CURRENCIES.MAJORCURRENCY, CURRENCIES.SORTORDER, TSY_CASHFLOWS.TCF_AMOUNT, TSY_CASHFLOWS.TCF_SUB_GROUP, TSY_COUNTERPARTIES.TCP_COUNTERPARTY_NAME, TSY_CASHFLOWS.TCF_START, TSY_CASHFLOWS.TCF_MATURITY, TSY_CASHFLOWS.TCF_RATE, TSY_CASHFLOWS.TCF_DETAILS, [What start date do you require ?] AS Expr1, ([What start date do you require ?]+[How many days forward ?]) AS Expr2, TSY_DEAL_TYPES.TDT_DEAL_TYPE, TSY_BALANCES.BLA_BALANCE AS BLA_BALANCEAMOUNT, TSY_DEAL_TYPES.INTERNAL_EXTERNAL
    FROM CURRENCIES INNER JOIN (TSY_DEAL_TYPES RIGHT JOIN ((TSY_COUNTERPARTIES RIGHT JOIN TSY_CASHFLOWS ON TSY_COUNTERPARTIES.TCP_counterparty_id = TSY_CASHFLOWS.TCP_COUNTERPARTY_ID) LEFT JOIN TSY_BALANCES ON (TSY_CASHFLOWS.TCF_CASHFLOW_DATE = TSY_BALANCES.BAL_DATE) AND (TSY_CASHFLOWS.TCF_CURRENCY = TSY_BALANCES.BAL_CURRENCY)) ON TSY_DEAL_TYPES.TDT_DEAL_TYPE_ID = TSY_CASHFLOWS.TDT_DEAL_TYPE_ID) ON CURRENCIES.CURRENCY = TSY_CASHFLOWS.TCF_CURRENCY
    WHERE (((TSY_CASHFLOWS.TCF_CASHFLOW_DATE)>=[What start date do you require ?] And (TSY_CASHFLOWS.TCF_CASHFLOW_DATE)<=([What start date do you require ?]+[How many days forward ?])) AND ((TSY_CASHFLOWS.TCF_DEPT) In ([Which Department do you want ?])) AND ((TSY_CASHFLOWS.TCF_CURRENCY) Like UCase(IIf(IsNull([Which Currency do you require ?]),"%",[Which Currency do you require ?]))) AND ((TSY_CASHFLOWS.TCF_NOSTRO) Like UCase(IIf(IsNull([Which NOSTRO do you require ?]),"%",[Which NOSTRO do you require ?]))))
    ORDER BY TSY_CASHFLOWS.TCF_CASHFLOW_DATE
    WITH OWNERACCESS OPTION;

    I know it looks like a real mess.
    This is the section which does not work
    (TSY_CASHFLOWS.TCF_DEPT) In ([Which Department do you want ?])
    if I type in a single value like LAM or FRM it is fine.
    But I would like the users type in something like LAM, FRM
    But this fails.
    I have tried "'LAM', 'FRM'" and 'LAM', 'FRM' but it still does not work.

    Please help.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try this phrase instead:

    AND InStr([Which Department do you want ?],[TSY_CASHFLOWS.TCF_DEPT]) > 0

    The instr function will return a 0 if it can't find [TSY_CASHFLOWS.TCF_DEPT] in [Which Department do you want ?], otherwise it will return a positive integer that will satisfy the >0 criteria.

    This won't work if some of your Department strings are subsets of other department strings, but maybe your data will support it. Otherwise, you will have to dynamically create your sql statement from the parameters you are supplied.

    blindman

  3. #3
    Join Date
    Apr 2003
    Posts
    12

    In operator with parameter.

    Originally posted by blindman
    Try this phrase instead:

    AND InStr([Which Department do you want ?],[TSY_CASHFLOWS.TCF_DEPT]) > 0

    The instr function will return a 0 if it can't find [TSY_CASHFLOWS.TCF_DEPT] in [Which Department do you want ?], otherwise it will return a positive integer that will satisfy the >0 criteria.

    This won't work if some of your Department strings are subsets of other department strings, but maybe your data will support it. Otherwise, you will have to dynamically create your sql statement from the parameters you are supplied.

    blindman
    Thanks for all the help. It fixed my problem.

    Many 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
  •