Add a column with default value

Question

Add a column with default value

pedroccamara

SSC-Addicted

Points: 431
More actions
July 3, 2022 at 12:11 pm

#4058122

Hi guys
I have a query with a SELECT statement, that joins 2 other queries, filter a column and order another one. I did it in SSMS.
I would love to add a new column with a static value for all records, past and future.
I believe i should add these lines, but i'm not 100% sure:
Alter Table dbo.CabecDoc
ADD Company VARCHAR (50) Not NULL
Constrain DF_Company default 'S&A'
But i don't know where, in the statement query....at the beginning, at the end, in the middle....
Can anyone help me please?
Thanks a lot all of you
P.S. If this isn't the right place to ask please let me know where

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Here is some code which demonstrates this. Notice that I have changed your constraint name to follow the convention DF_[tablename]_[columnname], because constraint names must be unique (within schema).

DROP TABLE IF EXISTS #CabecDoc;

CREATE TABLE #CabecDoc
(
    SomeInt INT
);

INSERT #CabecDoc
(
    SomeInt
)
VALUES
(1  )
,(2)
,(3);

SELECT cd.SomeInt
FROM #CabecDoc cd;

ALTER TABLE #CabecDoc
ADD Company VARCHAR(50) NOT NULL CONSTRAINT DF_CabecDoc_Company
                                 DEFAULT ('S&A');

SELECT *
FROM #CabecDoc cd;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pedroccamara SSC-Addicted Points: 431 More actions · Answer 2

Hi Phil,

Many thanks for your answer but i am a newbie, really. Unfortunately i didn't understand where to put the code and what. Let me add my code. If you could add a way to add another column with that specific value, it would be awesome!

SELECT dbo.CabecDoc.Data AS Date, dbo.CabecDoc.Entidade AS Entity, dbo.CabecDoc.TipoDoc AS [Doc Type], dbo.CabecDoc.NumDoc AS [Doc N], dbo.CabecDoc.Serie, dbo.CabecDoc.AnoCBL,

dbo.CabecDoc.Responsavel AS SalesPerson, dbo.CabecDoc.Documento AS Doc#, dbo.LinhasDoc.Artigo AS Product#, dbo.LinhasDoc.TaxaIva AS VatRt, dbo.LinhasDoc.Quantidade AS Qty, dbo.LinhasDoc.PrecUnit AS UnitPrice,

