Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I want to Use two fields from two tables in one load/resident script for calculate 'PuWt_Status' field.
please help me in Attached Script.
Try this:
If(sum(AMOUNT) = round(sum(if(SlipType = 4,total))*0.05) , 'OK' , 'NOK') AS PuWt_Status,
José Mª Tos wrote:
Try this:
If(sum(AMOUNT) = round(sum(if(SlipType = 4,total))*0.05) , 'OK' , 'NOK') AS PuWt_Status,
TNX for Code
But Not Work Bucause Show Error: <AMOUNT> Field Not Found!
My problem is:
Load <Amount> feild(from Table2) in one LOAD script with <SlipType> Field(from Table1) .
Please Read Attached File OR This CODE:
//==========================SELECT==========================
test:
sql select
slipnr,
docode,
total,
servicecode,
dateslip,
servicename,
bostatus,
SlipType
from "UOD".dbo."V_140_mmtrans" where servicecode in ('NP1001','SS3991','ZS1111') and bostatus != 3;
test2:
sql select TRANSNR,
Status,
LineExp,
DOCODE as docode,
shamsidate,
MiladiDate As dateslip,
AMOUNT,
ARPDesc
from V_140_ARPTrans where transnr like 'WT%' and ArpCode = 'S.IRN.0043';
sql select Shamsi_Date as LastUpdateDate,
Miladi_Date as convMiladi,
convert(varchar,getdate(),108) as LastUpdateTime
from V_S_ShamsiDate where Miladi_Date = convert(varchar,getdate(),102);
//===========================LOAD===========================
LOAD docode,
sum(if(SlipType = 9,total)) As Sales_invoice,
sum(if(SlipType = 4,total)) As Purchase_invoice,
//PLEASE HELP FOR CaCULATE THIS LINE:
If(sum(AMOUNT) = round(sum(if(SlipType = 4,total))*0.05) , 'OK' , 'NOK') AS PuWt_Status,
if(Round(sum(if(SlipType = 9,total))/2)= sum(if(SlipType = 4,total)),'OK','NOK') As SaPu_Status
resident test
group by docode;
//====================================================================
Message was edited by: ramin006
Message was edited by: ramin006
You could use a Lookup() function but it would be very slow. More of a concern is your data model, however, and this needs sorting before you can effectively solve this. Is it intentional to have two common fields between the two tables (docode and dateslip)?
it is intentional to have two common fields between the two tables
I think must create new view in sql server with two tables and use new view in my script!
But i know this solution is not good