• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

Returning a Dynamic Table

I am trying to create a return table that has dynamic colums. I have done some researching and found this code
/*
CREATE TABLE #fundinfo
(
onum int,
fund varchar(30),
amt int
)
INSERT INTO #fundinfo VALUES (1, 'ABCDfund', 45000)
INSERT INTO #fundinfo VALUES (1, 'XYXfund', 345600)
INSERT INTO #fundinfo VALUES (1, 'QWERFund', 890000)
INSERT INTO #fundinfo VALUES (2, 'ABCDfund', 89000)
INSERT INTO #fundinfo VALUES (2, 'XYXfund', 34440)
INSERT INTO #fundinfo VALUES (2, 'QWERFund', 100000)

SELECT * FROM #fundinfo
*/


DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(500)
SET @fieldlist = ''

SELECT @fieldlist = @fieldlist + ', ' + distinctfund + ' int'
FROM (SELECT DISTINCT(fund) as distinctfund FROM #fundinfo) a

SET @sqlstmt = 'CREATE TABLE tmp_output (onum int' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt


DECLARE @order int
DECLARE @fund varchar(30)
DECLARE @amt int

DECLARE @insertcols varchar(300)
DECLARE @insertvals varchar(300)


DECLARE c_Order CURSOR FOR SELECT DISTINCT(onum) distinctonum FROM #fundinfo
OPEN c_Order
FETCH NEXT FROM c_Order INTO @order
WHILE @@FETCH_STATUS = 0
BEGIN

SET @insertcols = ''
SET @insertvals = ''
SELECT @insertcols = @insertcols + ',' + fund,
     @insertvals = @insertvals + ', ' + CAST(amt as varchar(10))
FROM #fundinfo WHERE onum = @order
     
     set @sqlstmt = 'INSERT INTO tmp_output (onum' + @insertcols + ') VALUES (' + CAST(@order as varchar(10)) + @insertvals + ')'
     EXEC sp_executesql @sqlstmt
     
     FETCH NEXT FROM c_Order INTO @order
END
CLOSE c_Order
DEALLOCATE c_Order

SELECT * FROM tmp_output

DROP TABLE tmp_output

It seems to do what I am looking for but i have not been able to fine tune it to do what i need it to. Basically, i have two tables in MSSQL.

First is TBL_EVNT_SUP_SET (
      [EVNT_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [STND_NUM] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [SUP_FNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [SUP_LNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [MGR_FNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [MGR_LNAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Notes] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Misc] [numeric](4, 0) NULL ,
      [Checked] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Changed_By] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Last_Changed] [datetime] NULL )

Second is TBL_SUP_PERF (
      [EVNT_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [STND_NUM] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [POINT_ID] [numeric](4, 0) NOT NULL ,
      [POINT_DESC] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [POINT_VALUE] [numeric](4, 0) NOT NULL ,
      [Checked] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Changed_By] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Last_Changed] [datetime] NULL
What I want is a table to be created using the POINT_DESC values as columns and have the value for each stand for every event. There can be an indefinite number of these and I dont care much it they hold null values when they are not being used. No mathmatical operations have to be done on this information as of yet, this will just allow me to export to create a canned report with the info into an excel spreadsheet. Any help with this would be greatly appreciated.
I've got the code far enough that it creates the table, I just don't know how to populate it. This is what I have thus far
DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(500)
SET @fieldlist = ''

SELECT @fieldlist = @fieldlist + ', ' + REPLACE(distinctpoint, ' ', '_') + ' int'
FROM (SELECT DISTINCT(POINT_DESC) as distinctpoint FROM ARCSTRACKER.tracker.TBL_SUP_PERF) a

SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
/*

DECLARE @EVNT_Id CHAR(10)
DECLARE @STND_NUM CHAR(5)
DECLARE @fund varchar(35)
DECLARE @amt int

DECLARE @insertcols varchar(300)
DECLARE @insertvals varchar(300)


DECLARE c_Order CURSOR FOR SELECT DISTINCT(EVNT_ID) distinctevent, DISTINCT(STND_NUM) distinctstndnum FROM ARCSTRACKER.tracker.TBL_SUP_PERF
OPEN c_Order
FETCH NEXT FROM c_Order INTO @EVNT_Id, @STND_NUM
WHILE @@FETCH_STATUS = 0
BEGIN

SET @insertcols = ''
SET @insertvals = ''
SELECT @insertcols = @insertcols + ',' + fund,
     @insertvals = @insertvals + ', ' + CAST(amt as varchar(10))
FROM #fundinfo WHERE onum = @order
     
     set @sqlstmt = 'INSERT INTO tmp_output (onum' + @insertcols + ') VALUES (' + CAST(@order as varchar(10)) + @insertvals + ')'
     EXEC sp_executesql @sqlstmt
     
     FETCH NEXT FROM c_Order INTO @order
END
CLOSE c_Order
DEALLOCATE c_Order
*/
SELECT * FROM tmp_output

DROP TABLE tmp_output

Thanks in advance, Joe.
0
arajoe
Asked:
arajoe
  • 12
  • 8
1 Solution
 
Scott PletcherSenior DBACommented:
OK, haven't had a chance to test any of this, but here goes :-)

First, we have to make *sure* that the column names of the points and the values line up, so we have to order the points.  I've used a temp table for that.  This makes several additions to your code for creating the table, as shown below, and one change, as noted below:

-- add all these lines

SET NOCOUNT ON  --optional

IF OBJECT_ID('tempdb.dbo.#distinctPoints') IS NOT NULL
    DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
    ident INT IDENTITY(1, 1),
    distinctPoint VARCHAR(35),
    PRIMARY KEY (ident)
    )
DECLARE @numDistinctPoints SMALLINT

INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_desc, ' ', '_'))
FROM ARCSTRACKER.tracker.TBL_SUP_PERF
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT

-- change to current code
SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident

--...other CREATE TABLE code as you originally had it


Then for the INSERT, generate a single SQL statement (rather than a cursor) to load the table, something like below:


SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
    'SELECT ess.evnt_id, MAX(ess.stnd_num), MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
    '    MAX(ess.mgr_fname), MAX(ess.mgr_lname),  MAX(ess.notes)'
DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
    SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + '    MAX(CASE WHEN sp.point_desc = ''' +
        (SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum) + '''' +
        ' THEN point_value ELSE NULL END)'
    SET @distinctPointNum = @distinctPointNum + 1
END --WHILE
SET @sqlStmt = @sqlStmt + ' ' +
    'FROM tbl_evnt_sup_set ess ' +
    'INNER JOIN ARCSTRACKER.tracker.TBL_SUP_PERF sp ON sp.evnt_id = ess.evnt_id ' +
    '    /* AND sp.stnd_num = ess.stnd_num */ '
PRINT @sqlStmt  --optional
EXEC(@sqlStmt)

0
 
arajoeAuthor Commented:
REVISED CODE

-- add all these lines

SET NOCOUNT ON  --optional

IF OBJECT_ID('tempdb.dbo.#distinctPoints') IS NOT NULL
    DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
    ident INT IDENTITY(1, 1),
    distinctPoint VARCHAR(35),
    PRIMARY KEY (ident)
    )
DECLARE @numDistinctPoints SMALLINT

INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_desc, ' ', '_'))
FROM ARCSTRACKER.tracker.TBL_SUP_PERF
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT

DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(3000)
SET @fieldlist = ''

-- change to current code
SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident



/*
SELECT @fieldlist = @fieldlist + ', ' + REPLACE(distinctpoint, ' ', '_') + ' int'
FROM (SELECT DISTINCT(POINT_DESC) as distinctpoint FROM ARCSTRACKER.tracker.TBL_SUP_PERF) a
*/
SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt
/*

DECLARE @EVNT_Id CHAR(10)
DECLARE @STND_NUM CHAR(5)
DECLARE @fund varchar(35)
DECLARE @amt int

DECLARE @insertcols varchar(300)
DECLARE @insertvals varchar(300)


DECLARE c_Order CURSOR FOR SELECT DISTINCT(EVNT_ID) distinctevent, DISTINCT(STND_NUM) distinctstndnum FROM ARCSTRACKER.tracker.TBL_SUP_PERF
OPEN c_Order
FETCH NEXT FROM c_Order INTO @EVNT_Id, @STND_NUM
WHILE @@FETCH_STATUS = 0
BEGIN

SET @insertcols = ''
SET @insertvals = ''
SELECT @insertcols = @insertcols + ',' + fund,
     @insertvals = @insertvals + ', ' + CAST(amt as varchar(10))
FROM #fundinfo WHERE onum = @order
     
     set @sqlstmt = 'INSERT INTO tmp_output (onum' + @insertcols + ') VALUES (' + CAST(@order as varchar(10)) + @insertvals + ')'
     EXEC sp_executesql @sqlstmt
     
     FETCH NEXT FROM c_Order INTO @order
END
CLOSE c_Order
DEALLOCATE c_Order
*/
SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
    'SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
    '    MAX(ess.mgr_fname), MAX(ess.mgr_lname),  MAX(ess.notes)'
DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
    SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + '    MAX(CASE WHEN sp.point_desc = ''' +
        (SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum) + '''' +
        ' THEN point_value ELSE NULL END)'
    SET @distinctPointNum = @distinctPointNum + 1
END --WHILE
SET @sqlStmt = @sqlStmt + ' ' +
    'FROM tbl_evnt_sup_set ess ' +
    'INNER JOIN ARCSTRACKER.tracker.TBL_SUP_PERF sp ON sp.evnt_id = ess.evnt_id ' +
    '    /* AND sp.stnd_num = ess.stnd_num */ '

SET @sqlStmt = @sqlStmt + ' ' + 'GROUP BY ess.evnt_id, ess.stnd_num'

PRINT @sqlStmt  --optional
EXEC(@sqlStmt)

SELECT * FROM tmp_output

DROP TABLE tmp_output

That all worked to add everything to the table except for the actual point values. It is giving me null in all of the value fields.
0
 
arajoeAuthor Commented:
Cleaned the code a little

SET NOCOUNT ON

IF OBJECT_ID('tempdb.dbo.#distinctPoints') IS NOT NULL
    DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
    ident INT IDENTITY(1, 1),
    distinctPoint VARCHAR(35),
    PRIMARY KEY (ident)
    )
DECLARE @numDistinctPoints SMALLINT

INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_desc, ' ', '_'))
FROM ARCSTRACKER.tracker.TBL_SUP_PERF
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT

DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(3000)
SET @fieldlist = ''

SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident

SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt

SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
    'SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
    '    MAX(ess.mgr_fname), MAX(ess.mgr_lname),  MAX(ess.notes)'
DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
    SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + '    MAX(CASE WHEN sp.point_desc = ''' +
        (SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum) + '''' +
        ' THEN point_value ELSE NULL END)'
    SET @distinctPointNum = @distinctPointNum + 1
END
SET @sqlStmt = @sqlStmt + ' ' +
    'FROM tbl_evnt_sup_set ess ' +
    'INNER JOIN ARCSTRACKER.tracker.TBL_SUP_PERF sp ON sp.evnt_id = ess.evnt_id ' +
    '    /* AND sp.stnd_num = ess.stnd_num */ '

SET @sqlStmt = @sqlStmt + ' ' + 'GROUP BY ess.evnt_id, ess.stnd_num'

PRINT @sqlStmt  --optional
EXEC(@sqlStmt)

SELECT * FROM tmp_output

DROP TABLE tmp_output
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Scott PletcherSenior DBACommented:
Did you print out the SQL statement?  Do the values in it match values actually in the table?
0
 
arajoeAuthor Commented:
SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),
    MAX(ess.mgr_fname), MAX(ess.mgr_lname),  MAX(ess.notes),
    MAX(CASE WHEN sp.point_desc = 'ALL_PAPERWORK_SIGNED' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'BEFORE_EVENT_TRANSFERS_ATTACHED' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'DURING_EVENT_TRANSFERS_ATTACHED' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'EMPLOYEE_MEAL_SHEET_COMPLETE' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'EMPLOYEE_MEAL_SHEET_MATCHES_STAND' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'SPOILAGE_SHEET_COMPLETE' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'SPOILAGE_SHEET_MATHCES_STAND_SHEET' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'STAND_MANAGER_CHECKLIST_COMPLETE' THEN point_value ELSE NULL END) FROM tbl_evnt_sup_set ess INNER JOIN ARCSTRACKER.tracker.TBL_SUP_PERF sp ON sp.evnt_id = ess.evnt_id     /* AND sp.stnd_num = ess.stnd_num */  GROUP BY ess.evnt_id, ess.stnd_num
Warning: Null value is eliminated by an aggregate or other SET operation.

is what i get from the print.
0
 
arajoeAuthor Commented:
Working Code:
SET NOCOUNT ON

IF OBJECT_ID('tempdb.dbo.#distinctPoints') IS NOT NULL
    DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
    ident INT IDENTITY(1, 1),
    distinctPoint VARCHAR(35),
    PRIMARY KEY (ident)
    )
DECLARE @numDistinctPoints SMALLINT

INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_desc, ' ', '_'))
FROM ARCSTRACKER.tracker.TBL_SUP_PERF
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT

DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(3000)
SET @fieldlist = ''

SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident

SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt

SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
    'SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
    '    MAX(ess.mgr_fname), MAX(ess.mgr_lname),  MAX(ess.notes)'
DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
    SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + '    MAX(CASE WHEN sp.point_desc = ''' +
        REPLACE((SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum), '_', ' ') + '''' +
        ' THEN point_value ELSE NULL END)'

    SET @distinctPointNum = @distinctPointNum + 1

END

SET @sqlStmt = @sqlStmt + ' ' +
    'FROM tbl_evnt_sup_set ess ' +
    'INNER JOIN ARCSTRACKER.tracker.TBL_SUP_PERF sp ON sp.evnt_id = ess.evnt_id ' +
    '    /* AND sp.stnd_num = ess.stnd_num */ '

SET @sqlStmt = @sqlStmt + ' ' + 'GROUP BY ess.evnt_id, ess.stnd_num'

SET @sqlStmt = @sqlStmt + CHAR(13)

PRINT @sqlStmt  --optional
EXEC(@sqlStmt)

SELECT * FROM tmp_output

DROP TABLE tmp_output

Got it I think. I had to use the replace command again to put the spaces back into the description and take out the _ I made it put in to make them valid column names. I'll post back after I check the data and let you know how it goes. Thanks, Joe.
0
 
Scott PletcherSenior DBACommented:
Sorry, was busy earlier.  Also sorry about, D'OH, not thinking about having to re-replace the _/spaces.  Thks for the grade though!
0
 
arajoeAuthor Commented:
No prob. If it wasn't for your help, I would have been nowhere.
0
 
arajoeAuthor Commented:

CREATE  PROCEDURE tracker.dbp_TheCarson (@T0 CHAR(10), @FROM CHAR(10))
 AS
SET NOCOUNT ON

IF OBJECT_ID('tempdb.dbo.#distinctPoints') IS NOT NULL
    DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
    ident INT IDENTITY(1, 1),
    distinctPoint VARCHAR(35),
    PRIMARY KEY (ident)
    )
DECLARE @numDistinctPoints SMALLINT

/*
--
--TAKE OUT
--

DECLARE @T0 CHAR(10)
DECLARE @FROM CHAR(10)

SET @T0 =   0000000000
SET @FROM = 9999999999

--
--
--
*/

INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_desc, ' ', '_'))
FROM ARCSTRACKER.tracker.TBL_SUP_PERF
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT

DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(4000)
SET @fieldlist = ''

SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident

SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150),  OVER_SHORT_POINTS NUMERIC(11,8)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt

SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
    'SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
    '    MAX(ess.mgr_fname), MAX(ess.mgr_lname),  MAX(ess.notes), ' + CHAR(13) + '    (SELECT OS_RATING FROM fn_Calc_Stnd_OS_Perf sp.evnt_id, sp.stnd_num )'

DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
    SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + '    MAX(CASE WHEN sp.point_desc = ''' +
        REPLACE((SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum), '_', ' ') + '''' +
        ' THEN point_value ELSE NULL END)'

    SET @distinctPointNum = @distinctPointNum + 1

END

SET @sqlStmt = @sqlStmt + ' ' +  CHAR(13)
    + 'FROM tbl_evnt_sup_set ess ' + CHAR(13)
    + 'INNER JOIN ARCSTRACKER.tracker.TBL_SUP_PERF sp ON'
    + CHAR(13) + '         sp.evnt_id = ess.evnt_id'
    + CHAR(13) + '     AND sp.stnd_num = ess.stnd_num  '
    + CHAR(13) + '     AND ess.evnt_id <= ''' + @T0   + ''''
    + CHAR(13) + '     AND ess.evnt_id >= ''' + @FROM + ''''

SET @sqlStmt = @sqlStmt + ' '
    + CHAR(13) + 'GROUP BY ess.evnt_id, ess.stnd_num'
    + CHAR(13) + 'ORDER BY ess.evnt_id, ess.stnd_num'

--SET @sqlStmt = @sqlStmt + CHAR(13)

PRINT @sqlStmt  --optional

EXEC(@sqlStmt)

SELECT * FROM tmp_output

DROP TABLE tmp_output
GO
 One more thing. I am trying to add another column now that gets its value from a function. When I run the code, I get an error saying incorrect syntax near '.'. I can only assume it is from the aruguments the function takes.
0
 
Scott PletcherSenior DBACommented:
Perhaps; I can't really tell without seeing the code.  Also, remember to include the user name, normally dbo, on all uses of user-defined functions.  For example:

SELECT dbo.userFunc1(param1, param2)

instead of:

SELECT userFunc1(param1, param2)  --<<-- SQL does not accept
0
 
arajoeAuthor Commented:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
INSERT INTO tmp_output
SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),
    MAX(ess.mgr_fname), MAX(ess.mgr_lname),  MAX(ess.notes),
    (SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Perf sp.evnt_id, sp.stnd_num ),
    MAX(CASE WHEN sp.point_desc = 'ALL PAPERWORK SIGNED' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'BEFORE EVENT TRANSFERS ATTACHED' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'DURING EVENT TRANSFERS ATTACHED' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'EMPLOYEE MEAL SHEET COMPLETE' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'EMPLOYEE MEAL SHEET MATCHES STAND' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'SPOILAGE SHEET COMPLETE' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'SPOILAGE SHEET MATHCES STAND SHEET' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'STAND MANAGER CHECKLIST COMPLETE' THEN point_value ELSE NULL END)
FROM tbl_evnt_sup_set ess
INNER JOIN ARCSTRACKER.tracker.TBL_SUP_PERF sp ON
         sp.evnt_id = ess.evnt_id
     AND sp.stnd_num = ess.stnd_num  
     AND ess.evnt_id <= '9999999999'
     AND ess.evnt_id >= '0         '
GROUP BY ess.evnt_id, ess.stnd_num
ORDER BY ess.evnt_id, ess.stnd_num


Thats the print out. This is the code to the Stored Prcedure that we finished yesterday:


CREATE  PROCEDURE tracker.dbp_TheCarson (@T0 CHAR(10), @FROM CHAR(10))
 AS
SET NOCOUNT ON

IF OBJECT_ID('tempdb.dbo.#distinctPoints') IS NOT NULL
    DROP TABLE #distinctPoints
CREATE TABLE #distinctPoints (
    ident INT IDENTITY(1, 1),
    distinctPoint VARCHAR(35),
    PRIMARY KEY (ident)
    )
DECLARE @numDistinctPoints SMALLINT

/*
--
--TAKE OUT
--

DECLARE @T0 CHAR(10)
DECLARE @FROM CHAR(10)

SET @T0 =   0000000000
SET @FROM = 9999999999

--
--
--
*/

INSERT INTO #distinctPoints
SELECT DISTINCT(REPLACE(point_desc, ' ', '_'))
FROM ARCSTRACKER.tracker.TBL_SUP_PERF
ORDER BY REPLACE(point_desc, ' ', '_')
SET @numDistinctPoints = @@ROWCOUNT

DECLARE @fieldlist nvarchar(300)
DECLARE @sqlstmt nvarchar(4000)
SET @fieldlist = ''

SELECT @fieldlist = @fieldlist + ', ' + distinctpoint + ' int'
FROM #distinctPoints
ORDER BY ident

SET @sqlstmt = 'CREATE TABLE tmp_output (EVNT_ID CHAR(10), STND_NUM CHAR(5), SUP_FNAME VARCHAR(20), SUP_LNAME VARCHAR(20), MGR_FNAME VARCHAR(20), MGR_LNAME VARCHAR(20), NOTES VARCHAR(150),  OVER_SHORT_POINTS NUMERIC(11,8)' + @fieldlist + ')'
EXEC sp_executesql @sqlstmt

SET @sqlstmt = 'INSERT INTO tmp_output ' + CHAR(13) +
    'SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),' + CHAR(13) +
    '    MAX(ess.mgr_fname), MAX(ess.mgr_lname),  MAX(ess.notes), ' + CHAR(13) + '    (SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Perf sp.evnt_id, sp.stnd_num )'

DECLARE @distinctPointNum SMALLINT
SET @distinctPointNum = 1
WHILE @distinctPointNum <= @numDistinctPoints
BEGIN
    SET @sqlStmt = @sqlStmt + ', ' + CHAR(13) + '    MAX(CASE WHEN sp.point_desc = ''' +
        REPLACE((SELECT distinctPoint FROM #distinctPoints WHERE ident = @distinctPointNum), '_', ' ') + '''' +
        ' THEN point_value ELSE NULL END)'

    SET @distinctPointNum = @distinctPointNum + 1

END

SET @sqlStmt = @sqlStmt + ' ' +  CHAR(13)
    + 'FROM tbl_evnt_sup_set ess ' + CHAR(13)
    + 'INNER JOIN ARCSTRACKER.tracker.TBL_SUP_PERF sp ON'
    + CHAR(13) + '         sp.evnt_id = ess.evnt_id'
    + CHAR(13) + '     AND sp.stnd_num = ess.stnd_num  '
    + CHAR(13) + '     AND ess.evnt_id <= ''' + @T0   + ''''
    + CHAR(13) + '     AND ess.evnt_id >= ''' + @FROM + ''''

SET @sqlStmt = @sqlStmt + ' '
    + CHAR(13) + 'GROUP BY ess.evnt_id, ess.stnd_num'
    + CHAR(13) + 'ORDER BY ess.evnt_id, ess.stnd_num'

--SET @sqlStmt = @sqlStmt + CHAR(13)

PRINT @sqlStmt  --optional

EXEC(@sqlStmt)

SELECT * FROM tmp_output

DROP TABLE tmp_output
GO
 and this is the code to the function that it calls:

CREATE function tracker.fn_Calc_Stnd_OS_Perf ( @EventId varchar(10), @Stnd_Num CHAR(5))
RETURNS  
@RetTable TABLE(
   OS_Rating NUMERIC(11,8)
) AS
BEGIN

--DECLARE @Item_Gross_Sales NUMERIC(11,2)
DECLARE @stnd_cash NUMERIC(11,2)
DECLARE @stnd_OS NUMERIC(11,2)
DECLARE @Perc NUMERIC(11,8)
DECLARE @Ratio NUMERIC(11,8)
---DECLARE @OS_Pts_Max NUMERIC(11,8)

SET @Perc = (
      (SELECT
            PERC_VALUE
      FROM
            ARCSTRACKER.tracker.TBL_MST_OS_PERC P
      WHERE
            P.Last_Changed = (
                  SELECT
                        MAX(Last_Changed)
                  FROM
                        ARCSTRACKER.tracker.TBL_MST_OS_PERC)
      ) / 100)
/*
SET @OS_Pts_Max = (
      SELECT
            PERC_POINT_VALUE
      FROM
            ARCSTRACKER.tracker.TBL_MST_OS_PERC)
      WHERE
            LAST_CHANGED = (
                  SELECT
                        MAX(LAST_CHANGED)
                  FROM
                        ARCSTRACKER.tracker.tbl_mst_pts)) */

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--GET THE CASH DATA
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*IF(
      (SELECT
            CSUM_CROOM_TOTAL
      FROM
            NEWARCS.dbnewarcs1.TblCashSum  B
      WHERE
            Csum_stnd_num = @Stnd_Num
            AND Csum_Evnt_Id = @EventId) <> NULL)

      BEGIN*/

      SET @stnd_cash =
            (SELECT
                  CSUM_CROOM_TOTAL
            FROM
                  NEWARCS.dbnewarcs1.TblCashSum  B
            WHERE
                  Csum_stnd_num = @Stnd_Num
                  AND Csum_Evnt_Id = @EventId
            )
/*
--Stand Invetory Sales
IF (
      (SELECT
            SUM((Esum_Item_Price * Esum_Tot_Sale)) AS SALES
      FROM
            NEWARCS.dbnewarcs1.TblEvntSum
      WHERE
            Esum_Stnd_Num = @Stnd_Num
            AND Esum_Evnt_Id = @EventId)<>NULL)

      BEGIN
            SET @Item_Gross_Sales =
                  (SELECT
                        SUM((Esum_Item_Price * Esum_Tot_Sale)) AS SALES
                  FROM
                        NEWARCS.dbnewarcs1.TblEvntSum
                  WHERE
                        Esum_Stnd_Num = @Stnd_Num
                        AND Esum_Evnt_Id = @EventId)
      END
ELSE
      BEGIN
            SET @Item_Gross_Sales = 0
      END*/

SET @stnd_OS = (@stnd_cash -
      (SELECT
            SUM((Esum_Item_Price * Esum_Tot_Sale)) AS SALES
      FROM
            NEWARCS.dbnewarcs1.TblEvntSum
      WHERE
            Esum_Stnd_Num = @Stnd_Num
            AND Esum_Evnt_Id = @EventId) )

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--MAKE THE CALCULATIONS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SET @Ratio =  (
      SELECT
            PERC_POINT_VALUE
      FROM
            ARCSTRACKER.tracker.TBL_MST_OS_PERC P
      WHERE
            P.Last_Changed = (
                  SELECT
                        MAX(Last_Changed)
                  FROM
                        ARCSTRACKER.tracker.TBL_MST_OS_PERC))

If ( ABS(@stnd_OS) > (@stnd_cash * @Perc) )
      BEGIN
            SET @Ratio = 0
      END
/*ELSE
      BEGIN
            SET @Ratio = @OS_Pts_Max
      END

IF ( (@Item_Gross_Sales = 0) OR (@stnd_cash = 0) )
      BEGIN
            SET @Ratio = NULL
      END

ELSE
      BEGIN
            If (@Ratio > @OS_Pts_Max)
                  BEGIN
                        SET @Ratio = @OS_Pts_Max
                  END
            
            If (@Ratio < 0)
                  BEGIN
                        SET @Ratio = 0
                  END
      END
*/

INSERT INTO @RetTable
      VALUES(@Ratio)



RETURN

END
0
 
Scott PletcherSenior DBACommented:
I don't think this function:

(SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Perf sp.evnt_id, sp.stnd_num ),

can be used in that context, even with the required parentheses around the parameters:

(SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Perf (sp.evnt_id, sp.stnd_num)),
0
 
arajoeAuthor Commented:
I know almost nothing of SQL. Any ideas on how to make it?
0
 
Scott PletcherSenior DBACommented:
Actually I mis-read it the first time.  If the function returns a single value rather than a table of values, and it seems as if it probably does, try this:


max((SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Perf (sp.evnt_id, sp.stnd_num) )),


and let me mknow what u get.
0
 
arajoeAuthor Commented:
same deal
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
INSERT INTO tmp_output
SELECT ess.evnt_id, ess.stnd_num, MAX(ess.sup_fname), MAX(ess.sup_lname),
    MAX(ess.mgr_fname), MAX(ess.mgr_lname),  MAX(ess.notes),
    max((SELECT OS_RATING FROM tracker.fn_Calc_Stnd_OS_Perf (ess.evnt_id, ess.stnd_num) )),
    MAX(CASE WHEN sp.point_desc = 'ALL PAPERWORK SIGNED' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'BEFORE EVENT TRANSFERS ATTACHED' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'DURING EVENT TRANSFERS ATTACHED' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'EMPLOYEE MEAL SHEET COMPLETE' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'EMPLOYEE MEAL SHEET MATCHES STAND' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'SPOILAGE SHEET COMPLETE' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'SPOILAGE SHEET MATHCES STAND SHEET' THEN point_value ELSE NULL END),
    MAX(CASE WHEN sp.point_desc = 'STAND MANAGER CHECKLIST COMPLETE' THEN point_value ELSE NULL END)
FROM tbl_evnt_sup_set ess
INNER JOIN ARCSTRACKER.tracker.TBL_SUP_PERF sp ON
         sp.evnt_id = ess.evnt_id
     AND sp.stnd_num = ess.stnd_num  
     AND ess.evnt_id <= '9999999999'
     AND ess.evnt_id >= '0         '
GROUP BY ess.evnt_id, ess.stnd_num
ORDER BY ess.evnt_id, ess.stnd_num
0
 
Scott PletcherSenior DBACommented:
DOUBLE D'OH:

I read it right the first time.  This is trying to return a table of values based on inputs from a table -- I don't think you can do that.  Even this shouldn't work:

max((SELECT MAX(OS_RATING) FROM tracker.fn_Calc_Stnd_OS_Perf (ess.evnt_id, ess.stnd_num) )),
0
 
arajoeAuthor Commented:
I was toying with trying to make it return just a single value, but I couldn't get it to. If I make it do that, will it work?
0
 
Scott PletcherSenior DBACommented:
No, not really.  If you can re-work the function to return a single value rather than a table, that is, a scalar rather than table function, then it should work fine.
0
 
arajoeAuthor Commented:
Well the way it works is there is actually ony one row in that table so it is just one value. I don't know how to make it return just, lets say, a numeric value.
0
 
arajoeAuthor Commented:
Nevermind, Figured it out. Thanks again.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now