By using this website, you agree to our Terms of Use (click here)
This is the basis of a bigger solution i am working on, but I need to get this working as its the core of that solution. I'm reasonably new to GI, but have a lot of SQL experience (mostly Oracle). My query works fine where there are rows found for each both accounts, but doesn't work when the right table has no data.
This was cloned to an F200 training database to submit here.Â
Now when I run this query, I get the following (correct results).
If I change the account 303000 to 304000 (which doesn't have data), no rows are returned at all. I would expect null values to be returned for fields from the right table as it is related via left join.Â
My Guess: When it returns NULL, it fails your conditions tab, so you don't get a row at all. Conditions don't accept NULL unless you specify the option. I would try toggling them off and on. You can use isnull() to get around those values or just add a line that checks for nullity.
I think you want to move your last condition into the join itself so that if it fails it won't kill your whole dataset, only the data from the table on the other side of the join.
My Guess: When it returns NULL, it fails your conditions tab, so you don't get a row at all. Conditions don't accept NULL unless you specify the option. I would try toggling them off and on. You can use isnull() to get around those values or just add a line that checks for nullity.
Had the same theory and did try that, but still no rows returned.
I think you want to move your last condition into the join itself so that if it fails it won't kill your whole dataset, only the data from the table on the other side of the join.
I had that theory and tried it previously, but i just found out that i made a mistake. For some reason, in the relations tab you have to use the internal Account ID value. In the Conditions tab, you can use the AccountCD within AccountID criteria and it works. I looked up the internal Account ID value for the test accounts and it worked in both cases (ie. rows or no rows on right table).Â
But now the issue is trying to join the GL.Account table to an already left Joined table without killing the query. If I used an inner join between my already left joined table and the GL.Account table, i get no rows. If I use one of the outer joins, I get too many rows. At the moment the only way it would work is to use the internal Account ID value in the query.
That's right. AccountID is a database-generated integer value that uniquely identifies the GL Account but that you don't see in the application. AccountCD is the actual GL Account number that you can see within Acumatica. You want to use AccountID in joins.
Do you need to join to Account twice, once from chgstockhist and once from stockhist? I can't see any reason why you wouldn't be able to do this as long as you use AccountID in the joins.
Both tables needed to join to Account because they look for different values AccountCD. I couldn't get it to work but i think i probably should have done:
stockhist inner account1
stockhist left chgstockhist
chgstockhist left account2
The logic being that the required account in chgstockhist may not have any data and if so, i still want the stockhist row. Can't remember if i tried this or not, but i wasn't able to get it working.
Anyway, I found a much better approach for the solution and so this query was abandoned. Thanks for your help 🙂