Query to show Highest Id linked to another record

Query to show Highest Id linked to another record

  
Hello Braniacs..! I've spent days trying to follow similar posts and transposing my tables/columns into the suggestions to return a list of distinct values, but none have worked... probably my limited knowledge, but I've finally given up and hoping someone can help me....!
I have a Table ' Property' that contains unique house addresses plus other data.
I have a second table 'PropertyTransactions' that contains all the financial data for each property with a link to the 'Property' reference (Id). I want a screen that shows the very last Transaction row per property as this contains the latest financial totals.

To be clear there could be hundreds of Property Transactions per Property - only the last one is valid and I want a screen that shows only one property per row.... all my testing shows many duplicates of each property.... I hope this makes sense.
I've attached my latest oml that has a non working Advanced Query in Screen  'PropertyTransaction_List'
 
  
Hello Gary,

I can see the problem since you Property Transactions is linked on the reference attribute, but that attribute is the same numer for each transaction records. I don't know if it's possible but I would say the easiest solution is to add a date time stamp attribute to the transactions table where you can do an sort on. Then you can select the top 1 from Property Transactions ordered desc on the date time stamp. But would be only possible for new transactions if your table is already filled you'll should make some kind of conversion for those records.

I didn't had much time to take a look but maybe somebody else got more time to watch it and have a solution for it as it is now.

Kind regards,
Evert

Hi Gary,

I couldn't open the OML because I am still on version 5 but here is my solution:

select TBL_A.*, TBL_B.*

from TBL_A

left join TBL_B

on TBL_B.RefID = TBL_A.ID

where TBL_B.id in

(select max(id)as myMaxID--, RefID

from TBL_B group by RefID)

TBL_A would correspond to your 'Property' tabel and TBL_B to your 'Property Transactions'
The TBL_B.RefID is the reference to TBL_A.ID

Hope this helps.

Regards Harry Snier
 


Hi Gary,

You are talking about "the last one", but don't specify how we can determine what the last one actually is. There must be some property that indicates which is the last one, or that increases so it's possible to sort on it. Assuming that the "Reference" field is actually increased for each transaction (so that they are unique for each PropertyTransaction record of any given Property), the query is this:

SELECT {Property}
INNER JOIN {PropertyTransactions} ON {PropertyTransactions}.[Reference] = (
    SELECT MAX({PropertyTransactions}.[Reference])
    FROM {PropertyTransactions}
    WHERE [{PropertyTransactions}.[PropertyReference] = {Property}.[Reference]
)
Hi Harry,

Thank you fo ryour suggestion, I used it as follows...
 
 select {Property}.*, {PropertyTransactions}.*
from {Property}
left join {PropertyTransactions}
on {PropertyTransactions}.PropertyReference = {Property}.Reference
where {PropertyTransactions}.PropertyReference in
(select max(Reference)as myMaxID--, PropertyReference
from {PropertyTransactions} group by PropertyReference)  

It returned the following error..


Hi Kilian,

Thank you for your help too....
Yes the 'Last One' is the row from PropertyTransactions that has the highest Reference Id and increases for each transaction.
I ran your suggestion and returned the following error?
Thanks in advance for any more help you could offer.


Gary Williams wrote:
 It returned the following error..


 
 
That is because the SQL parser in the platform has some problems with in-lined comments. It should work if you remove the "--, PropertyReference" or change it into "/*, PropertyReference*/".
Gary Williams wrote:
I ran your suggestion and returned the following error?
Thanks in advance for any more help you could offer.

 
 That is because I (inadvertently) gave you some malformed SQL, as you can see on closer inspection. It should be something like "SELECT * FROM {Property}" etc. Sorry for the confusion.

Kilian Hekhuis wrote:
That is because the SQL parser in the platform has some problems with in-lined comments. It should work if you remove the "--, PropertyReference" or change it into "/*, PropertyReference*/".
 
Yes, thas is right. The part in comment was used for the test of the select statement between the parenthesis, I didn't test it in ServiceStudio just directly on a SQL database.

I think Kilian's version is more elegant (apart from the missing " * from" ;-) ), but I do not have tables large enough to see a performance differences between his version and mine.
If you need to make comments in advanced queries use /* Comment */ instead, that way it works. It seems that when sent the new lines are removed from the sql so the comment would be the entire code
Mariana Talone wrote:
If you need to make comments in advanced queries use /* Comment */ instead, that way it works.

Note however, that even between /* */, the parser does some parsing. So for example, the query fails if you use an unmatched single quote (e.g. /* it's great! */ fails).

 
Getting closer.... but no cigar yet! Latest OML attached...
That error means that the number of columns you select ({Property}.* and {PropertyTransactions}.*) do not match the number of columns in the output structure you defined (PropertyTransaction2). You probably want to list the specific fields (in the right order!) in your SELECT statement (the alternative would be to select Property and PropertyTransactions as your output structure).
Thank you, I thought copying the source Entity and pasting into a structure would work... wrong - new attributes worked though.
I did further research on SQL forums and came up with query below, BUT how do I get the OrderBy and Search boxes to work ( I need to Search on 'Property Reference' only)


select p.Address1, p.Reference, p.Archive, t.Reference, t.LLDepBalance, t.DepBal, t.RepairBalance, t.LL_Bal, t.LandlordRef, t.RetentionsBalance
from {Property} p
inner JOIN {PropertyTransactions} t
ON t.PropertyReference = p.REFERENCE 
join 
    (
    select PropertyReference, max(REFERENCE) as maxId 
    from {PropertyTransactions} 
    group by PropertyReference
    ) b 
on t.PropertyReference = b.PropertyReference 
 
and t.[REFERENCE] = b.maxId Where (Branch=@BranchOffice)
and p.Archive=0  Order By p.Address1
Hi Gary,

When the platform warns about a mismatch, it needn't be there's an actual mismatch, but just that the platform can't determine there's no mismatch. But in general, it's good practice to either select the entitie records themselves as output, or explicitly list the fields in your select statement.

As for a search parameter, the string the user entered in the input box needs to be a text input parameter to the query. Be sure to set the "Expand Inline" property of the query parameter to "True". In the advanced query itself, add it to the WHERE clause (or the JOIN condition or whatever place is appropriate for you), e.g.

WHERE t.PropertyReference LIKE '%' + @YourSearchParameter + '%'

For the order, it's basically the same, add an Expand Inline search parameter etc., unless you use the RichWidgets List_SortColumn, which no doubt is covered somewhere else on the forum or the help.
Ok, as my SQL knowledge is pretty poor, I wasn't sure where to put the statement - I tried various places, but got the following error  :




This is what created the error:


 

You're better off not fiddling with SQL if your understanding of it lacks what I would call "elementary skills", but anyway. It seems that PropertyReference is an integer, in which case you can't do a LIKE on it.

I guess your comment is justified... I'm not a developer, I'm just trying to create an app for my own use and so far with the research on the forums my knowledge and ability is getting better. I know I'm out of my depth and my honesty about having poor SQL knowledge was to get a bit more specific help... basically I don't know where I can put extra lines in an advanced query to give the result I'm looking for.
As an aside my 'fiddling' so far has resulted in a really useful timesaving app, I'd be the first to  admit it probably isn't as optimised as a true professional would write, but given the small amount of data and only 5 connectiosn it works as fast as it needs to..! (not a dig... just an explanation of what I'm doing here)
The PropertyReference in the output Structure is set as TEXT not INTEGER..?? and in testing the data displayed in this column is the property address i.e. '1 Sandiway Close'
By the way Kilian, I'm so grateful for all the time you've spent helping me, please don't feel obliged to keep helping :-)
Thank You!
Hi Gary,

Perhaps you can post your OML, so I can check what causes the error. Unless you inadvertently have the Search parameter specified as integer, I can't explain from the error message what else could cause SQL wanting to convert '%' to an integer...
Kilian Hekhuis wrote:
Hi Gary,

Perhaps you can post your OML, so I can check what causes the error. Unless you inadvertently have the Search parameter specified as integer, I can't explain from the error message what else could cause SQL wanting to convert '%' to an integer...
 Thank you... OML attached.!
 
Hi Gary,

The OML you attached does not have the query that you showed in the error messages, so I can't see what caused the error. Do you have the OML that caused the error?
Kilian Hekhuis wrote:
Hi Gary,

The OML you attached does not have the query that you showed in the error messages, so I can't see what caused the error. Do you have the OML that caused the error?
 
 Sorry I'd backed out the change... now back in.!
Ok, that's better. It's like I said: PropertyReference is indeed an integer. It is defined as a "Property Identifier", which means it has the same type as the identifier of the Property table. The property table has the "Reference" column as identifier (as can be seen when opening the properties for the Property table), and Reference is indeed an integer:



That explains the error, as the string resulting from '%' + @Search + '%' was begin interpreted as an integer (or rather, the database engine tried to convert it to an integer, which failed).

I see, I changed to search on Address1 as below and got a different error?
	select p.Address1, t.Reference, t.PropertyReference, t.LLDepBalance, 
t.DepBal, t.RepairBalance, t.LL_Bal, t.LandlordRef, t.RetentionsBalance
from {Property} p 
inner JOIN {PropertyTransactions} t 
ON t.PropertyReference = p.REFERENCE 
join 
    (
    select PropertyReference, max(REFERENCE) as maxId 
    from {PropertyTransactions} 
    group by PropertyReference 
    ) b 
on t.PropertyReference = b.PropertyReference 
and t.[REFERENCE] = b.maxId WHERE (Branch='Northwich') and
 p.Archive=0 and p.Address1 LIKE '%' + @Search + '%' Order by t.LLDepBalance DESC
 
As you can see in the testing, Address1 is text data - Incidently I recognise 'Romanes' as it's part of an address... I guess I need to do more fiddling.!

Looks like you have @searcv as an expand in line parameter
Rui Barbosa wrote:
Looks like you have @searcv as an expand in line parameter
 
 
That is indeed the case. @Gary: I hadn't seen it before, although I should've been triggered by the complaint about the '%' in the previous error message:



The "Expand Inline" makes the platform so a "search and replace" on the parameter before sending it to the database engine. So in this case, the Search parameter containing "Romanes", the platform sends "'%' + Romanes + '%'" to the engine, which causes this error message. The solution in this case is to simply set "Expand Inline" to "No". Only when you put actual SQL code in a parameter you'd want the Expand Inline to be set to Yes.
THAT'S IT..!! Thank you so much for all your help!.... on to the order by now :-)
Well after much more fiddling I finally got the order to work, I ended up removing all the aliases in the advanced query as I kept getting The multi-part identifier 'nnn' could not be bound." so for those visting here with the same problem, here was my final WORKING advanced query:
select {Property}.[Address1], {PropertyTransactions}.[Reference], {PropertyTransactions}.[PropertyReference],
 {PropertyTransactions}.[LLDepBalance], {PropertyTransactions}.[DepBal], {PropertyTransactions}.[RepairBalance], 
{PropertyTransactions}.[LL_Bal], {PropertyTransactions}.[LandlordRef], {PropertyTransactions}.[RetentionsBalance]
from {Property}
inner JOIN {PropertyTransactions}
ON {PropertyTransactions}.[PropertyReference] = {Property}.[Reference]
join 
    (
    select {PropertyTransactions}.[PropertyReference], max(REFERENCE) as maxId 
    from {PropertyTransactions} 
    group by {PropertyTransactions}.[PropertyReference]
    ) FILTER 
on {PropertyTransactions}.[PropertyReference] = FILTER.PropertyReference 
and {PropertyTransactions}.[Reference] = FILTER.maxId WHERE (Branch=@BranchOffice) and
 {Property}.[Archive]=0 and ({Property}.[Address1] LIKE '%' + @Search + '%') ORDER BY @OrderBy