Query Help

[jw2000]

Code compiles but I get alot of errors for line "SELECT Substring ..." because chemicalId, chemicalTitle, chemicalDescription, chemicalCountry, chemicalState, chemicalCity, etc. gets the following errors:

Invalid column name 'chemicalId'
Invalid column name 'chemicalTitle'
Invalid column name 'chemicalDescription,'
Invalid column name 'chemicalCountry'
Invalid column name 'chemicalState'
Invalid column name 'chemicalCity,'
etc

I am trying to get values to print out values for @Chunk1, @Chunk2, and @Chunk3 which are part from the TEXT chemicalsInfo column of a Temp Table.


--------------------------------------------------------

CREATE TABLE #TempChemicalsChunk
(
ID int IDENTITY,
chemicalsInfo TEXT
)

DECLARE EmailCursor CURSOR FOR
SELECT ae.emailAlertId, ae.emailAlertState, ae.emailAlertCountry, ae.emailAlertEmail, ae.chemicalIndustryId, ae.chemicalTypeId, ae.chemicalAskingPrice
FROM emailalert ae, users u
WHERE (... lots more code ...)


OPEN EmailCursor


FETCH NEXT FROM EmailCursor
INTO @EmailAlertId, @emailAlertState, @emailAlertCountry, @emailAlertEmail, @chemicalIndustryId, @chemicalTypeId, @chemicalAskingPrice

WHILE @@FETCH_STATUS = 0
BEGIN


INSERT INTO #TempChemicals (chemicalId, chemicalIndustryDescription, chemicalTypeDescription, chemicalIndustryId2, chemicalTypeId2, chemicalTitle, chemicalCity, chemicalState, chemicalCountry, chemicalAskingPrice2, chemicalSalesRevenue, chemicalNetProfit, chemicalDescription)
SELECT b.chemicalId, cI.chemicalIndustryDescription, bT.chemicalTypeDescription, c.chemicalIndustryId, c.chemicalTypeId, c.chemicalTitle, c.chemicalCity, c.chemicalState, c.chemicalCountry, c.chemicalAskingPrice, c.chemicalSalesRevenue, c.chemicalNetProfit, c.chemicalDescription
FROM chemical c, chemicalIndustry cI, chemicalType cT
WHERE c.chemicalIndustryId = cI.chemicalIndustryId
AND c.chemicalTypeId = cT.chemicalTypeId
AND c.chemicalIndustryId = @chemicalIndustryId
AND c.chemicalTypeId = @chemicalTypeId
AND c.chemicalCountry = @emailAlertCountry
AND c.chemicalState = IsNull(@emailAlertState, chemicalState)
ORDER BY c.chemicalTitle ASC



IF @@ROWCOUNT > 0
BEGIN


INSERT INTO #TempChemicalsChunk (chemicalsInfo)
SELECT Substring('<A href="

http://www.domain.com/chem.asp?cId='

+ CAST(chemicalId AS varchar) + '" target="NEW">' + dbo.PROPER(chemicalTITLE) + '</A><BR>' + dbo.SentenceCase(LEFT(chemicalDescription, 80)) + '...<BR>' + REPLACE((chemicalCountry + ' > ' + chemicalState + ' > ' + dbo.PROPER(chemicalCity) + '<BR>'), '> >', '>') + REPLACE('Asking Price: ' + chemicalAskingPrice2 + ' * Revenue: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1))-3) + ' * Profit: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1))-3) + ' *<BR><BR>', '$0', 'Call/Email'), 1, 8000)


SELECT @Chunk1 = Substring(chemicalsInfo, 1, 8000) FROM #TempChemicalsChunk
SELECT @Chunk1 = Substring(chemicalsInfo, 8001, 8000) FROM #TempChemicalsChunk
SELECT @Chunk1 = Substring(chemicalsInfo, 16001, 8000) FROM #TempChemicalsChunk

print '@Chunk1: ' + @Chunk1
print '@Chunk2: ' + @Chunk2
print '@Chunk3: ' + @Chunk3




Truncate table #TempChemicals
FETCH NEXT FROM EmailCursor
INTO @EmailAlertId, @emailAlertState, @emailAlertCountry, @emailAlertEmail, @chemicalIndustryId, @chemicalTypeId, @chemicalAskingPrice


END
END

CLOSE EmailCursor
DEALLOCATE EmailCursor

END

 

[bborissov]

I didn't see where you declare #TempChemicals, I see you only create #TempChemicalsChunk

Borislav Borissov

 

[maswien]

this part of code is wrong,

         INSERT INTO #TempChemicalsChunk (chemicalsInfo)
                 SELECT Substring('<A href="[URL unfurl="true"]http://www.domain.com/chem.asp?cId='[/URL] + CAST(chemicalId AS varchar) + '" target="NEW">' + dbo.PROPER(chemicalTITLE) + '</A><BR>' + dbo.SentenceCase(LEFT(chemicalDescription, 80)) + '...<BR>' + REPLACE((chemicalCountry + ' > ' + chemicalState + ' > ' + dbo.PROPER(chemicalCity) + '<BR>'), '>  >', '>') + REPLACE('Asking Price: ' + chemicalAskingPrice2 + '   *     Revenue:  $' +  LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1))-3) + '   *     Profit:  $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1))-3) + '   *<BR><BR>', '$0', 'Call/Email'), 1, 8000)
        
        
            SELECT @Chunk1 = Substring(chemicalsInfo, 1, 8000) FROM #TempChemicalsChunk 
            SELECT @Chunk1 = Substring(chemicalsInfo, 8001, 8000) FROM #TempChemicalsChunk 
            SELECT @Chunk1 = Substring(chemicalsInfo, 16001, 8000) FROM #TempChemicalsChunk

The column chemicalsInfo in #TempChemicalsChunk will have a maximum length at 8000, so the two following SQLs will fail

 

[jw2000]

chemicalsInfo in #TempChemicalsChunk is TEXT, so it doesn't have a maximum length of 8000, does it?

If it does, do you have suggestions on how I can temporarily store chemicalsInfo (which can have up to 24,000 characters) to break down into 3 chucks (@Chuck1, @Chuck2, and @Chuck3)?

 

[jw2000]

Type above, the 3 selects should read:

SELECT @Chunk1 = Substring(chemicalsInfo, 1, 8000) FROM #TempChemicalsChunk
SELECT @Chunk2 = Substring(chemicalsInfo, 8001, 8000) FROM #TempChemicalsChunk
SELECT @Chunk3 = Substring(chemicalsInfo, 16001, 8000) FROM #TempChemicalsChunk

 

[jw2000]

Can anyone help?

 

[maswien]

jw2000, I didn't mean type TEXT has a limit length of 8000. Look your code carefully you will find that the insert statement(right before the 3 select statements) insert only maximum to 8000 characters to the column of table #TempChemicalsChunk, and this for sure cause query "SELECT @Chunk2 = Substring(chemicalsInfo, 8001, 8000) FROM #TempChemicalsChunk " failing.

 

[jw2000]

maswien, I changed the code to:

INSERT INTO #TempChemicalsChunk (chemicalsInfo)
SELECT Substring('<A href="

http://www.domain.com/chem.asp?cId='

+ CAST(chemicalId AS varchar) + '" target="NEW">' + dbo.PROPER(chemicalTITLE) + '</A><BR>' + dbo.SentenceCase(LEFT(chemicalDescription, 80)) + '...<BR>' + REPLACE((chemicalCountry + ' > ' + chemicalState + ' > ' + dbo.PROPER(chemicalCity) + '<BR>'), '> >', '>') + REPLACE('Asking Price: ' + chemicalAskingPrice2 + ' * Revenue: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1))-3) + ' * Profit: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1))-3) + ' *<BR><BR>', '$0', 'Call/Email'), 1, 24000)

