Add a column with default value

  • 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

  • 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;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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:

    1. Mixing right and left joins makes it rather difficult to work out what is going on. Most people stick to LEFT JOINs exclusively.
    2. 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.
    3. Use table aliases to make your code less verbose.
    4. Always qualify your table names with their schemas (so dbo.cdStatus rather than cdStatus)
    5. If the cd.Data column is already a DATE or DATETIME, you can simplify the condition in the WHERE clause:

    AND (cd.Data >= '20200101')

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Such valuable information!!!!

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

    Best regards

    Pedro

  • 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 1 month, 1 week ago by  pedroccamara.
  • This, maybe? (Assuming you are using the table aliases I suggested)

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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 1 month, 1 week ago by  pedroccamara.
  • Hi Jeffrey

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

  • 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 1 month, 1 week ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Amazing job Phil!!

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

    Thank you thank you thank you!!!

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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