How can I control which dataset gets sent to my table.data?

General Discussion
1

I’m building a SQL query in the data property for a table, where I am first SELECTing the relevant data from my database and then inserting it into a “temp” table called @Raw that I declare as part of the query script. Then I take some of the new data in the @Raw table, and run it through some basic logic, after which it updates the table with the results, where I can then SELECT {all the columns I want, including my new results} from @Raw order by Indx

The problem is that the system is happy with the query as it sits now, but doesn’t show me the data from my Select {stuff} from @Raw (2nd Select statement) it only shows me the data I brought in to Insert into @Raw to work on.

How can I get the SQL query expression to show the data from my second query?

Here’s my code:

set NOCOUNT ON
DECLARE @EID int = {Root Container.dpd_SLAM_Line.selectedValue}

DECLARE @Raw Table
(
Indx int,
ShipCode varchar(255) NULL,
LPCode varchar(255) NULL,
KO_Code varchar(25) NULL,
PackType int NULL,
PackTypeString varchar(6) NULL,
AvgHeight1 int NULL,
AvgHeight2 int NULL,
ScaleScanBox varchar(255) NULL,
ScaleSP varchar(255) NULL,
ExceededSlope varchar(255) NULL,
PositionError varchar(255) NULL,
Weight float(4)
)

–Clear the @Raw Table before adding more data to it
–If (Select count(*) from @Raw) > 0
–delete from @Raw

–Insert new Data in to @Raw from the DB
select top 100 [Index], [Ship_BC], [SPOO_BC], [KO_Code], [PackType], [AvgHeight1], [AvgHeight2], [ScaleScanBox], [ScaleSP], [ExceededSlope], [PositionError] from [Verify_Package_Data] where @EID = [EID] order by [t_stamp] desc
INSERT into @Raw(Indx, ShipCode, LPCode, KO_Code, PackType, AvgHeight1, AvgHeight2, ScaleScanBox, ScaleSP, ExceededSlope, PositionError)
Values(‘Index’, ‘Ship_BC’, ‘SPOO_BC’, ‘KO_Code’, ‘PackType’, ‘AvgHeight1’, ‘AvgHeight2’, ‘ScaleScanBox’, ‘ScaleSP’, ‘ExceededSlope’, ‘PositionError’)

– configure the resulting output for the table to be output to the screen

Update @Raw Set PackTypeString = ‘Flat’ where PackType = 1
Update @Raw Set PackTypeString = ‘Ship’ where PackType = 0

– configure the resulting weight data - get Raw Weight from “Scale Grades” table

UPDATE @Raw Set Weight = (convert(float(4),(Select Weight from Scale_Grades where SPOO_BC = LPCode)) * convert(float(4), 0.0001))

– Send Table data to Screen table view
–Select Indx As “Index”, ShipCode AS “Label BC”, ScaleSP as “sp00 Label”, ScaleScanBox AS “Box DimCode”, Weight AS “Scale Weight”, KO_Code AS “KO Reason”, AvgHeight1 As “Height 1”, AvgHeight2 As “Height 2”, ExceededSlope AS “Exceeded Slope”, PositionError AS “Position Err” from @Raw order by Indx desc
select * from @Raw

2

Jeff, maybe I am missing something, but if I’m reading this right, it sounds like you should do all this work in a stored procedure on the SQL server, passing parameters to it if necessary, then have the last statements in the stored procedure be the select for the data you need for your table.

I like doing things like this in a stored procedure because you can make changes in the stored procedure without having to change anything in your project. The project still calls the same stored procedure, but its results change as you modify it.