ATOUTFOX
COMMUNAUTÉ FRANCOPHONE DES PROFESSIONNELS FOXPRO
Visual FoxPro : le développement durable

Forum AtoutFox : Re: Bases SQL et multi-threading   

Sujet

rss Flux RSS des derniers messages

Vous devez vous identifier pour pouvoir poser une question ou répondre.

sam. 09 janvier 2016, 11h24

FoxInCloud (Th. Nivelet)
France France

atoutfox.public.association

Re: Bases SQL et multi-threading

Dans la requête ci-dessous, quelqu'un peut-t-il donner quelques exemples de clauses et/ou fonctions incompatibles avec SQLserver ?

eg.

Nvl()
Space()
Empty
... ?


create SQL view (m.thisView) as;
;
select;
UH.*;
, Cast(allTrim(carrier.name) + Iif(Alltrim(carrier.Code) $ carrier.name, '', ' - ' + carrier.Code) as C(50)) as carrier;
, Nvl(logoWeb.logoWeb, Space(50)) AS logo;
from (;
select;
UH.uniqueID;
, Cast(Upper(Alltrim(UH.invoice)) as C(20)) AS invoice; && normalization
, UH.code;
, Cast(Upper(Alltrim(UH.acct_num)) as C(20)) as acct_num; && normalization
, UH.invDate;
, UH.inv_amtDue + UH.inv_amtAdj as inv_amt; && displayed in grid
, UH.inv_amtDue; && displayed in details form
, UH.inv_amtAdj; && displayed in details form && , UH.inv_void; && displayed in details form
, UH.shipper;
, UH.shpAddress;
, UH.consignee;
, UH.conAddress;
, UH.refnum;
, UH.dispute;
, UH.approve;
, UH.pay;
, UH.shipDate;
, UH.payDate;
, UH.payRef;
, UH.payAmount;
, UH.payNotes;
, UH.payer_code;
, UH.glc1;
, UH.glc2;
, UH.glc3;
, UH.glc4;
, UH.glc5;
, UH.glc6;
, UH.glc7;
, UH.glc8;
, UH.GLcode;
, UH.GLcodeMemo;
, UH.status;
, UH.statusDate;
, UH.inv_status;
, UH.terms;
, UH.priority;
, UH.userIDpay;
, UH.excludePay;
, UH.divisionID;
, division.division; && for grid
, UH.company; && ?
, UH.carrierID;
, UH.GLcodeID;
, UH.inv_stat; && added to optimize WebTemp
, UH.invDueDate; && added to optimize WebTemp
, UH.payer; && this.TableCont_41_UploadsHistory_42() corrects this data: replace payer with cPayer(UH.payer_code)
, UH.audit_status; && thn added 2014-09-08
, UH.audit_date; && thn added 2014-09-08
, UH.userIDaudt; && thn added 2014-09-08
, Space(1) AS invoice_so;
, .F. as lNote; && invoice requires a note
from ip!UploadsHistory UH;
join ip!division on UH.divisionID = division.divisionID;
join (;
Select; && divisionIDs, carrierIDs, GLcodeIDs of invoices that current user can access given his/her rights
division_.divisionID;
, carrier_.carrierID;
, division_.GLcodeID;
from (;
; && ======= DIVISIONS =======
select; && DIVISIONS: all companies or divisions && non-carrier user, && 2014-10-07 carrier users treated like regular users
division.divisionID;
, GLcode.GLcodeID;
from ip!Company;
join ip!division on company.companyID = division.companyID;
join ip!GLcode on division.divisionID = GLcode.divisionID;
where company.companyID = ?m.companyID;
and (.F.;
or .T.;
and exists(;
select UR.userID; && all companies...
from ip!UR;
where UR.userID = ?m.userID;
and UR.lAllComp;
);
and !exists(; && ... without exception
select URdiv.URdivID;
from ip!URdiv;
join ip!UR on URdiv.URID = UR.URID;
where UR.userID = ?m.userID;
and UR.companyID = ?m.companyID;
);
or exists(;
select UR.userID; && all divisions (of current company)
from ip!UR;
where UR.userID = ?m.userID;
and UR.companyID = ?m.companyID;
and UR.lAllDiv;
));
union;
select; && DIVISIONS: rights set explicitely && non-carrier user, && 2014-10-07: carrier users treated like regular users
URdiv.divisionID;
, GLcode.GLcodeID;
from ip!Users;
join ip!UR on Users.userID = UR.userID;
join ip!URdiv on UR.URID = URdiv.URID;
join ip!division on URdiv.divisionID = division.divisionID;
join ip!GLcode on division.divisionID = GLcode.divisionID;
where Users.userID = ?m.userID;
and UR.companyID = ?m.companyID; && and Empty(Users.carrierID); && 2014-10-07 carrier users treated like regular users
union;
select; && DIVISIONS: carrier user && deprecated 2014-10-07: carrier users treated like regular users
division.divisionID;
, GLcode.GLcodeID;
from ip!Users;
join ip!carrier on users.carrierID = carrier.carrierID;
join ip!comp_carr on carrier.carrierID = comp_carr.carrierID;
join ip!company on comp_carr.companyID = company.companyID;
join ip!division on company.companyID = division.companyID;
join ip!GLcode on division.divisionID = GLcode.divisionID;
where .F.; && 2014-10-07 carrier users treated like regular users
and Users.userID = ?m.userID;
and !Empty(Users.carrierID);
and company.companyID = ?m.companyID;
group by 1,2;
;
) division_;
join (;
; && ======= CARRIERS =======
select; && CARRIERS: non-carrier user, all carriers
carrier.carrierID;
, GLcode.GLcodeID;
from ip!company;
join ip!comp_carr on company.companyID = comp_carr.companyID;
join ip!carrier on comp_carr.carrierID = carrier.carrierID;
join ip!GLcode on carrier.carrierID = GLcode.carrierID;
where company.companyID = ?m.companyID;
and (.F.;
or .T.;
and exists(;
select UR.userID; && all companies...
from ip!UR;
where UR.userID = ?m.userID;
and UR.lAllComp;
);
and !exists(; && ... without exception
select URcar.URcarID;
from ip!URcar;
join ip!UR on URcar.URID = UR.URID;
where UR.userID = ?m.userID;
and UR.companyID = ?m.companyID;
);
or exists(;
select UR.userID; && all carriers (serving current company)
from ip!UR;
where UR.userID = ?m.userID;
and UR.companyID = ?m.companyID;
and UR.lAllCar;
));
union;
select; && CARRIERS: non-carrier user, allowed carriers
URcar.carrierID;
, GLcode.GLcodeID;
from ip!Users;
join ip!UR on Users.userID = UR.userID;
join ip!URcar on UR.URID = URcar.URID;
join ip!carrier on URcar.carrierID = carrier.carrierID;
join ip!GLcode on carrier.carrierID = GLcode.carrierID;
where Users.userID = ?m.userID;
and UR.companyID = ?m.companyID;
union;
select; && CARRIERS: carrier user, only his carrier
carrier.carrierID;
, GLcode.GLcodeID;
from ip!Users;
join ip!carrier on Users.carrierID = carrier.carrierID;
join ip!GLcode on carrier.carrierID = GLcode.carrierID;
where Users.userID = ?m.userID;
group by 1,2;
;
) carrier_;
on division_.GLcodeID = carrier_.GLcodeID;
where division_.GLcodeID in (;
; && ======= GLcodeS =======
select distinct; && GLcodeS: all accounts && non-carrier user, && 2014-10-07 carrier users treated like regular users
GLcode.GLcodeID;
from ip!company;
join ip!division on company.companyID = division.companyID;
join ip!GLcode on division.divisionID = GLcode.divisionID;
where company.companyID = ?m.companyID;
and (.F.;
or .T.;
and exists(;
select UR.userID; && all companies...
from ip!UR;
where UR.userID = ?m.userID;
and UR.lAllComp;
);
and !exists(;
select URacc.URaccID; && ... without exception
from ip!URacc;
join ip!UR on URacc.URID = UR.URID;
where UR.userID = ?m.userID;
and UR.companyID = ?m.companyID;
);
or exists(;
select UR.userID; && all accounts (of current company)
from ip!UR;
where UR.userID = ?m.userID;
and UR.companyID = ?m.companyID;
and UR.lAllAcc;
));
);
or division_.GLcodeID in (;
select distinct; && GLcodeS: allowed accounts && non-carrier user, && 2014-10-07 carrier users treated like regular users
GLcode.GLcodeID;
from ip!Users;
join ip!UR on Users.userID = UR.userID;
join ip!URacc on UR.URID = URacc.URID;
join ip!GLcode on URacc.AcctNum = Upper(GLcode.AcctNum); && GLcode: index on Upper(AcctNum) tag AcctNum
where UR.userID = ?m.userID;
and UR.companyID = ?m.companyID; && and Empty(Users.carrierID); && 2014-10-07 carrier users treated like regular users
);
or division_.GLcodeID in (;
select distinct; && GLcodeS: carrier user && deprecated 2014-10-07: carrier users treated like regular users
GLcode.GLcodeID;
from ip!GLcode;
join ip!carrier on GLcode.carrierID = carrier.carrierID;
join ip!Users on carrier.carrierID = users.carrierID;
join ip!comp_carr on carrier.carrierID = comp_carr.carrierID;
join ip!company on comp_carr.companyID = company.companyID;
join ip!division on GLcode.divisionID = division.divisionID;
join ip!company company_ on division.companyID = company_.companyID;
where .F.; && 2014-10-07 carrier users treated like regular users
and Users.userID = ?m.userID;
and !Empty(Users.carrierID);
and company.companyID = ?m.companyID;
and company_.companyID = ?m.companyID;
);
group by 1,2,3;
) div_car_gl on UH.GLcodeID = div_car_gl.GLcodeID and UH.divisionID = div_car_gl.divisionID and UH.carrierID = div_car_gl.carrierID;
where UH.companyID = ?m.companyID;
and !Empty(UH.INVOICE); && Invoice validity condition
and !Empty(UH.ACCT_NUM); && Invoice validity condition
and !Empty(UH.PAYER_CODE); && Invoice validity condition
and !Empty(UH.CODE); && Invoice validity condition
and !Empty(UH.invDate); && Invoice validity condition
and !Empty(UH.INV_STATUS); && Invoice validity condition
and !Empty(UH.statusDate); && Invoice validity condition
and UH.invDate < Gomonth(Date(), 1); && Invoice validity condition
and (UH.inv_status # 'IP' or IsNull(?m.InvDatePost) or UH.invDate > ?m.InvDatePost); && parameter && m.openOnly or
; && and !Empty(GLcode) - removed 9/25/13 on client request
; && and !Empty(Trim(GLcodeMemo)) - removed 9/25/13 on client request
; && and statusDate <= Date() - removed 9/25/13 on client request
) UH;
join ip!carrier on UH.carrierID = carrier.carrierID;
left join logoWeb on carrier.code = logoWeb.code; && no 'ip!' because logoWeb is a cursor - ip!logoWeb is a fake table, just for the sake of defining this view




Permalink : http://www.atoutfox.org/nntp.asp?ID=0000017149
20 286 messages dans le forum • Liste complète des messages

www.atoutfox.org - Site de la Communauté Francophone des Professionnels FoxPro - v3.4.0 - © 2004-2026.
Cette page est générée par un composant COM+ développé en Visual FoxPro 9.0-SP2-HF3