Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can you not just put the code you already have into a view and call that instead?
flipside
Yes,would be much easy so, but the result is not good. So I have to change the formula and I'm trying to find out what I have there first and what data from each table brings that script.
Olivia
Hi Olivia,
You could try just adding the preceding LOAD command in front of your SQL statement ...
LOAD <list of fields>;
SQL SELECT <your sql statement>;
... or if you are looking to break up the command you could rewrite using left joins such as this example ...
VAclienti:
LOAD
BranchOfficeCode,
BranchOfficeName,
Position,
BranchOfficeCode & Position as SalesAccrualKey;
SQL SELECT BranchOfficeCode, BranchOfficeName, Position FROM dbo.VAclienti
left join
LOAD
Branch & Position as SalesAccrualKey,
SalesAccruals;
SQL SELECT Branch, Position, ISNULL(SUM(Amount), 0) as SalesAccruals FROM dbo.VAclientiAccruals
group by Branch, Position;
If you leave out the left join, you will get two tables linked by the SalesAccrualKey, which is another option.
Hope this helps
flipside
Hello again,
I am trying to make the first sum in first select statement and it gives me an error ("OLEDB read failed").
I don't see why...
VAclienti:
LOAD
BranchOfficeCode,
BranchOfficeName,
Position,
BranchOfficeCode & Position as SalesAccrualKey,
BusinessArea,
SAPpartnerCode,
VG,
Relation,
RevenueType,
Sales,
CostCenter,
Position,
BookingMonth,
SAPPartnerName,
left(Position,2) as Month,
left(BookingMonth,4) as Year;
sql select
BranchOfficeCode,
BranchOfficeName,
Position,
BusinessArea,
SAPpartnerCode,
VG,
Relation,
RevenueType,
CostCenter,
Position,
BookingMonth,
SAPPartnerName,
ISNULL(SUM(AmountLocalCurrency),0) as Sales
FROM dbo.VAclienti
group by BranchOfficeCode, BranchOfficeName, Position, BusinessArea, SAPpartnerCode, VG, Relation, RevenueType, CostCenter, BookingMonth, SAPPartnerName;
"("OLEDB read failed " are hard to debug.
Try commenting fields from LOAD and check if they work
Hey, 10x!
I've just realised where the problem was... I had 2 fields with the same name... ...
Have a nice day!
"Position" field, isn´t it?
Yes!
Now I have other question: how do I transpose the next script, where I have a sum of two select statements:
(
SELECT ISNULL(SUM(AmountLocalCurrency), 0) AS Expr1
FROM dbo.VAfurnizori AS F
WHERE (C.BranchOfficeName = BranchOfficeName) AND (C.Position = Position) AND (C.BookingMonth = BookingMonth)
)
+
(
SELECT ISNULL(SUM(CostAllocation), 0) AS Expr1
FROM dbo.VAinterface2 AS V2
WHERE (C.BranchOfficeCode = Branch) AND (C.Position = Position) AND (C.BookingMonth = BookingMonth)
)
AS Cost