0

I have a database with a table, which I will call Table1. The database also has 2 table-valued functions. The first function returns a table which I will call Table2 and the second function returns a table which I will call Tabel3. There is also a 4th table, which I will call Table4, which I want to insert records into.

Table1 has the following fields:

Fileno
Description
GeneralCode

The first function takes in 2 parameters (Table1.Fileno, Table1.Description) and returns Table2, which has the following fields:

FileNum
Desc1
Desc2
Desc3

The second function takes in 2 parameters (Table1.Fileno, Table1.GeneralCode) and returns Table3, which has the following fields:

FileNum
Code1
Code2

The forth table, Table4, has the following fields:

CaseNum
Desc1
Desc2
Desc3
Code1
Code2

I wrote the following query which works:

DELETE FROM Table4
INSERT INTO Table4(CaseNum, Desc1, Desc2, Desc3)
SELECT Fileno, Desc1, Desc2, Desc3
FROM Table1 
CROSS APPLY function1(FileNo, Description)

This query runs, calls the function (which returns a table), and inserts data into Table4.

Now, I need to modify the query to call a second function, which will return another table, and insert that data into Table4. I have the following code:

DELETE FROM Table4
INSERT INTO Table4(CaseNum, Desc1, Desc2, Desc3, Code1, Code2)
SELECT m.Fileno, d.Desc1, d.Desc2, d.Desc3, c.Code1, c.Code2
FROM Table1 m
INNER JOIN function1(m.Fileno, m.Description) d ON d.FileNum = m.Fileno
LEFT OUTER JOIN function2(m.Fileno, m.GeneralCode) c ON c.FileNum = m.Fileno;

But this code does not work. Intellisense highlights the fields that I am passing to the functions so obviously I can't reference these fields in this manner but I'm not sure how to resolve that.

Any help is greatly appreciated. Thanks!

Dale K
  • 25,246
  • 15
  • 42
  • 71
Jonathan Small
  • 1,027
  • 3
  • 18
  • 40
  • Can you please report what does `SELECT @@version;` return? If that doesn't work, try `SELECT VERSION();` – Bill Karwin Dec 23 '22 at 18:02
  • @Akina - Yes, I am using MS SQL – Jonathan Small Dec 23 '22 at 18:05
  • @BillKarwin - SELECT @@version returns Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (X64) Nov 1 2020 00:48:37 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor) – Jonathan Small Dec 23 '22 at 18:06
  • You are using Microsoft SQL Server, not MySQL. I've corrected the tag. Using the right tags is important because folks on Stack Overflow follow tags, and you want the people who are best able to answer your question to notice it. – Bill Karwin Dec 23 '22 at 18:22
  • @BillKarwin - Thank you, I must have clicked on the wrong suggested tag. – Jonathan Small Dec 23 '22 at 18:24
  • I would never return a table in a function due to potential performance issues: https://stackoverflow.com/questions/4109152/table-valued-function-killing-my-query-performance – Sebastian S. Dec 23 '22 at 18:33
  • 1
    @SebastianS. - This is not a concern as this script is being written to convert data from an outside client to an inhouse database. It will only be used once to convert the data. Once the data is converted, this script will no longer be needed – Jonathan Small Dec 23 '22 at 18:40
  • Do your functions return 1 or n rows? You can't *join* a TVF like that but you can use it in-line or like you have *already shown* – Stu Dec 23 '22 at 19:56
  • @Stu - The functions each return 1 row. They are actually based on a function that you helped me on here: https://stackoverflow.com/questions/74882263/sql-server-split-data-element-based-on-delimiter?noredirect=1#comment132160741_74882263 – Jonathan Small Dec 23 '22 at 20:03

1 Answers1

0

Your query should probably look something like the following.

You can apply multiple table expressions or TVFs, you're showing an outer-join in your sample query so use outer apply where you might (presumably) not get a row returned, and then handle the NULLs, if necessary.

select m.Fileno, 
    f1.Desc1, f1.Desc2, f1.Desc3, 
    IsNull(f2.Code1,''), IsNull(f2.Code2,'')
from Table1 m
cross apply function1(m.FileNo, m.Description)f1
outer apply function2(m.FileNo, m.GeneralCode)f2;
Stu
  • 30,392
  • 6
  • 14
  • 33