cancel
Showing results for 
Search instead for 
Did you mean: 

combining two fields and search for the highest value

Former Member
0 Kudos

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
1122112235
1122112236
5323532365
11231123XX

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

Accepted Solutions (1)

Accepted Solutions (1)

frank_wang6
Active Contributor
0 Kudos

T0.[U_compcode] + 1  (I guess this need to changed CAST(T0.U_compcode as INT) + 1, i felt ur field is defined as varchar)


T0.[U_compcode]  Like '@FirstFour%%'



You are confused SQL server, what;s the type of this U_compcode field? Varchar or INT?


Frank

Former Member
0 Kudos

Hello Frank ,

thanks for your reply ,

all the fields are varchar datatype

frank_wang6
Active Contributor
0 Kudos

TRY THIS.

SELECT CAST( CAST(MAX(T0.[U_compcode]) AS INT) + 1 AS VARCHAR(6))

FROM AIQG Where  LEFT(T0.[U_compcode], 4) = T0.U_prodcode + T0.U_typecode

Frank

Answers (0)