Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Performing division from dynamic SQL

Author  Topic 

ayamas
Aged Yak Warrior

552 Posts
Posted - 2008-09-26 : 09:19:05
Hi guys,
My resultset is like this

year testintru1-D testintru2-D txt-p testintru4-D tst-Np/m3
2005 335.00\1000 12.84\1000 14.21 37.31\1000 22.22
2008 9219.00\1000 3.13\1000 11.97 3.92\1000 0.00

I would like to perform the divisions wherever it exists.But the problem is the above resultset comes from a dynamic SQL.I know how to do it if it exists in some table.Is there anyways by which I can put the output of the dynamic sql in a temporary table.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-26 : 09:23:45
yup you can use INSERT INTO #Temp... EXEC
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-26 : 09:25:20
So you will get above resultset as string values?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-09-26 : 09:27:16
Why dynamic SQL?
Here is a start
DECLARE	@Sample TABLE
	(
		year smallint,
		[testintru1-D] varchar(100),
		[testintru2-D] varchar(100),
		[txt-p] money,
		[testintru4-D] varchar(100),
		[tst-Np/m3] money
	)

INSERT	@Sample
SELECT	2005,  '335.00\1000', '12.84\1000', 14.21, '37.31\1000', 22.22 UNION ALL
SELECT	2008, '9219.00\1000',  '3.13\1000', 11.97,  '3.92\1000',  0.00

SELECT	*,
	CAST(REPLACE(PARSENAME(REPLACE(Peso, '\', '.'), 2), '#', '.') AS MONEY) / 
	CAST(REPLACE(PARSENAME(REPLACE(Peso, '\', '.'), 1), '#', '.') AS MONEY) AS [new_testintru1-D]
FROM	(
		SELECT	*,
			REPLACE([testintru1-D], '.', '#') AS Peso
		FROM	@Sample
	) AS d


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

ayamas
Aged Yak Warrior

552 Posts
Posted - 2008-09-26 : 09:30:14
Visakh & Peso Thanks for the reply. The problem is the column numbers are not constant so I cannot predefine the temporary table.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-09-26 : 09:34:30
[code]DECLARE @Sample TABLE
(
year smallint,
[testintru1-D] varchar(100),
[testintru2-D] varchar(100),
[txt-p] money,
[testintru4-D] varchar(100),
[tst-Np/m3] money
)

INSERT @Sample
SELECT 2005, '335.00\1000', '12.84\1000', 14.21, '37.31\1000', 22.22 UNION ALL
SELECT 2008, '9219.00\1000', '3.13\1000', 11.97, '3.92\1000', 0.00

SELECT *
FROM @Sample

SELECT year,
CAST(dbo.fnParseString(-1, '\', [testintru1-D]) AS MONEY) / COALESCE(CAST(dbo.fnParseString(-2, '\', [testintru1-D]) AS MONEY), 1) AS [testintru1-D],
CAST(dbo.fnParseString(-1, '\', [testintru2-D]) AS MONEY) / COALESCE(CAST(dbo.fnParseString(-2, '\', [testintru2-D]) AS MONEY), 1) AS [testintru2-D],
CAST(dbo.fnParseString(-1, '\', [txt-p]) AS MONEY) / COALESCE(CAST(dbo.fnParseString(-2, '\', [txt-p]) AS MONEY), 1) AS [txt-p],
CAST(dbo.fnParseString(-1, '\', [testintru4-D]) AS MONEY) / COALESCE(CAST(dbo.fnParseString(-2, '\', [testintru4-D]) AS MONEY), 1) AS [testintru4-D],
CAST(dbo.fnParseString(-1, '\', [tst-Np/m3]) AS MONEY) / COALESCE(CAST(dbo.fnParseString(-2, '\', [tst-Np/m3]) AS MONEY), 1) AS [tst-Np/m3]
FROM @Sample[/code]See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-09-26 : 09:35:33
What query gives your resulset?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

ayamas
Aged Yak Warrior

552 Posts
Posted - 2008-09-26 : 09:38:37
quote:
Originally posted by Peso

DECLARE	@Sample TABLE
	(
		year smallint,
		[testintru1-D] varchar(100),
		[testintru2-D] varchar(100),
		[txt-p] money,
		[testintru4-D] varchar(100),
		[tst-Np/m3] money
	)

INSERT	@Sample
SELECT	2005,  '335.00\1000', '12.84\1000', 14.21, '37.31\1000', 22.22 UNION ALL
SELECT	2008, '9219.00\1000',  '3.13\1000', 11.97,  '3.92\1000',  0.00

SELECT	*
FROM	@Sample

SELECT	year,
	CAST(dbo.fnParseString(-1, '\', [testintru1-D]) AS MONEY) / COALESCE(CAST(dbo.fnParseString(-2, '\', [testintru1-D]) AS MONEY), 1) AS [testintru1-D],
	CAST(dbo.fnParseString(-1, '\', [testintru2-D]) AS MONEY) / COALESCE(CAST(dbo.fnParseString(-2, '\', [testintru2-D]) AS MONEY), 1) AS [testintru2-D],
	CAST(dbo.fnParseString(-1, '\', [txt-p]) AS MONEY) / COALESCE(CAST(dbo.fnParseString(-2, '\', [txt-p]) AS MONEY), 1) AS [txt-p],
	CAST(dbo.fnParseString(-1, '\', [testintru4-D]) AS MONEY) / COALESCE(CAST(dbo.fnParseString(-2, '\', [testintru4-D]) AS MONEY), 1) AS [testintru4-D],
	CAST(dbo.fnParseString(-1, '\', [tst-Np/m3]) AS MONEY) / COALESCE(CAST(dbo.fnParseString(-2, '\', [tst-Np/m3]) AS MONEY), 1) AS [tst-Np/m3]
FROM	@Sample
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033


E 12°55'05.63"
N 56°04'39.26"


Peso the columns are not constant.The resultset which I posted comes from a dynamic SQL.Sometimes the number of columns can be even one.I just wanted to know the way by which I can put the resultset generated by the dynamic SQL into some temporary table.But I cannot predefine the temporary table as the number of columns are not constant.
Go to Top of Page

ayamas
Aged Yak Warrior

552 Posts
Posted - 2008-09-26 : 09:41:41
Here some extract of my query.I hope you will get a fair idea.

SET @SQL=''
SET @Str=''
SELECT @SQL = @SQL + ',MAX(Case when InstrumentName=''' + InstrumentName +'''
THEN (Case When DisplayFormula Is Not Null Then
Cast(Reading As Varchar(25)) + '''' + DisplayFormula Else
Cast(Reading As Varchar(25)) End) End) As "'+ InstrumentName + '-' + UnitName +'"'
FROM
(SELECT Distinct InstrumentName,UnitName,InstrumentOrder
From #tbl Union Select Distinct InstrumentName,DisplayUnit,InstrumentOrder
From Instruments Inner Join Units On Units.UnitID=Instruments.UnitID Where LineID= @LineId AND IsActive=1) A
order by InstrumentOrder


Select @str = @str + 'Select year as Transactionyear ' +(@sql)+' Into #t From #tbl Group By year order by year'

Exec (@str)

select * from #t

As you can see I tried to insert into a table #t.But when i try to query the table #t it says invalid object #t
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-09-26 : 09:43:23
That is because of SCOPE.
The local temp table #t created in the dynamic query is not available outside the dynamic query (scope).
Change #t to ##t (global temp table).


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

ayamas
Aged Yak Warrior

552 Posts
Posted - 2008-09-26 : 09:45:42
quote:
Originally posted by Peso

That is because of SCOPE.
The local temp table #t created in the dynamic query is not available outside the dynamic query (scope).
Change #t to ##t (global temp table).


E 12°55'05.63"
N 56°04'39.26"


Wow Peso it worked I didnt knew that.Thanks a million.
Thanks again.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-09-26 : 09:50:07
Why are you using dynamic sql at all for this simple query?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site