July 3, 2022 at 12:11 pm
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
July 3, 2022 at 12:22 pm
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.
July 3, 2022 at 12:50 pm
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
July 3, 2022 at 1:07 pm
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:
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.
July 3, 2022 at 1:13 pm
Such valuable information!!!!
That was awesome Phil!!! Thank you sooooo much!!!
Best regards
Pedro
July 3, 2022 at 3:10 pm
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
July 3, 2022 at 3:34 pm
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.
July 3, 2022 at 3:35 pm
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
July 3, 2022 at 3:38 pm
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
July 3, 2022 at 3:40 pm
Hi Jeffrey
Yes, it shows me the error : Invalid column name 'Company'
July 3, 2022 at 6:08 pm
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;
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.
July 3, 2022 at 6:34 pm
Amazing job Phil!!
You've done all the hard work for me AND solved my problem!!!
Thank you thank you thank you!!!
July 3, 2022 at 8:02 pm
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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply