June 13, 2008 at 12:25 pm
I have a stored procedure that does not require any parameters and returns about 7 columns. It works fine when executing the proc on the data tab. But when I try to design a report and I want to assign one of the fields to a textbox or whatever, and they are not listed in the expression dialog box. What gives?
June 13, 2008 at 12:53 pm
Can you post the stored proc? Are you missing all the fields or only certain ones?
June 13, 2008 at 2:36 pm
Here is the Proc:
It first retrieves a sku number and date from a SQL Server table, and puts them in a temp table. Then, it hits an Oracle box (through a linked server) and joins on this sku number.
CREATE PROCEDURE dbo.usp_GetNewSkusInDC
AS
--Allow dirty reads
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--Create a temp table of alpha skus where First Stocking Date is NULL or less than 5 days of current date.
SELECT RIGHT('00000000' + CAST(Short_Sku AS varchar), 8) AS sku_alpha,
First_Stocking_Date
INTO #sku_table
FROM IMAX_SKU_STORE.dbo.Sku_Store AS Sku_Store
WHERE (Store = 6001) AND
((DATEDIFF(day, First_Stocking_Date, CURRENT_TIMESTAMP) < 5) OR First_Stocking_Date IS NULL)
ORDER BY sku_alpha
SELECT INVENTORY.PRODUCT_ID,
INVENTORY.PHYSICAL_LOCATION_NO,
INVENTORY.INVENTORY_TYPE,
INVENTORY.ONHAND_QUANTITY,
LOCATION.ZONE_CODE,
PRODUCT_MASTER.DESCRIPTION,
#sku_table.First_Stocking_Date
FROM WMSCOL..MOVE.INVENTORY INVENTORY
INNER JOIN WMSCOL..MOVE.LOCATION LOCATION ON INVENTORY.PHYSICAL_LOCATION_NO = LOCATION.LOCATION_NO
INNER JOIN WMSCOL..MOVE.PRODUCT_MASTER PRODUCT_MASTER ON INVENTORY.PRODUCT_ID = PRODUCT_MASTER.PRODUCT_ID
INNER JOIN #sku_table ON INVENTORY.PRODUCT_ID = #sku_table.sku_alpha
WHERE INVENTORY.INVENTORY_TYPE IN ('REC', 'LOC') AND
EXISTS (select 1 from #sku_table where inventory.product_id = sku_alpha)
ORDER BY INVENTORY.PRODUCT_ID
Drop Table #sku_table
GO
exec usp_UtlGrantNecessaryPermissions 'usp_GetNewSkusInDC'
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply