How to concatenate columns identified by surname?

Asked

Viewed 47 times

3

I have the following appointment:

select case when datediff(...) > 0 then month(tblData.data1)
            when day(tblData.data2) > 10 then month(tblData.data2)
            when month(tblData.data2) = 1 then 12 
            else month(tblData.data2) - 1 end as Mês,

    case when datediff(...) > 0 then year(tblData.data1)
    when day(tblData.data2) > 10 then year(tblData.data2)
    when month(tblData.data2) = 1 then year(tblData.data2) - 1 
    else year(tblData.data2) end as Ano,

    concat(Ano, Mês) as AnoMes -- Como fazer essa linha aqui funcionar??

...
from where order by

I need the third column to concatenate the values of the previous two. However, if I put this function I take an undefined column name error since Anoand Mês are just nicknames.

  • How can I concatenate these values avoiding the repetition of these case when???

Obs: the parts omitted in no way interfere with the question!

sql sql-server tsql string-concatenation
  • 1

    It is not possible to reference an alias in the same query, because this is processed at the end, and at the time of the "Month" execution it does not exist, it will only be at the end of the query. The solution to this is to transform the first case into a subselect

1 answer

1

It’s not possible that way. Even if your columns with a nickname come before, that’s not how it works...

This code snippet for example:

case when datediff(...) > 0 then month(tblData.data1)
            when day(tblData.data2) > 10 then month(tblData.data2)
            when month(tblData.data2) = 1 then 12 
            else month(tblData.data2) - 1 end as Mês

It has not yet been processed in the context of your query, so this nickname "Month" does not exist yet. It is not as simple as an excel for example, to access this information it needs to be processed first.

To not repeat this processing, you can make a SELECT around your query, turning the main query into a SUB-QUERY.

Example:

SELECT mês 
  FROM (SELECT CASE
                 WHEN datediff(.. .) > 0 THEN MONTH(tbldata.data1)
                 WHEN DAY(tbldata.data2) > 10 THEN MONTH(tbldata.data2)
                 WHEN MONTH(tbldata.data2) = 1 THEN 12
                 ELSE MONTH(tbldata.data2) - 1
               END AS mês
          FROM tblData)

So the month column has already been processed in your sub-query and you can do whatever you want with it in the main query without repeating the processing.

You could also create a view for your table, returning these formatted fields and use it in your query... The ways of doing this are several...

Browser other questions tagged sql sql-server tsql string-concatenation

You are not signed in. Login or sign up in order to post.