dbo.LinhasDoc.Descricao AS Product, dbo.LinhasDoc.CCustoCBL AS [Cost Center#], dbo.LinhasDoc.Desconto1, dbo.LinhasDoc.Desconto2, dbo.LinhasDoc.Desconto3

FROM dbo.CabecDoc RIGHT OUTER JOIN

dbo.LinhasDoc ON dbo.CabecDoc.Id = dbo.LinhasDoc.IdCabecDoc LEFT OUTER JOIN

dbo.CabecDocStatus ON dbo.CabecDoc.Id = dbo.CabecDocStatus.IdCabecDoc

WHERE (dbo.CabecDocStatus.Anulado = 0) AND (dbo.CabecDoc.Data >= CONVERT(DATETIME, '2020-01-01 00:00:00', 102))

ORDER BY Date

Thank you so much Phil

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Ah, please ignore my first answer, which would add a column to a physical table. If all you want to do is add another column to the query, it's as simple as this:

SELECT Date = cd.Data
      ,Entity = cd.Entidade
      ,[Doc Type] = cd.TipoDoc
      ,[Doc N] = cd.NumDoc
      ,cd.Serie
      ,cd.AnoCBL
      ,SalesPerson = cd.Responsavel
      ,Doc# = cd.Documento
      ,Product# = ld.Artigo
      ,VatRt = ld.TaxaIva
      ,Qty = ld.Quantidade
      ,UnitPrice = ld.PrecUnit
      ,Product = ld.Descricao
      ,[Cost Center#] = ld.CCustoCBL
      ,ld.Desconto1
      ,ld.Desconto2
      ,ld.Desconto3
  ,Company = 'S&A'
FROM dbo.CabecDoc cd
    RIGHT OUTER JOIN dbo.LinhasDoc ld
        ON cd.Id = ld.IdCabecDoc
    LEFT OUTER JOIN cdStatus cds
        ON cd.Id = cdStatus.IdCabecDoc
WHERE (cds.Anulado = 0)
      AND (cd.Data >= CONVERT(DATETIME, '2020-01-01 00:00:00', 102))
ORDER BY Date;

I have some additional comments/suggestions:

Mixing right and left joins makes it rather difficult to work out what is going on. Most people stick to LEFT JOINs exclusively.
You have a LEFT JOIN to cdStatus, but in your WHERE clause you have specified that Anulado must be zero. That effectively turns your LEFT JOIN into an INNER JOIN. To make the LEFT JOIN work, use (cds.Anulado = 0 or cds.Anulado IS NULL), otherwise use an INNER JOIN.
Use table aliases to make your code less verbose.
Always qualify your table names with their schemas (so dbo.cdStatus rather than cdStatus)
If the cd.Data column is already a DATE or DATETIME, you can simplify the condition in the WHERE clause:

AND (cd.Data >= '20200101')

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pedroccamara SSC-Addicted Points: 431 More actions · Answer 4

Such valuable information!!!!

That was awesome Phil!!! Thank you sooooo much!!!

Best regards

Pedro

pedroccamara SSC-Addicted Points: 431 More actions · Answer 5

I'm so sorry, but i can't merge these 3 columns: the recent column and 2 more...

dbo.CabecDoc.Serie + '-' + Company + '-' + dbo.LinhasDoc.CCustoCBL AS JoinCC

I've tried so many ways and it's not working....can you help...again?

I've tried also

CONCAT(dbo.CabecDoc.Serie, '-', Company, '-',dbo.LinhasDoc.CCustoCBL) AS JoinCC

and it does not work

This reply was modified 2 years, 4 months ago by pedroccamara.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

This, maybe? (Assuming you are using the table aliases I suggested)

CONCAT(cd.Serie, ' - ', 'S&A', ' - ',ld.CCustoCBL) AS JoinCC

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

pedroccamara wrote:

I'm so sorry, but i can't merge these 3 columns: the recent column and 2 more...
dbo.CabecDoc.Serie + '-' + Company + '-' + dbo.LinhasDoc.CCustoCBL AS JoinCC
I've tried so many ways and it's not working....can you help...again?
I've tried also
CONCAT(dbo.CabecDoc.Serie, '-', Company, '-',dbo.LinhasDoc.CCustoCBL) AS JoinCC
and it does not work

What does 'it does not work' mean? Are you getting an error - wrong data - something else?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

pedroccamara SSC-Addicted Points: 431 More actions · Answer 8

I am Phil, just like you told me so. But i wouldn't like to "repeat" the company name. Is this the only way? Can't i write Company instead of "S&A" in this case?

But also, if it doesn't have any Cost center number on the column it shows me null for this new field and it should concat the other 2 columns anyhow

This reply was modified 2 years, 4 months ago by pedroccamara.

pedroccamara SSC-Addicted Points: 431 More actions · Answer 9

Hi Jeffrey

Yes, it shows me the error : Invalid column name 'Company'

Phil Parkin SSC Guru Points: 246960 More actions · Answer 10

pedroccamara wrote:

I am Phil, just like you told me so. But i wouldn't like to "repeat" the company name. Is this the only way? Can't i write Company instead of "S&A" in this case? But also, if it doesn't have any Cost center number on the column it shows me null for this new field and it should concat the other 2 columns anyhow

Avoiding repetition is good, but you can't do it that way. But this was should work:

SELECT Date = cd.Data
      ,Entity = cd.Entidade
      ,[Doc Type] = cd.TipoDoc
      ,[Doc N] = cd.NumDoc
      ,cd.Serie
      ,cd.AnoCBL
      ,SalesPerson = cd.Responsavel
      ,Doc# = cd.Documento
      ,Product# = ld.Artigo
      ,VatRt = ld.TaxaIva
      ,Qty = ld.Quantidade
      ,UnitPrice = ld.PrecUnit
      ,Product = ld.Descricao
      ,[Cost Center#] = ld.CCustoCBL
      ,ld.Desconto1
      ,ld.Desconto2
      ,ld.Desconto3
      ,Company = c1.Company
      ,JoinCC = CONCAT(cd.Serie, ' - ', c1.Company, ' - ', isnull(ld.CCustoCBL,''))
FROM dbo.CabecDoc cd
    RIGHT OUTER JOIN dbo.LinhasDoc ld
        ON cd.Id = ld.IdCabecDoc
    LEFT OUTER JOIN cdStatus cds
        ON cd.Id = cdStatus.IdCabecDoc
    CROSS APPLY
(SELECT Company = 'S&A') c1
WHERE (cds.Anulado = 0)
      AND (cd.Data >= CONVERT(DATETIME, '2020-01-01 00:00:00', 102))
ORDER BY Date;

This reply was modified 2 years, 4 months ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pedroccamara SSC-Addicted Points: 431 More actions · Answer 11

Amazing job Phil!!

You've done all the hard work for me AND solved my problem!!!

Thank you thank you thank you!!!

Phil Parkin SSC Guru Points: 246960 More actions · Answer 12

It really is great when people are polite, enthusiastic and post back with gratitude. Pleade keep on posting and learning.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin