Create function that returns table in SQL

Question

I wanted to create view with some logic like using (for loop , if .. else) but since that's not supported in SQL I thought of creating table function that takes no parameter and returns a table.

I have a table for orders as below

OrderId  Destination  Category  Customer
----------------------------------------
6001     UK           5         Adam
6002     GER          3         Jack

And table for tracking orders as below

ID  OrderID  TrackingID
-----------------------
1   6001     1
2   6001     2
3   6002     2

And here are the types of tracking

ID  Name
--------------
1   Processing
2   Shipped
3   Delivered

As you can see in tracking order, The order number may have more than one record depending on how many tracking events occurred.

We have more than 25 tracking types that I didn't include here. which means one order can exist 25 times in tracking order table.

Now with that being said , My requirements is to create view as below with condition that an order must belong to 5 or 3 category ( we have more than 15 categories).

And whenever I run the function it must return the updated information.

So for example, when new tracking occurs and it's inserted in tracking order , I want to run my function and see the update in the corresponding flag column (e.g isDelivered).

I'm really confused on what is the best way to achieve this. I don't need the exact script i just need to understand the way to achieve it as i'm not very familiar with SQL

I don't see why you cannot do this in a query. You can use `PIVOT` for example — Wouter, Sep 09 '20 at 12:16
@Wouter Thanks for your answer , We wanted to have it as view so we could just call it directly from out APIs. However I will look into that — rav, Sep 09 '20 at 12:21
I agree with Wouter's comment. If you do end up going the function route: you want to create what is called a "table-valued function" (returns a table). See [this page](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql) of the documentation. Examples B and C show table-valued functions. — Sander, Sep 09 '20 at 12:22
@rav that's what I meant, create a query and put it in your view — Wouter, Sep 09 '20 at 12:27
@Wouter As far from what I understood from reading , we can't use If else statements in views. Also I don't want all the orders to be returned , I want only of category of 5 and 3. In addition in case of category 5 I want the order record to be present in my view if certain tracking type occurred. For example if it's category 5 and the tracking type was of type 2 then the record shall be included and many similar conditions .. — rav, Sep 09 '20 at 12:41
If...else not, but you can use `case`. Conditions you can put in the `where` clause. — Wouter, Sep 09 '20 at 12:44

SteveC · Accepted Answer · 2020-09-09T17:14:09.073

1

It could be done with a crosstab query using conditional aggregation. Something like this

select o.OrderID, 
       max(case when tt.[Name]='Processing' then 1 else 0 end) isPrepared,
       max(case when tt.[Name]='Shipped' then 1 else 0 end) isShipped,
       max(case when tt.[Name]='Delivered' then 1 else 0 end) isDelivered
from orders o
     join tracking_orders tro on o.OrderID=tro.OrderID
     join tracking_types tt on tro.TrackingID=tt.TrackingID
where o.category in(3, 5)
group by o.OrderID;

[EDIT] To break out Category 3 orders, 3 additional columns were added to the cross tab.

select o.OrderID, 
       max(case when tt.[Name]='Processing' then 1 else 0 end) isPrepared,
       max(case when tt.[Name]='Shipped' then 1 else 0 end) isShipped,
       max(case when tt.[Name]='Delivered' then 1 else 0 end) isDelivered,
       max(case when tt.[Name]='Processing' and o.category=3 then 1 else 0 end) isC3Prepared,
       max(case when tt.[Name]='Shipped' and o.category=3 then 1 else 0 end) isC3Shipped,
       max(case when tt.[Name]='Delivered'  and o.category=3 then 1 else 0 end) isC3Delivered
from orders o
     join tracking_orders tro on o.OrderID=tro.OrderID
     join tracking_types tt on tro.TrackingID=tt.TrackingID
where o.category in(3, 5)
group by o.OrderID;

edited Sep 09 '20 at 17:14

answered Sep 09 '20 at 12:40

SteveC

5,955
2
11
24

What if I want only to retrieve the orders which belongs to category 3 if the tracking type 2 occurred? – rav Sep 09 '20 at 16:05
Column 'orders .OrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – rav Sep 09 '20 at 16:38
Updated with GROUP BY – SteveC Sep 09 '20 at 16:39
Thanks for your quick reply I really appreciate it. But I think you misunderstood what I meant. I want to retrieve orders of category 3 and 5 but in case of category 3 I want to retrieve only orders which has tracking type 2 – rav Sep 09 '20 at 16:57
It breaks out order type indicators for category 3 orders – SteveC Sep 09 '20 at 17:15
The query runs successfully , I have a huge set of data I'm trying to figure out how to test the result.I have one question , what does the max before each case does exactly? – rav Sep 09 '20 at 18:14
Also the end keyword? Because I have one order with two tracking processing and shipped but it shows 0 for processing and 1 for shipped while it should be 1 for both – rav Sep 09 '20 at 18:27
The MAX function is best read from the inside of the parentheses first, The CASE logic return either 1 or 0. So the MAX function return 1 if any of the rows contain a TRUE condition (for the CASE logic). The full syntax of CASE WHEN ... THEN ... ELSE ... END. The keyword END closes the CASE WHEN statement. END doesn't really do anything by itself. – SteveC Sep 09 '20 at 18:32
Unfortunately , It's not checking right I don't know why exactly some orders for example have a tracking of type shipped but in the result it shows 0 – rav Sep 09 '20 at 18:56
Sorry I no longer respond to comments in answers which have not been accepted or +1'ed – SteveC Sep 10 '20 at 11:22

Create function that returns table in SQL

1 Answers1