Stack Overflow
Stack Overflow

Questions tagged [table-functions]

A function in a relational database system that does not return a single value or row, but an arbitrary number of them. In SQL, a call to a table function can usually be used instead of a table.

A function in a system that does not return a single value or , but an arbitrary number of them.

In , a call to a table function can usually be used instead of a table.

Table functions go by different names in different relational database systems, such as in .

94 questions
133
votes
2 answers

Declare variable in table valued function

How can I declare a variable in a table valued function?
esquare
  • 3,947
  • 10
  • 34
  • 37
4
votes
2 answers

Why is the performance of table value function better than select direct statement?

I use AdventureWorks2012 and do a test. and my question: Why is SELECT statement directly performance lower than table values function. I only put SELECT statemnt into table value function and Completely opposite performance. CREATE FUNCTION…
rony Tran
  • 105
  • 5
4
votes
0 answers

table valued function python

How to call table value function in python ? Tried In [73]: cur.callproc('fn_',('blah','12/31/2013','9/13/2014')) but doesn't work Error: MSSQLDatabaseException: (2809, "The request for procedure 'fn_' failed because 'fn_' is a table valued…
user1723988
  • 51
  • 6
4
votes
2 answers

How to include null values in `tablefunc` query in postgresql?

I'm trying to use the crosstab function in postgresql to create a pivot table. However, I'm having difficulty understanding how to structure my SQL within the query. My data consists of four columns and looks like this: I create this table using…
djq
  • 14,810
  • 45
  • 122
  • 157
3
votes
2 answers

COUNTIF in Tableau?

I have a simple COUNTIF task in Excel that is proving rather difficult to replicate in Tableau... This is the data: ID Metric Scope DynamicCalc 1 A1 TRUE X 1 B1 FALSE X 2 B1 TRUE X 2 A1 FALSE X 2 …
Pavel
  • 53
  • 1
  • 2
  • 8
3
votes
2 answers

PostgreSQL 9.3: Pivot table query

I want to show the pivot table(crosstab) for the given below table. Table: Employee CREATE TABLE Employee ( Employee_Number varchar(10), Employee_Role varchar(50), Group_Name varchar(10) ); Insertion: INSERT INTO Employee VALUES('EMP101','C#…
MAK
  • 6,824
  • 25
  • 74
  • 131
3
votes
1 answer

Callback function in Oracle PL/SQL

I have been doing some data transformation/processing in PL/SQL and I want to eliminate duplicate code from my package. This are the relevant code parts. Input type for the table function: type t_legs_cur is ref cursor return legs%rowtype; …
bpgergo
  • 15,669
  • 5
  • 44
  • 68
2
votes
2 answers

Randomizing SQL data with data from table function

I trying to make a sql script that will randomize the city, state, and zip code of a "members" table. I have made a table function that returns a single row with columns "city", "state" and "zip" taken from another database at random (via a view).…
mtmurdock
  • 12,756
  • 21
  • 65
  • 108
2
votes
2 answers

Oracle - Grouping Sets and Pipelined Table Functions (expected NUMBER got ROW)

I'm working on a report with summary logic using GROUPING SETS, but I'm getting this error: SELECT c1, c2, c3, SUM(c4) AS MySum FROM TABLE(get_data()) src GROUP BY GROUPING SETS ((c1, c2, c3), (c1, c2), c1, c2,…
ravioli
  • 3,749
  • 3
  • 14
  • 28
2
votes
1 answer

Insert into table collection type in table function without using explicit cursor in PL/SQL

I write the following code in PL/SQL and it works: declare type deliveryStat_o IS record ( warehouseName varchar2(20), shipMode char(30), thirty_days number, sixty_days number, ninety_days number, oneTwenty_days number, veryLate…
surabhi gupta
  • 65
  • 1
  • 1
  • 9
2
votes
2 answers

Dynamic Oracle Table Function for use in Tableau

We have a large amount of data in an Oracle 11g server. Most of the engineers use Tableau for visualizing data, but there is currently not a great solution for visualizing straight from the Oracle server because of the structure of the database.…
HudsonMC
  • 170
  • 2
  • 9
2
votes
2 answers

In PL/SQL how do you return the results of a query from a User-Defined Function?

I'm coming to Oracle 11g from SQL Server, and I'm trying to use SQL Developer to create a simple function (getIDs) that will return the results of a query (SELECT id FROM employee). Nothing that I've found on the internet seems to work. Here is the…
Steven Edmunds
  • 315
  • 6
  • 14
1
vote
1 answer

I am trying to create a UDTF in Snowflake but I get an error on RETURNS TABLE even though it matches the syntax

CREATE OR REPLACE FUNCTION DQ_IS_NULL ("SCHEMA_NAME" VARCHAR(500), "TABLE_NAME" VARCHAR(500), "COLUMN_NAME" VARCHAR(500)) RETURNS TABLE (TABLE_NME VARCHAR(500), COLUMN_NME VARCHAR(500), ISSUE_CODE VARCHAR(100), PARAMETERS VARCHAR(500), VALUE VARCHAR…
Alex Markides
  • 51
  • 5
1
vote
1 answer

bigquery sql table function with string interpolation

I am trying to write a BigQuery SQL function / stored procedure / table function that accepts as input: a INT64 filter for the WHERE clause, a table name (STRING type) as fully qualified name e.g. project_id.dataset_name.table_name The idea is to…
TPPZ
  • 4,447
  • 10
  • 61
  • 106
1
vote
1 answer

Unexpected behaviour using table functions over multivalued columns with same length

Using table functions I thought that you get new rows associated with the current row, each new row may have more than one column, and each column contains a single value of the values contained in the expression referenced (with it is evaluated…
Victor
  • 3,841
  • 2
  • 37
  • 63
1
2 3 4 5 6 7