You’re offline. This is a read only version of the page.
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 :
Share
Reportwhile 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,
One way is to add while select on TableInvoice matching with custAccount and inside the loop add select statement to find firstonly record for CustTrans.
You can check this article, I think fetching single record in child table is not straight forward in select statement.
Hi Mohit,
Yes, for TableInvoice, I only need InvoiceId and CustAccount, then I would join to CustTrans based on these two information.
The two information are correctly selected if I checked in debug window (local variable TableInvoice), but CustTrans all is null, while in the 1st query without the use of grouping, give me the correct record as well.
Thanks
Hi, When you group by on fields in select statement, you can retrieve those fields only. If you tell us your requirement then someone can help you better.
#1
André Arnaud de Cal...
291,996
Super User 2025 Season 1
#2
Martin Dráb
230,853
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (
