Skip to Content
0
Former Member
Aug 16, 2015 at 09:31 AM

combining two fields and search for the highest value

11 Views

Dear Experts ,

I have this issue with a query . i need the query to do the following :

Concats the T0.[U_prodcode], T0.[U_typecode] and then search in a 3rd Column T0.[U_compcode] where they are combined :

This will select the highest combined number after searching combined column with a concatenated string/number. and return it back as a number just add 1 to it

for example

11 22 112235

11 22 112236

53 23 532365

11 23 1123XX

U_prodcode U_typecode U_compcode 11 22 112235 11 22 112236 53 23 532365 11 23 1123XX

now for the last row i want the query to give me the U_compcode wish it will be combining the T0.[U_prodcode] and T0.[U_typecode], then search in U_compcode for the one that have the same values[11][22] , and the hightest digit after it , wish it will be the second row 112236 , now the result of the query should be 112337.

I had this query but it shows errors :

The multi-part identifier "T0.U_prodcode" could not be bound.

The multi-part identifier "T0.U_typecode" could not be bound.


here is the query :

DECLARE @FirstFour varchar = CONCAT( T0.[U_prodcode], T0.[U_typecode])

SELECT TOP(1) T0.[U_compcode] + 1

FROM AIQG Where T0.[U_compcode] Like '@FirstFour%%'

order by T0.[U_compcode] Desc