Declare FMS query not working

[MukeshSingh]

Hi,

Declare @PayType as NVarchar(15)

select  @PayType='cashAcct'

  Begin

  select distinct t0.U_exptype, t0.U_glcode, t0.u_gldesc from [@GLD] t0 inner join [@OGLG] t1 on t0.docentry=t1.docentry where t1.u_paytype='Cash' and t1.u_cbcode=$[OVPM.cashAcct] and t0.u_outpay='Y'

If @PayType='TrsfrAcct'

  select distinct t0.U_exptype, t0.U_glcode, t0.u_gldesc from [@GLD] t0 inner join [@OGLG] t1 on t0.docentry=t1.docentry where t1.u_paytype='Transfer' and t1.u_cbcode=$[OVPM.TrsfrAcct] and t0.u_outpay='Y'

  End

My first FMS query is working fine but second condition not working.

Please suggest where is the mistake.

Regards,

Mukesh Singh

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query.

Declare @PayType as NVarchar(15)

select  @PayType='cashAcct'

  Begin

  select distinct t0.U_exptype, t0.U_glcode, t0.u_gldesc from [Dbo].[@GLD] t0 inner join [dbo].[@OGLG] t1 on t0.docentry=t1.docentry where t1.u_paytype='Cash' and t1.u_cbcode=$[OVPM.cashAcct] and t0.u_outpay='Y'

If @PayType='TrsfrAcct'

  select distinct t0.U_exptype, t0.U_glcode, t0.u_gldesc from [dbo].[@GLD] t0 inner join [dbo].[@OGLG] t1 on t0.docentry=t1.docentry where t1.u_paytype='Transfer' and t1.u_cbcode=$[OVPM.TrsfrAcct] and t0.u_outpay='Y'

  End

[Johan_H · Accepted solution]

Hi Nagarajan,

This looks like an exact copy of the query Mukesh posted originally. Could you please highlight the difference ?

Regards,

Johan

[MukeshSingh · Accepted solution]

Hi,

Thanks for your suggestion.

We done FMS different way, please find the FMS query.

if $[OVPM.cashAcct] !=0

select distinct

t0.U_exptype, t0.U_glcode, t0.u_gldesc from [@GLD] t0

inner join [@OGLG] t1 on t0.docentry=t1.docentry

where  t0.u_outpay='Y' and t1.u_cbcode=$[OVPM.CashAcct] AND t1.u_paytype='Cash'

else if $[OVPM.cashAcct] =0

select distinct

t0.U_exptype, t0.U_glcode, t0.u_gldesc from [@GLD] t0

inner join [@OGLG] t1 on t0.docentry=t1.docentry

where  t0.u_outpay='Y' and t1.u_cbcode=$[OVPM.TrsfrAcct] AND t1.u_paytype='Transfer'

Regards,

Mukesh Singh

[Johan_H · Accepted solution]

Hi Mukesh,

Ok that query makes more sense. Does it work as expected now ?

Regards,

Johan

[MukeshSingh · Accepted solution]

Yes our problem solve thanks for your effort and advise.

[kothandaraman_nagarajan · Accepted solution]

Hi Johan,

Just added [dbo] at UDT table in above query.

Thank you.

[kothandaraman_nagarajan]

Hi Mukesh,

Could you explain problem with your original query and answered query?

Thank you.

[Johan_H]

Hi Mukesh,

In your first query you set @PayType, in the second query you do not set @PayType.

Regards,

Johan

[MukeshSingh]

Hi Johan,

Can you correct my query bcz i try but result are same.

Regards,

Mukesh

[Johan_H]

Hi Mukesh,

I cannot correct it for you, because I do not know what it is supposed to do, but I will try to help.

Declare @PayType as NVarchar(15)
select  @PayType='cashAcct'
  Begin
  select distinct t0.U_exptype, t0.U_glcode, t0.u_gldesc from [@GLD] t0 inner join [@OGLG] t1 on t0.docentry=t1.docentry where t1.u_paytype='Cash' and t1.u_cbcode=$[OVPM.cashAcct] and t0.u_outpay='Y'
If @PayType='TrsfrAcct'
  select distinct t0.U_exptype, t0.U_glcode, t0.u_gldesc from [@GLD] t0 inner join [@OGLG] t1 on t0.docentry=t1.docentry where t1.u_paytype='Transfer' and t1.u_cbcode=$[OVPM.TrsfrAcct] and t0.u_outpay='Y'
  End

At the top you set @PayType to 'cashAcct'.

Then in the red part you check if the value of @PayType is 'TrsfrAcct', but the value of @PayType is never changed anywhere.

Are you sure that this is the entire query ?

Regards,

Johan

[MukeshSingh]

Hi Johan,

Thanks for your response, basically we created our  table @OGLG header, @GLD row  and we define "On account" type entry ledger should be select bases on payment means define ledger in our table.

Example - We created our table @OGLG we define payment means type cash / Bank Transfer and also define pament means wise GL code

Entry system

We want to select payment means first cash / bank transfer any one after that when press shift+F2 under gl account that time based on payment means selection gl will be shown.

I hope you understand my question.

Regards,

Mukesh

[Johan_H]

Hi Mukesh,

Ok, it looks like there is a part missing from the query:

Here you set the payment type to 'cash:'

Declare @PayType as NVarchar(15)
select  @PayType='cashAcct'

From here it looks like something is missing, because next you do not  check what the payment type is, you just run the query.

  Begin
  select distinct t0.U_exptype, t0.U_glcode, t0.u_gldesc from [@GLD] t0 inner join [@OGLG] t1 on t0.docentry=t1.docentry where t1.u_paytype='Cash' and t1.u_cbcode=$[OVPM.cashAcct] and t0.u_outpay='Y'

In this query the payment type is not changed in any way, so it is still 'cash'.

Then you check if the payment type is 'bank transfer':

If @PayType='TrsfrAcct'
  select distinct t0.U_exptype, t0.U_glcode, t0.u_gldesc from [@GLD] t0 inner join [@OGLG] t1 on t0.docentry=t1.docentry where t1.u_paytype='Transfer' and t1.u_cbcode=$[OVPM.TrsfrAcct] and t0.u_outpay='Y'
  End

But as the payment method is never changed, this query will never be executed.

Next, an FMS is supposed to return one value, but your queries both return three values.

Mukesh Singh wrote:

...My first FMS query is working fine but second condition not working...

Which query is that ?

Regards,

Johan