Need help with some SQL

[guest2013-1]

In Dynamics GP, you have a table "Budget Master" and then you have the "General Ledger Transactions" table.

Previously I selected from the GLT and left joined the BM. But then our FD moaned about budgets not showing correctly on the report. I saw that if there's no GLT for that BM period, it won't show. So I made the BM the primary to select from where the GLT joins on to IF there's a transaction.

However, now the actuals are ****ed up (even though the budget is fine)

ACTINDX is the culprit here I think, the GLT ACTINDX doesn't exist in the BM, but you can also have BM's that doesn't exist for a GLT (one can be applied to the other / other doesn't have to exist)

How the hell do I do a join/select out all the data. Because if I look at BM, I throw away GLT's, if I look at GLT's, I throw away BM's

Simple example:

select * from BM
left outer join GLT on BM.YEAR1 = GLT.YEAR1
	AND GLT.PERIODID = BM.PERIODID
	AND GLT.ACTINDX = BM.ACTINDX
	AND BM.ACCATNUM = GLT.ACCATNUM

ACTINDX 44426 exists in GLT (which has to come through) but it doesn't exist in BM

I think ACTINDX is the culprit here that throws away the records, but I'm not sure if I can just ignore that column when joining in Dynamic GP

 

[cerebus]

Why are you doing an outer and not an inner join? You want matched conditions here right?
.
.
.
.
.
.
Spoken from pure ignorance mind you

 

[ABCpt]

Maybe try a Union between BM and GLT.

 

[guest2013-1]

Maybe try a Union between BM and GLT.

Will have to look at this, was thinking it, but I'm also thinking of just ripping the two apart and having QlikView handle the joins

Why are you doing an outer and not an inner join? You want matched conditions here right?
.
.
.
.
.
.
Spoken from pure ignorance mind you

Naw see, I'll have a budget setup for an account, but that doesn't mean there was a transaction applied on that account that has the budget. So if i did an inner join, we won't see the budget for that account, because there's no transaction for that account

 

[Cam]

Hey, try a FULL OUTER JOIN. That should effectively merge the tables.

 

[acidman1]

I have found the APPLY functions usefull in cases like this.
http://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

 

[guest2013-1]

good lord was this a toughie

I eventually split the 3 things (balance, period movement and budget) into 3 separate views, full outer joined with one of those dynamic table thingies you can do in sql (bunch of isnull checks inside on common fields) and then join the other tables on that one derived table.

bob should be my uncle

 

[zippy]

Larry Ellison deserves his yacht purely for sensible sql syntax. ANSI joins so early in the morning hurt I am spoiled by Oracle

 

[xrapidx]

good lord was this a toughie

I eventually split the 3 things (balance, period movement and budget) into 3 separate views, full outer joined with one of those dynamic table thingies you can do in sql (bunch of isnull checks inside on common fields) and then join the other tables on that one derived table.

bob should be my uncle

use coalesce for the common fields
e.g.

SELECT COALESCE(table_a.ACC_NR, table_B.ACC_NR, 'None') AS ACC_NR,
etc....
FROM table_a
FULL OUTER JOIN table_b

 

[guest2013-1]

use coalesce for the common fields
e.g.

SELECT COALESCE(table_a.ACC_NR, table_B.ACC_NR, 'None') AS ACC_NR,
etc....
FROM table_a
FULL OUTER JOIN table_b

That's what I did initially but I dumbed it down in case someone else needs to take over my work while im on vacation or whatever

 

[cerebus]

I'm really pretty chuffed that I can actually read and make sense of the SQL in this thread. If it stumped the likes of AcidRazor then it must be bona fide.

 

[guest2013-1]

I'm really pretty chuffed that I can actually read and make sense of the SQL in this thread. If it stumped the likes of AcidRazor then it must be bona fide.

Hey! in my own defense, it only stumped me for a few hours

I reworked almost the entire sql last week and the FD still moaned about "funny data". So I asked him for examples and worked from there to fix the issue. Now the issue is fixed, there's still "funny data", but this is what they put in there I'm at the Garbage in/Garbage out stage, lol.

Plus I have NO clue about period movements/income statements/opening balances etc.