Hi guys,
Need some help on sql query in x , which I'm not sure why it is not working, but if I test it in SSMS it is fine.
First of all I have a custom table (lets say TableInvoice) which is to hold customer invoice. This will linked to table CustTrans.
When I'm using this query :
while select TableInvoice
where TableInvoice.CustAccount == "CustomerA"
join custTrans where custTrans.AccountNum == TableInvoice.CustAccount
&& custTrans.Invoice == TableInvoice.InvoiceId
The 2 table give me the right value.
But because in my TableInvoice could have multi row of same invoice with same customer while I only need one record, so I change it to this :
while select InvoiceId, CustAccount from TableInvoice group by InvoiceId, CustAccount
where TableInvoice.CustAccount == "CustomerA"
join AccountNum, Invoice, RecId from custTrans where custTrans.AccountNum == TableInvoice.CustAccount
&& custTrans.Invoice == TableInvoice.InvoiceId
So I adding grouping for the 1st table, my custom table "TableInvoice". In debug, I can see the record TableInvoice is correct, but CustTrans empty.
If I put this grouping query and join in SSMS, it is returning the correct record that I want, like this :
select InvoiceId, CustAccount from TABLEINVOICE a
join custTrans b on b.AccountNum = a.CustAccount and b.INVOICE = a.InvoiceId
where a.CustAccount = 'CustomerA'
group by a.InvoiceId, a.CustAccount
How to get the record CustTrans with the 2nd query (X query), because I need to add another join to CustTransOpen, but currently cannot since CustTrans empty.
Thanks,
