Union all & NZ query (1 Viewer)

[gmazza76]

Good afternoon,

I am new to SQL and I have jumped I think in at the depend end.
I have a table called "All_data" that I want to populate from weekly tables (44 & 45)
All data has 27 columns but my data in Week_45 start with partial data and can complete up to all columns by the end of the week, but I am getting a syntax error at the "union all"point below. I have only used the Select NZ on the Week 45 as it isn't currently a complete week and I need to give updates through out the week.

Can I be please pointed in the right direction to fix this error?

delete * from All_data;
INSERT INTO All_data( DateTag, SessionIDTag, DNISTag, CLITag, LOBusTag, SegTag, ICMTag, CountTag, PrompSTag, AppTag, CardTypeHintTag, AccountTypeHintTag, WildcardHintTag, ReturnCodeTag, CETag, Tag16, Tag17, Tag18, Tag19, Tag20, Tag21, Tag22, Tag23, tag24, tag25, tag26, tag27, tag28 )
SELECT Week_44.Field1, Week_44.Field2, Week_44.Field3, Week_44.Field4, Week_44.Field5, Week_44.Field6, Week_44.Field7, Week_44.Field8, Week_44.Field9, Week_44.Field10, Week_44.Field11, Week_44.Field12, Week_44.Field13, Week_44.Field14, Week_44.Field15,
Week_44.Field16, Week_44.Field17, Week_44.Field18, Week_44.Field19, Week_44.Field20, Week_44.Field21, Week_44.Field22, Week_44.Field23, Week_44.Field24, Week_44.Field25, Week_44.Field26, Week_44.Field27, Week_44.Field28
from Week_44
Union all
SELECT NZ(Week_45.Field1,""), (Week_45.Field2,""), (Week_45.Field3,""), (Week_45.Field4,""), (Week_45.Field5, ""), (Week_45.Field6,""), (Week_45.Field7,""), (Week_45.Field8,""), (Week_45.Field9,""), (Week_45.Field10,""), (Week_45.Field11,""), (Week_45.Field12,""), (Week_45.Field13,""), (Week_45.Field14,""), (Week_45.Field15,""),
(Week_45.Field16,"") ,(Week_45.Field17,""), (Week_45.Field18,""), (Week_45.Field19,""), (Week_45.Field20,""), (Week_45.Field21,""), (Week_45.Field22,""), (Week_45.Field23,""), (Week_45.Field24,""), (Week_45.Field25,""), (Week_45.Field26,""), (Week_45.Field27,""), (Week_45.Field28,"")
from Week_45
select * from All_data;

thanks in advance
Gavin

 

[Minty]

You need to use the NZ prefix on every field

SELECT NZ(Week_45.Field1,""), NZ(Week_45.Field2,""), NZ(Week_45.Field3,""), NZ(Week_45.Field4,""), NZ(Week_45.Field5, ""), Etc Etc

 

[gmazza76]

Thank you @Minty I have updated the code as per below, but I get a "syntax error in the FROM clause" is there any easy way to see what I have missed?

delete * from All_data;
INSERT INTO All_data ( DateTag, SessionIDTag, DNISTag, CLITag, LOBusTag, SegTag, ICMTag, CountTag, PrompSTag, AppTag, CardTypeHintTag, AccountTypeHintTag, WildcardHintTag, ReturnCodeTag, CETag, Tag16, Tag17, Tag18, Tag19, Tag20, Tag21, Tag22, Tag23, tag24, tag25, tag26, tag27, tag28 )
SELECT Week_44.Field1, Week_44.Field2, Week_44.Field3, Week_44.Field4, Week_44.Field5, Week_44.Field6, Week_44.Field7, Week_44.Field8, Week_44.Field9, Week_44.Field10, Week_44.Field11, Week_44.Field12, Week_44.Field13, Week_44.Field14, Week_44.Field15,
Week_44.Field16, Week_44.Field17, Week_44.Field18, Week_44.Field19, Week_44.Field20, Week_44.Field21, Week_44.Field22, Week_44.Field23, Week_44.Field24, Week_44.Field25, Week_44.Field26, Week_44.Field27, Week_44.Field28
from Week_44
Union all
SELECT NZ(Week_45.Field1,""), NZ(Week_45.Field2,""), NZ(Week_45.Field3,""), NZ(Week_45.Field4,""), NZ(Week_45.Field5, ""), NZ(Week_45.Field6,""), NZ(Week_45.Field7,""), NZ(Week_45.Field8,""), NZ(Week_45.Field9,""), NZ(Week_45.Field10,""), NZ(Week_45.Field11,""), NZ(Week_45.Field12,""), NZ(Week_45.Field13,""), NZ(Week_45.Field14,""), NZ(Week_45.Field15,""), NZ(Week_45.Field16,"") ,NZ(Week_45.Field17,""), NZ(Week_45.Field18,""), NZ(Week_45.Field19,""), NZ(Week_45.Field20,""), NZ(Week_45.Field21,""), NZ(Week_45.Field22,""), NZ(Week_45.Field23,""), NZ(Week_45.Field24,""), NZ(Week_45.Field25,""), NZ(Week_45.Field26,""), NZ(Week_45.Field27,""), NZ(Week_45.Field28,"")
from Week_45
);
select * from All_data;

thanks

 

[Minty]

Sorry, I'm being dim - this is in SQL server yes?
Nz doesn't work in SQL server, and it will barf at the double quotes - use single ones. Try IsNull() instead.

IsNull(Week_45.Field1,'') , IsNull(Week_45.Field2,''), etc