but I still get the following errors:

Server: Msg 207, Level 16, State 3, Procedure sp_Test, Line 189
Invalid column name 'chemicalId'.
Server: Msg 207, Level 16, State 1, Procedure sp_Test, Line 189
Invalid column name 'chemicalTITLE'.
Server: Msg 207, Level 16, State 1, Procedure sp_Test, Line 189
Invalid column name 'chemicalDescription'.
Server: Msg 207, Level 16, State 1, Procedure sp_Test, Line 189
Invalid column name 'chemicalCountry'.
Server: Msg 207, Level 16, State 1, Procedure sp_Test, Line 189
Invalid column name 'chemicalState'.
Server: Msg 207, Level 16, State 1, Procedure sp_Test, Line 189
Invalid column name 'chemicalCity'.
Server: Msg 207, Level 16, State 1, Procedure sp_Test, Line 189
Invalid column name 'chemicalAskingPrice2'.
Server: Msg 207, Level 16, State 1, Procedure sp_Test, Line 189
Invalid column name 'chemicalSalesRevenue'.
Server: Msg 207, Level 16, State 1, Procedure sp_Test, Line 189
Invalid column name 'chemicalSalesRevenue'.
Server: Msg 207, Level 16, State 1, Procedure sp_Test, Line 189
Invalid column name 'chemicalNetProfit'.
Server: Msg 207, Level 16, State 1, Procedure sp_Test, Line 189
Invalid column name 'chemicalNetProfit'.

 

[maswien]

you can try following:

declare @str1 as varchar(5000)
declare @str2 as varchar(5000)
set @str1 = ...
set @str2 = ...
create table #myTable(myCol text)
insert into #myTable
 select substring( @str1 + @str2, 1, 10000)

It should fail, because insert statement can't handle a string longer than 8000. Read the BOL and use different method to dealing with longer than 8000 strings.

 

[jw2000]

Using the following code, I still get the same errors (shown above):

declare @str1 as varchar(8000)
declare @str2 as varchar(8000)
declare @str3 as varchar(8000)

set @str1 = Substring('<A href="

http://www.domain.com/chem.asp?cId='

+ CAST(chemicalId AS varchar) + '" target="NEW">' + dbo.PROPER(chemicalTITLE) + '</A><BR>' + dbo.SentenceCase(LEFT(chemicalDescription, 80)) + '...<BR>' + REPLACE((chemicalCountry + ' > ' + chemicalState + ' > ' + dbo.PROPER(chemicalCity) + '<BR>'), '> >', '>') + REPLACE('Asking Price: ' + chemicalAskingPrice2 + ' * Revenue: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1))-3) + ' * Cash Flow: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1))-3) + ' *<BR><BR>', '$0', 'Call/Email'), 1, 8000)

set @str2 = Substring('<A href="

http://www.domain.com/chem.asp?cId='

+ CAST(chemicalId AS varchar) + '" target="NEW">' + dbo.PROPER(chemicalTITLE) + '</A><BR>' + dbo.SentenceCase(LEFT(chemicalDescription, 80)) + '...<BR>' + REPLACE((chemicalCountry + ' > ' + chemicalState + ' > ' + dbo.PROPER(chemicalCity) + '<BR>'), '> >', '>') + REPLACE('Asking Price: ' + chemicalAskingPrice2 + ' * Revenue: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1))-3) + ' * Cash Flow: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1))-3) + ' *<BR><BR>', '$0', 'Call/Email'), 8001, 16000)

