ask formatted search using UDT

[Former Member]

dear all,

I have a question about query in formatted search. i 'm trying to make query like this

select 'a-'+ $[@UDT.Name]  from [@UDT].

This query will attach in Formatted search in the same udt in code field.

But i still face a problem how can i get the name value in this UDT to set in my query.

thanks in advance

regards

Jia shun

Edited by: Jia Shun on May 11, 2011 9:13 AM

[Former Member · Accepted solution]

Hi Jia,

Try This....

SELECT 'a' + $[@USERCONTRACT.Name] 

First Enter the data in the Name Field in the udt then go to the Code Field

Thanks

Shafi

[Former Member · Accepted solution]

Hi shafi_sunshine , thanks for your respond. actually it's work , but i face another problem again.

Actually i just want to set an autonumbering in Formatted search . i create a query like this in SAP and attach it in formatted search. but when i click the formatted search there is an error like this conversion failed from nvarchar to int. Do you know why. The query is like this :

DECLARE @Code nvarchar(8)
SET @Code = (SELECT MAX(Code) FROM [@UDT])

IF @Code IS NULL
            SELECT '001' + '-' + $[@UDT.Name]                                                                                '
ELSE
            (SELECT RIGHT( '00' + REPLACE(MAX(Code)+1,'',''), 3) +'-' + $[@UDT.Name]                                                                                FROM [@UDT])

So the result will be like this 001-A, 002-B , ect

Do you know how to convert it ?

Thanks in advance

Jia shun

Edited by: Jia Shun on May 11, 2011 10:30 AM

Edited by: Jia Shun on May 11, 2011 10:32 AM

[former_member689126 · Accepted solution]

Hi

try This

DECLARE @Code nvarchar(8)
SET @Code = (SELECT MAX(Code) FROM [@UDT])
 
IF @Code IS NULL
            SELECT '001' + '-' + $[@UDT.Name]                                                                                
ELSE
            (SELECT RIGHT( '00' + REPLACE(MAX(Code)+'1','',''), 3) +'-' + $[@UDT.Name]            
            
FROM [@UDT])

Regards

Arun

[Former Member · Accepted solution]

Hi arun,

Thanks for your respond, i have try this. but the code return wrong value. It does not like the result i want, for example

001+ [@UDT.Name], 002 +...

and so on. Do you know why ?

Thanks in advance

Regards

Jia Shun

Edited by: Jia Shun on May 12, 2011 4:32 AM

[former_member689126 · Accepted solution]

Hi

Try this one

DECLARE @Code nvarchar(8)
SET @Code = (SELECT MAX(CAST(Code AS int))  FROM [@UDT])
 
IF @Code IS NULL
            SELECT '001' + '-' + $[@UDT.Name]                                                                                
ELSE
            (SELECT RIGHT( '00' + REPLACE(MAX(CAST(Code AS int))+1,'',''), 3) +'-' + $[@UDT.Name] FROM [@UDT])

hope this will solve your problem

Regards

Arun

[Former Member]

Hi Jia

Try This.....

declare @code as nvarchar(8)
declare @temp1 as nvarchar(15)
declare @temp as nvarchar(8)
SET @Code = (SELECT MAX(Code) FROM [@usercontract])
 IF @Code IS NULL
            SELECT '001' + '-' + $[@usercontract.Name]                                                                                
ELSE
           
set @temp =(select substring(max(T0.[Code]),1,3)+1 FROM [dbo].[@USERCONTRACT]  T0)
set @temp1 ='00'+ @temp + '-' + $[@usercontract.Name]
select @temp1

Thanks

Shafi

[Former Member]

To Shafi, thanks for your respond, it's work

To Arun. thanks for your respond and support

Edited by: Jia Shun on May 12, 2011 12:42 PM