0

I created a table, tblNewParts with 3 columns:

NewCustPart
AddedDate
Handled

and I am trying to FULL JOIN it to an existing table, tblPartsWorkedOn. tblNewParts is defined to have Handled defaulted to 'N'...

SELECT * 
FROM dbo.tblPartsWorkedOn AS BASE
FULL JOIN dbo.tblNewParts AS ADDON ON BASE.[CustPN] = ADDON.[NewCustPart]
WHERE ADDON.[Handled] IS NULL
ORDER BY [CustPN] DESC

And I want the field [Handled] to come back as 'N' instead of NULL when I run the query. The problem is that when there aren't any records in the new table, I get NULL's instead of 'N's.

I saw a SELECT CASE WHEN col1 IS NULL THEN defaultval ELSE col1 END as a mostly suitable answer from here. I am wondering if this will work in this instance, and how would I write that in T-SQL for SQL Server 2012? I need all of the columns from both tables, rather than just the one.

I'm making this a question, rather than a comment on the cited link, so as to not obscure the original link's question.

Thank you for helping!

Community
  • 1
  • 1
CherryCoke
  • 309
  • 3
  • 17
  • 1
    What is stopping you from trying this solution? – Ivan Starostin Apr 02 '16 at 13:52
  • @IvanStarostin still being fairly new to SQL, and hitting walls because it's not a full programming language, I asked for help and then went off to try things also. I posted my "answer" as it now works for me, and I am hoping that it will stay up for others that think like I do, and not as the typical SQL Programmer. :D – CherryCoke Apr 02 '16 at 14:26
  • 1
    Feel free to post some more answers with demonstration of `ISNULL` and `COALESCE` functions usage. – Ivan Starostin Apr 02 '16 at 15:05

2 Answers2

1

Name the column (alias.column_name) in select statement and use ISNULL(alias.column,'N').

Thanks

Rajesh Ranjan
  • 537
  • 2
  • 12
  • Can you further explain this, with an example? I would love to know how to fit this into what I am doing, as I am going to be using a lot of T-SQL in the foreseeable future! Thank you!!! – CherryCoke Apr 07 '16 at 12:36
0

After many iterations I found the answer, it's kind of bulky but here it is anyway. Synopsis:

Yes, the CASE statement does work, but it gives the output as an unnamed column. Also, in this instance to get all of the original columns AND the corrected column, I had to use SELECT *, CASE...END as [ColumnName].

But, here is the better solution, as it will place the information into the correct column, rather than adding a column to the end of the table and calling that column 'Unnamed Column'.

Select [ID], [Seq], [Shipped], [InternalPN], [CustPN], [Line], [Status], 
       CASE WHEN ADDON.[NewCustPart] IS NULL THEN BASE.[CustPN] ELSE 
            ADDON.[NewCustomerPart] END as [NewCustPart],
       GetDate() as [AddedDate], 
       CASE WHEN ADDON.[Handled] IS NULL THEN 'N' ELSE ADDON.[Handled] END as [Handled]

        from dbo.tblPartsWorkedOn as BASE
        full join dbo.tblNewParts as AddOn ON Base.[CustPN] = AddOn.NewCustPart

        where AddOn.Handled = 'N' or AddOn.Handled is null
        order by [NewCustPart] desc

This sql code places the [CustPN] into [NewCustPart] if it's null, it puts a 'N' into the field [Handled] if it's null and it assigns the date to the [AddedDate] field. It also only returns records that have not been handled, so that you get the ones that need to be looked at; and it orders the resulting output by the [NewCustPart] field value.

Resulting Output looks something like this: (I shortened the DateTime for the output here.)

[ID]   [SEQ]   [Shipped]   [InternalPN]   [CustPN]   [Status]   [NewCustPart]   [AddedDate]   [Handled]
1      12      N           10012A         10012A     UP         10012A          04/02/2016    N
...

Rather than with the nulls:

[ID]   [SEQ]   [Shipped]   [InternalPN]   [CustPN]   [Status]   [NewCustPart]   [AddedDate]   [Handled]
1      12      N           10012A         10012A     UP         NULL            NULL          NULL
...

I'm leaving this up, and just answering it rather than deleting it, because I am fairly sure that someone else will eventually ask this same question. I think that lots of examples showing how and why something is done, is a very helpful thing to have as not everything can be generalized. Just some thoughts and I hope that this helps someone else!

CherryCoke
  • 309
  • 3
  • 17