BAQ using First/Last/Each


(Thomas Dejager) #1

When using E9 (prog) and working on creating a BAQ, there was an option to select a table and then specify if you want the records from First or Last or Each. How can do you accomplish this same task in E10 (Sql)?
Thank you


(Jose C Gomez) #2

Group By (generally) or Top(X) what’s the end goal


(Dan Edwards) #3

and an Order By clause


(Thomas Dejager) #4

with a customer AR invoice, which date is the last date a payment was received. In the past I was able to use CashDtl and instructed the table with Last instead of Each.


(Anya Chervinskaya) #5

You can also use subquery and do min/max calculation in there and a group by, then filter by that in the top query


(Jose Fernandez) #6

Hi All, i am trying to accomplish something similar to get some practice but cant seem to get it working on a simple BAQ. I want to show the first record of each customer contact.
I have a BAQ with customer and customer contact. Top level is the Customer and an inner subquery with customer contact. i am able to change the innersubquery to top 1 but the gives me all the customer rows but only contact information on the top row with the rest blank. What piece am i missing?

Thank you for your help!


(Brandon Anderson) #7

This post has a good way to do what I call a mini subquery, where you return one item for each row. It works well.


(Brandon Anderson) #8

The other way to do that, is to have the inner sub run all results, and then have a calculated field that gets a max or min of something, and group by all of the other fields in that query.


(Jose Fernandez) #9

Thanks Banderson. i will give this a shot!


(Jose Fernandez) #10

I am trying this method,only thing that has me cross is the single column comment. So for my child table, i can only display one column? i need to keep creating the child table with all the different columns i want?


(Ken Nash) #11

You don’t need to create a new query for each column.

Create a query that returns the top contact for each customer. CustNum and ConNum

Then use the results of that query to join to the contact table to get the rest of the contact record.


(Ken Nash) #12

edit to use sysrowid

This is was I was trying to say in my message.

select * from Erp.CustCnt inner join
(select CustNum, min(SysRowID) as SysRowID
from Erp.CustCnt
group by custnum) as contactlist on Erp.CustCnt.SysRowID = contactlist.SysRowID


(Jose Fernandez) #13

Thanks Ken. I been trying at this all day. Idk why this looks so hard in E10 compared to previous versions.

I see the query phrase (thank you for that). i have this query

select
[CustCnt].[Name] as [CustCnt_Name],
[CustCnt].[CustNum] as [CustCnt_CustNum],
[CustCnt].[ConNum] as [CustCnt_ConNum],
[CustCnt].[PhoneNum] as [CustCnt_PhoneNum]
from Erp.CustCnt as CustCnt

showing me all the contacts. Wheni try to use a cal field by creating a min field of the Connum i get Incorrect Syntax.

select
[CustCnt].[Name] as [CustCnt_Name],
[CustCnt].[CustNum] as [CustCnt_CustNum],
[CustCnt].[ConNum] as [CustCnt_ConNum],
[CustCnt].[PhoneNum] as [CustCnt_PhoneNum],
(min( CustCnt.ConNum )) as [Calculated_MIn]
from Erp.CustCnt as CustCnt
group by [CustCnt].[Name],
[CustCnt].[CustNum],
[CustCnt].[ConNum],
[CustCnt].[PhoneNum]


(Ken Nash) #14

You need to make a inner subquery first.

The min/max/sum you need to do a group by. That is why you are getting the syntax error.

Notice the GroupBy checkbox is checked.

This allows me to do a SUM


(Jose Fernandez) #15

Sorry but i am not following you. I created a query with Custcnt with a few columns. I then create a sub inner query with the same table Custcnt. I join the top level query with the inner query.

i then check off the group by connum field in the inner query display columns and create a cal field on the inner query with the min of the connum field?

Here are my screenshots , starting from scratch





with the phrase

select
[CustCnt].[Company] as [CustCnt_Company],
[CustCnt].[CustNum] as [CustCnt_CustNum],
[CustCnt].[ConNum] as [CustCnt_ConNum],
[CustCnt].[Name] as [CustCnt_Name],
[CustCnt].[PhoneNum] as [CustCnt_PhoneNum]
from Erp.CustCnt as CustCnt

Now i am going to create subquery of the same table CustCnt



Now i am going to join the queries


am i on the right track?

Thank you


(Ken Nash) #16

I usually create the subquery first. That way I can tell if I am going to be getting the correct data.

Check out this BAQ.

TestCustCnt.baq (21.0 KB)


(Jose Fernandez) #17

Thank you Ken! Reviewing it. Thanks for sending it and taking the time to show me!


(Jose Fernandez) #18

ken is it fair to say that whenever i want to use the First statement in a BAQ i will need to use the SysRowID field as my “filter”. I tried using the ConNum/CustNum and some other fields and never got a single row per customer. Thanks for all your help!

Maybe is because i am getting use to the new feature of 10 but seems like getting rid of the each, first, last statement made things more complicated than easier to do!

jose


(Brandon Anderson) #19

It’s just what you are used to. If I had to make a query in E-9 I would be totally confused, but that’s because I started with E-10. You’ll get the hang of it! We’ll help.


(Ken Nash) #20

I would not think that you would use the sysrowid each time. I was just using that field for this example.

There could be other fields that you might want to use. You have to make that decision by looking at the table first.