Microsoft SQL Server Management Studio - Views - Query with concatenate doesn't work - New Query or stored procedure the same select works fine

Question

**

• The following query doesnt work in the MSSMS Environment if I use the new view environment:

**

SELECT        PB_110233673.Buchungstag, YEAR(PB_110233673.Buchungstag) AS Buchungsjahr, PB_110233673.[Laufende Nummer], PB_110233673.Betrag, PB_110233673.Saldo,   
                         PB_110233673.Saldo - PB_110233673.Betrag AS [Saldo vor Buchung], PB_110233673.[Begünstigter/Absender - Bankleitzahl], PB_110233673.[Begünstigter/Absender - Kontonummer],   
                         PB_110233673.[Begünstigter/Absender - Name], PB_110233673.Unterkategorie, PB_110233673.Kostenstelle, Kostenstellen.Beschreibung, Kostenstellen.[Kostenstellengruppe 1], Kostenstellen.[Kostenstellengruppe 2],   
                         **{ fn CONCAT(PB_110233673.[Verwendungszweckzeile 1], PB_110233673.[Verwendungszweckzeile 2], PB_110233673.[Verwendungszweckzeile 3], PB_110233673.[Verwendungszweckzeile 4],   
                         PB_110233673.[Verwendungszweckzeile 5], PB_110233673.[Verwendungszweckzeile 6], PB_110233673.[Verwendungszweckzeile 7], PB_110233673.[Verwendungszweckzeile 8], PB_110233673.[Verwendungszweckzeile 9],   
                         PB_110233673.[Verwendungszweckzeile 10], PB_110233673.[Verwendungszweckzeile 11], PB_110233673.[Verwendungszweckzeile 12]) } AS [Verwendungszweck1-12],** PB_110233673.Abrechnungsjahr,   
                         PB_110233673.Auszug  
FROM            PB_110233673 INNER JOIN  
                         Kostenstellen ON PB_110233673.Kostenstelle = Kostenstellen.Name  
WHERE        (YEAR(PB_110233673.Buchungstag) = 2020)  
ORDER BY PB_110233673.[Laufende Nummer]  

--------------------------- Microsoft SQL Server Management Studio --------------------------- SQL Execution Error. Executed SQL statement: SELECT PB_110233673.Buchungstag, YEAR(PB_110233673.Buchungstag) AS Buchungsjahr, PB_110233673.[Laufende Nummer], PB_110233673.Betrag, PB_110233673.Saldo, PB_110233673.Saldo - PB_110233673.Betrag AS [Saldo vor Buchung], PB_110233673.[Begünstigter/Abse... Error Source: .Net SqlClient Data Provider Error Message: The CONCAT function requires 2 argument(s). --------------------------- OK Hilfe

**

• If I use different areras in the MSSMS Environment, like "stored procedure" or "Select Top 1000 Rows" as a starting point it works fine.

**

What is the reason for that different behavior? For me it is a bug in the "view area" and also in the "Edit Top 200 rows" area.

Could you please have a look to it.

Kind regards
Alois

Answer 1

It seems that this ODBC function requires exactly 2 arguments. If you want to concatenate more values, then try CONCAT without “{fn}”:

SELECT ... CONCAT(PB_110233673.[Verwendungszweckzeile 1], PB_110233673.[Verwendungszweckzeile 2], PB_110233673.[Verwendungszweckzeile 3] ...)

But if you need the ODBC function for some reason, then write something like this: {fn CONCAT(a, CONCAT(b, CONCAT(c, d)))}.

Answer 2

Many thanks for your answer: I tried the recommended notation
The query is running now, but the result isn't as expected - Most of the shown lines are now null although in the seperate fields values are included and are shown without concat

What i am not understand: does the SSMS different works with the sql server - in the view section with ODBC and in the stored procedure with a different protocol?

For me as a more or less end user it is confusing, again for me it is a bug in the SSMS

SELECT

 { fn CONCAT(dbo.PB_110233673.[Verwendungszweckzeile 1], { fn CONCAT(dbo.PB_110233673.[Verwendungszweckzeile 2], 
                         { fn CONCAT(dbo.PB_110233673.[Verwendungszweckzeile 3], { fn CONCAT(dbo.PB_110233673.[Verwendungszweckzeile 4], dbo.PB_110233673.[Verwendungszweckzeile 5]) }) }) }) } AS [Verwendungszweck1-12], 

Answer 3

Hi @Alois Weber ,

You could refer below basic example with fn CONCAT:

{fn CONCAT({fn CONCAT(column1, ' ')}, column2)}  

You also could refer below with 3 columns and modify your query like it if there is any null or non-varchar data type in your columns:

{fn CONCAT({fn CONCAT({fn CONCAT(isnull(cast(column1 as varchar(100)),''), ' ')}, ' '+ isnull(cast(column2 as varchar(100)),''))},' '+isnull(cast(column3 as varchar(100)),''))}  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 4

Hi,

I don't have problems with concat. It works fine for me if I use it in a query within Microsoft SQL Server Management Studio, for example in <Programmability> <Stored Procedures >

Select

concat ([Verwendungszweckzeile 1]
      ,[Verwendungszweckzeile 2]
      ,[Verwendungszweckzeile 3]
      ,[Verwendungszweckzeile 4]
      ,[Verwendungszweckzeile 5]
      ,[Verwendungszweckzeile 6]
      ,[Verwendungszweckzeile 7]
      ,[Verwendungszweckzeile 8]
      ,[Verwendungszweckzeile 9]
      ,[Verwendungszweckzeile 10]
      ,[Verwendungszweckzeile 11]
      ,[Verwendungszweckzeile 12]) as [Verwendungszweck1-12]

works fine.

But if I want to use the same code in <Views> it doesn't work. I always get the rror messasge Concat requires two arguments.

I don't understand why It works in one part of Microsoft SQL Server Management Studio and doesn't work in another part.

That is my question?

I hope at least the question is now clear.

Answer 5

Hi @Alois Weber ,

There is a chance of null value in the concatenated fields.

You could have a try to apply ISNULL function to the columns from the outer joined table.

Please refer below:

 concat (ISNULL([Verwendungszweckzeile 1],'')  
       ,ISNULL([Verwendungszweckzeile 2],'')  
       ,ISNULL([Verwendungszweckzeile 3],'')  
       ,ISNULL([Verwendungszweckzeile 4],'')  
       ,ISNULL([Verwendungszweckzeile 5],'')  
       ,ISNULL([Verwendungszweckzeile 6],'')  
       ,ISNULL([Verwendungszweckzeile 7],'')  
       ,ISNULL([Verwendungszweckzeile 8],'')  
       ,ISNULL([Verwendungszweckzeile 9],'')  
       ,ISNULL([Verwendungszweckzeile 10],'')  
       ,ISNULL([Verwendungszweckzeile 11],'')  
       ,ISNULL([Verwendungszweckzeile 12],'')) as [Verwendungszweck1-12]  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet