Is SET command possible to Query Manager? or any other way to make this?

[Former Member]

Hi folks,

I just want to ask if there's a possible way of setting a value for a particular variable in query manager of SAP Business One?

It goes like this....

Here's the code:

-

---

Select T0.Itemcode, T0.Itemname

,SUM(Case when T2.listnum=2 Then Case when T0.lastpurprc is NULL or T0.lastpurprc = 0 then '0' else Cast(case when T1.currency <>'PHP' then T1.PriceT3.Rate else T1.[Price]end/Case when T0.lastpurcur<>'PHP' then T0.lastpurprcT3.Rate Else T0.lastpurprc end as Float (6))end else '0' end ) Branch

,SUM(Case when T2.listnum=3 Then Case when T0.lastpurprc is NULL or T0.lastpurprc = 0 then '0' else Cast(case when T1.currency <>'PHP' then T1.PriceT3.Rate else T1.[Price]end/Case when T0.lastpurcur<>'PHP' then T0.lastpurprcT3.Rate Else T0.lastpurprc end as Float (6)) end else '0' end ) Dealer

,SUM(Case when T2.listnum=4 Then Case when T0.lastpurprc is NULL or T0.lastpurprc = 0 then '0' else Cast(case when T1.currency <>'PHP' then T1.PriceT3.Rate else T1.[Price]end/Case when T0.lastpurcur<>'PHP' then T0.lastpurprcT3.Rate Else T0.lastpurprc end as Float (6)) end else '0' end) Retail

,SUM(Case when T2.listnum=5 Then Case when T0.lastpurprc is NULL or T0.lastpurprc = 0 then '0' else Cast(case when T1.currency <>'PHP' then T1.PriceT3.Rate else T1.[Price]end/Case when T0.lastpurcur<>'PHP' then T0.lastpurprcT3.Rate Else T0.lastpurprc end as Float (6)) end else '0' end ) Service

,SUM(Case when T2.listnum=6 Then Case when T0.lastpurprc is NULL or T0.lastpurprc = 0 then '0' else Cast(case when T1.currency <>'PHP' then T1.PriceT3.Rate else T1.[Price]end/Case when T0.lastpurcur<>'PHP' then T0.lastpurprcT3.Rate Else T0.lastpurprc end as Float (6)) end else '0' end) CarDealer

FROM [dbo].[OITM] T0

left JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode

left JOIN OPLN T2 ON T1.PriceList = T2.ListNum

Left JOIN ORTT T3 ON T0.lastpurcur = T3.Currency

where T2.listnum IN (2,3,4,5,6)

GROUP BY T0.Itemcode, T0.Itemname

-

---

This one is running and doesn't show any errors at all, but the problem is I need to set a value for the "T3.Rate" where in the query must return the rate of the current date in table T3. The returned value will now be multiplied to the TO.lastpurprc and T1.Price

in mysql I made it by using the commane SET but it's another query which I ran before this one.... and it goes like this

SET @n:=(select rate from ORTT where ratedate=GETDATE());

and after that, I used "@n" for T3.Rate to have a value...

can you incorporate this to SAP B1?

I'm just a beginner... thanks and more power!

Edited by: fringe on May 26, 2011 10:58 AM

[Former Member]

If your question is answered, please close your thread and mark it as answered.

Go through [rules of engagement|http://wiki.sdn.sap.com/wiki/display/HOME/RulesofEngagement] if you haven't.

Thanks,

Gordon

[former_member204969]

You could use the set in a query, but in your case probably the solution is refining the join condition to get the appropriate rate:

Left JOIN ORTT T3 ON T0.lastpurcur = T3.Currency and datediff(d,T3.RateDate ,getdate())=0

[Former Member]

Thanks István Körös....

[Former Member]

Hi fringe,

Yes, you can use the SET command in Query Manager.

Kind regards

Carin

[Former Member]

Thanks Carin 😃