Skip to Content

Select case with query

Hello experts.

i'm in sales offer > table OQUT.

It is possible to have a FMS that select a query depending the OQUT.cardcode??

E.G.

if cardcode is A then run query A

else cardcode is B then run query B

i try this query :

SELECT CASE  $[OQUT.cardcode]
when '+MASTERFD'  Then

select a.itemcode, a.price, c.itmsgrpnam
from
	itm1 a
	inner join oitm b on a.itemcode = b.itemcode
	inner join oitb c on b.itmsgrpcod = c.itmsgrpcod
where
a.pricelist = (select listnum from ocrd where cardcode = $[OQUT.CardCode]) and
c.itmsgrpnam = $[OQUT.U_JDT_OFF_MER] and
(a.price <> 0 and a.price is not null)
for browse

else

select a.itemcode, c.itemname, a.price, a.fromdate, a.todate, d.itmsgrpnam, a.linenum
from spp1 a inner join
	(select cardcode, itemcode, max(todate) as ultimo from spp1 group by cardcode, itemcode) b
	on a.cardcode = b.cardcode and a.itemcode = b.itemcode and a.todate = b.ultimo
inner join oitm c on a.itemcode = c.itemcode
inner join oitb d on c.itmsgrpcod = d.itmsgrpcod
where a.cardcode = $[$4.0]

end

Than's in advence...

And sorry for my english....

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jul 22, 2009 at 10:19 AM

    Hi Gianluca...........

    Offcourse you can fire such kind of FMS by preparing Case When statement..........

    Regards,

    Rahul

    Add a comment
    10|10000 characters needed characters exceeded

    • Gianluca Calloni István KÅ‘rös

      Hi Istvan...

      The query run in this mode:

      If  $[OQUT.cardcode]='+MASTERFD' 
       
      select a.itemcode, a.price, c.itmsgrpnam
      from
      	itm1 a
      	inner join oitm b on a.itemcode = b.itemcode
      	inner join oitb c on b.itmsgrpcod = c.itmsgrpcod
      where
      a.pricelist = (select listnum from ocrd where cardcode = $[OQUT.CardCode]) and
      c.itmsgrpnam = $[OQUT.U_JDT_OFF_MER] and
      (a.price != 0 and a.price is not null)
       
      else
       
      select a.itemcode, c.itemname, a.price, a.fromdate, a.todate, d.itmsgrpnam, a.linenum
      from spp1 a inner join
      	(select cardcode, itemcode, max(todate) as ultimo from spp1 group by cardcode, itemcode) b
      	on a.cardcode = b.cardcode and a.itemcode = b.itemcode and a.todate = b.ultimo
      inner join oitm c on a.itemcode = c.itemcode
      inner join oitb d on c.itmsgrpcod = d.itmsgrpcod
      where a.cardcode = $[OQUT.cardcode]]

      Thank you a lot...

  • author's profile photo Former Member
    Former Member
    Posted on Jul 22, 2009 at 12:40 PM

    Hi

    Quick way to do is to test it in your system .

    Create a UDF in header level and use this query as Formatted search ..set it up as when Cardcode changes ..

    If you are not getting the value ,then would be the next step to troubleshoot

    Thank you

    bishal

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.