set @str3 = Substring('<A href="

http://www.domain.com/chem.asp?cId='

+ CAST(chemicalId AS varchar) + '" target="NEW">' + dbo.PROPER(chemicalTITLE) + '</A><BR>' + dbo.SentenceCase(LEFT(chemicalDescription, 80)) + '...<BR>' + REPLACE((chemicalCountry + ' > ' + chemicalState + ' > ' + dbo.PROPER(chemicalCity) + '<BR>'), '> >', '>') + REPLACE('Asking Price: ' + chemicalAskingPrice2 + ' * Revenue: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1))-3) + ' * Cash Flow: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1))-3) + ' *<BR><BR>', '$0', 'Call/Email'), 16001, 24000)

print '@str1: ' + @str1
print '@str2: ' + @str2
print '@str3: ' + @str3

 

[jw2000]

Any other suggestions? I'm not sure what I'm doing wrong.

 

[zhavic]

maybe I am missing something, but ...

There

SELECT Substring('<A href="

http://www.domain.com/chem.asp?cId='

+ CAST(chemicalId AS varchar) ... blah blah

you have not FROM clause, so you are using columns from 'nowhere', for example column chemicalId

I thing you need something like this:

SELECT Substring('<A href="

http://www.domain.com/chem.asp?cId='

+ CAST(chemicalId AS varchar) ... blah blah
FROM some_table

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[jw2000]

Zhavic,

Thanks for the pointer, but with the new code:

declare @str1 as varchar(8000)
declare @str2 as varchar(8000)
declare @str3 as varchar(8000)

select @str1 = Substring('<A href="

http://www.domain.com/chem.asp?cId='

+ CAST(chemicalId AS varchar) + '" target="NEW">' + dbo.PROPER(chemicalTITLE) + '</A><BR>' + dbo.SentenceCase(LEFT(chemicalDescription, 80)) + '...<BR>' + REPLACE((chemicalCountry + ' > ' + chemicalState + ' > ' + dbo.PROPER(chemicalCity) + '<BR>'), '> >', '>') + REPLACE('Asking Price: ' + chemicalAskingPrice2 + ' * Revenue: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1))-3) + ' * Cash Flow: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1))-3) + ' *<BR><BR>', '$0', 'Call/Email'), 1, 8000) from Chemical

select @str2 = Substring('<A href="

http://www.domain.com/chem.asp?cId='

+ CAST(chemicalId AS varchar) + '" target="NEW">' + dbo.PROPER(chemicalTITLE) + '</A><BR>' + dbo.SentenceCase(LEFT(chemicalDescription, 80)) + '...<BR>' + REPLACE((chemicalCountry + ' > ' + chemicalState + ' > ' + dbo.PROPER(chemicalCity) + '<BR>'), '> >', '>') + REPLACE('Asking Price: ' + chemicalAskingPrice2 + ' * Revenue: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1))-3) + ' * Cash Flow: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1))-3) + ' *<BR><BR>', '$0', 'Call/Email'), 8001, 16000)from Chemical

select @str3 = Substring('<A href="

http://www.domain.com/chem.asp?cId='

+ CAST(chemicalId AS varchar) + '" target="NEW">' + dbo.PROPER(chemicalTITLE) + '</A><BR>' + dbo.SentenceCase(LEFT(chemicalDescription, 80)) + '...<BR>' + REPLACE((chemicalCountry + ' > ' + chemicalState + ' > ' + dbo.PROPER(chemicalCity) + '<BR>'), '> >', '>') + REPLACE('Asking Price: ' + chemicalAskingPrice2 + ' * Revenue: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalSalesRevenue, 0)), 1))-3) + ' * Cash Flow: $' + LEFT(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1), LEN(CONVERT(VARCHAR, CONVERT(MONEY, ISNULL(chemicalNetProfit, 0)), 1))-3) + ' *<BR><BR>', '$0', 'Call/Email'), 16001, 24000)from Chemical

print '@str1: ' + @str1
print '@str2: ' + @str2
print '@str3: ' + @str3


I get errors:
Expression result length exceeds the maximum. 8000 max, 16000 found.
Expression result length exceeds the maximum. 8000 max, 24000 found.

Please help.

 

[jw2000]

Can anyone help?