ALL ACCPAC ITEMS OPTIONAL FIELDS IN ONE GO

Hello,


Today I was just working on some report and for the requirement I design a query that give all optional fields attached in item master in one go. We simply need to run the query and we can get the result in one table where we can get the result like Item number, item description, Optional fields as columns and there respective values.
Run the following query in any Accpac Database that contain Inventory data.


DECLARE @i INT

DECLARE @STR VARCHAR(2000)

Declare @MAINSTR VARCHAR(8000)

DECLARE @COLNAM VARCHAR(100)

DECLARE @OPTNAM VARCHAR(100)

DECLARE @TEMPTABLE VARCHAR(8000)

BEGIN

SET @i = 0

SET @MAINSTR = 'INSERT INTO ICIO select Itemno,[Desc]'

SET @TEMPTABLE ='CREATE TABLE ICIO(Itemno VARCHAR(22),[Desc] VARCHAR(100)'

PRINT @MAINSTR

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ICIO]') AND type in (N'U'))

DROP TABLE [dbo].[ICIO]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COLNAM]') AND type in (N'U'))

DROP TABLE [dbo].[COLNAM]

CREATE TABLE [dbo].[COLNAM] (COLNAM VARCHAR(200))

WHILE (@i < (select COUNT(distinct(OPTFIELD)) from icitemo))

BEGIN

DELETE FROM COLNAM

SET @STR = 'INSERT INTO COLNAM SELECT TOP 1 OPTFIELD FROM ICITEMO

WHERE

OPTFIELD = (SELECT DISTINCT TOP 1 OPTFIELD FROM ICITEMO WHERE OPTFIELD NOT IN

(SELECT DISTINCT TOP ' + convert(varchar(2), @i) + ' OPTFIELD FROM ICITEMO ORDER BY OPTFIELD)ORDER BY OPTFIELD)

ORDER BY OPTFIELD'

EXECUTE (@STR)

SELECT @OPTNAM = COLNAM FROM COLNAM

SET @MAINSTR = @MAINSTR+', (select [value] from icitemo op1 where op1.itemno=ICITEM.ITEMNO and

OPTFIELD=''' + @OPTNAM +''') as ' + @OPTNAM

SET @TEMPTABLE = @TEMPTABLE + ', '+ @OPTNAM + ' VARCHAR(100)'

SET @i = @i + 1

END

SET @MAINSTR = @MAINSTR + ' from ICITEM'

SET @TEMPTABLE = @TEMPTABLE + ')'

EXECUTE (@TEMPTABLE)

EXECUTE (@MAINSTR)

SELECT * FROM ICIO

END
It will give you the result. Now if lets say we want to use it in a report i.e crystal report. We can not use it directly. So simply create a store procedure of this query and use it in your report.
create proc ICIOPTVAL AS

DECLARE @i INT

DECLARE @STR VARCHAR(2000)

Declare @MAINSTR VARCHAR(8000)

DECLARE @COLNAM VARCHAR(100)

DECLARE @OPTNAM VARCHAR(100)

DECLARE @TEMPTABLE VARCHAR(8000)

BEGIN

SET @i = 0

SET @MAINSTR = 'INSERT INTO ICIO select Itemno,[Desc]'

SET @TEMPTABLE ='CREATE TABLE ICIO(Itemno VARCHAR(22),[Desc] VARCHAR(100)'

PRINT @MAINSTR

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ICIO]') AND type in (N'U'))

DROP TABLE [dbo].[ICIO]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COLNAM]') AND type in (N'U'))

DROP TABLE [dbo].[COLNAM]

CREATE TABLE [dbo].[COLNAM] (COLNAM VARCHAR(200))

WHILE (@i < (select COUNT(distinct(OPTFIELD)) from icitemo))

BEGIN

DELETE FROM COLNAM

SET @STR = 'INSERT INTO COLNAM SELECT TOP 1 OPTFIELD FROM ICITEMO

WHERE

OPTFIELD = (SELECT DISTINCT TOP 1 OPTFIELD FROM ICITEMO WHERE OPTFIELD NOT IN

(SELECT DISTINCT TOP ' + convert(varchar(2), @i) + ' OPTFIELD FROM ICITEMO ORDER BY OPTFIELD)ORDER BY OPTFIELD)

ORDER BY OPTFIELD'

EXECUTE (@STR)

SELECT @OPTNAM = COLNAM FROM COLNAM

SET @MAINSTR = @MAINSTR+', (select [value] from icitemo op1 where op1.itemno=ICITEM.ITEMNO and

OPTFIELD=''' + @OPTNAM +''') as ' + @OPTNAM

SET @TEMPTABLE = @TEMPTABLE + ', '+ @OPTNAM + ' VARCHAR(100)'

SET @i = @i + 1

END

SET @MAINSTR = @MAINSTR + ' from ICITEM'

SET @TEMPTABLE = @TEMPTABLE + ')'

EXECUTE (@TEMPTABLE)

EXECUTE (@MAINSTR)

SELECT * FROM ICIO

END
Hope this will simplify the work of adding lot of optional field in a report.
So, will be back soon with some new thing to share till then bye.
Aggyey