Why cant I reference a column in a SQL Cursor?

Question

Post reply

Why cant I reference a column in a SQL Cursor?

Viewing 15 posts - 46 through 60 (of 77 total)

You must be logged in to reply to this topic. Login to reply

blandry SSCarpal Tunnel Points: 4821 More actions · Answer 1

Sorry - back at the desk for a short while...

If you look at the LEFT OUT JOIN, its the DATEADD four lines down from that.

PADL is a custom implementation of "Pad Left" - Code is...

ALTER FUNCTION [dbo].[PADL] (@cSrting nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )

returns nvarchar(4000)

as

begin

declare @length smallint, @lengthPadCharacter smallint

select @length = datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode

select @lengthPadCharacter = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode

if @length >= @nLen

set @cSrting = left(@cSrting, @nLen)

else

begin

declare @nLeftLen smallint, @nRightLen smallint

set @nLeftLen = @nLen - @length -- Quantity of characters, added at the left

set @cSrting = left(replicate(@cPadCharacter, ceiling(@nLeftLen/@lengthPadCharacter) + 2), @nLeftLen)+ @cSrting

end

return (@cSrting)

end

We have PADL, PADR and PADC, eg, Pad Left, Pad Right and Pad Center. I think these were downloaded code back in about 2007-8.

Hope that is a bit helpful.

There's no such thing as dumb questions, only poorly thought-out answers...

blandry SSCarpal Tunnel Points: 4821 More actions · Answer 2

...PS...

The Boss knows that we need a major overhaul on the Main Database - I just have to get him to think beyond just the tables, keys and triggers... As well, he does know that with some clients, report generation can run quite long - so I think over time I can get him to think about the Stored Procs and Funcs too. They are rather old.

; )

There's no such thing as dumb questions, only poorly thought-out answers...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

blandry (5/2/2012)
Sorry - back at the desk for a short while...
If you look at the LEFT OUT JOIN, its the DATEADD four lines down from that.
PADL is a custom implementation of "Pad Left" - Code is...
ALTER FUNCTION [dbo].[PADL] (@cSrting nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
begin
declare @length smallint, @lengthPadCharacter smallint
select @length = datalength(@cSrting)/(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
select @lengthPadCharacter = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
if @length >= @nLen
set @cSrting = left(@cSrting, @nLen)
else
begin
declare @nLeftLen smallint, @nRightLen smallint
set @nLeftLen = @nLen - @length -- Quantity of characters, added at the left
set @cSrting = left(replicate(@cPadCharacter, ceiling(@nLeftLen/@lengthPadCharacter) + 2), @nLeftLen)+ @cSrting
end
return (@cSrting)
end
We have PADL, PADR and PADC, eg, Pad Left, Pad Right and Pad Center. I think these were downloaded code back in about 2007-8.
Hope that is a bit helpful.

Help me with these. In simple english with a couple of small examples, what is PADL doing. We can worry about the others later.

mtassin SSC-Insane Points: 23099 More actions · Answer 4

Near as I can tell PADL seems to be right justifying an nvarchar column.

Essentially it adds a number of characters to the left side of the field and then stick the nvarchar field onto it.

Not sure what PADR would do.... with nvarchar, not much....

PADC would try to center the string. Again without fixed width columns not sure what value you get.... could just call PADL and pass an additional parameter to divide the padding number of characters by 2.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

blandry SSCarpal Tunnel Points: 4821 More actions · Answer 5

PADL, PADR and PADC are three functions to .NET / XBase developers that are very common and go back a long way.

I cant address the original history in our SQL stuff of PADL, but here is how we use it now...

If you remember I told you these SPs are used to generate reports. In earlier versions of our software, clients would choose 1 company to report on and that company's ImportID is then passed to the SQL backend to limit data. In that version, the ImportID was an INT.

One major request from clients was the ability to report on multiple companies and projects (that they select through a checked listview in our app) - they can select as many as they like. Each selected ID is now stored in a string - say "01,05,09,12,16". When we want to check what data is to be included, obviously "01" and "1" are different. So PADL is used to take "1" and make it "01". It certainly could be reengineered, but again, its one of the many portions of our older code that works, and pushing through a reengineering for just that would likely be poo-pood. But in an overhaul? Yup.

Hope that helps.

There's no such thing as dumb questions, only poorly thought-out answers...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Does the following give you the same error?

WITH CTE(n) AS(SELECT 1 UNION ALL SELECT 1),

CTE2(n) AS (SELECT 1 FROM CTE x, CTE y),

CTE3(n) AS (SELECT 1 FROM CTE2 x, CTE2 y),

CTE4(n) AS (SELECT 1 FROM CTE3 x, CTE3 y),

CTE5(n) AS (SELECT 0 UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM CTE4 x, CTE4 y)

SELECT

DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,a.n,modDate)), 0) AS modifiedMonth,

SUM(IsAccess) AS AccessCount,

SUM(IsExcel) AS ExcelCount,

SUM(IsWord) AS WordCount,

SUM(IsPowerPoint) AS PowerPointCount

FROM CTE5 a

LEFT OUTER JOIN (SELECT

f.FileName,

f.LastModified,

DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,a.n,f.LastModified)), 0) AS monthModified,

f.FileType,

sf.ImportType,

sf.ImportID,

ft.Application,

CASE WHEN ft.Application = 'Access' THEN 1 ELSE 0 END AS IsAccess,

CASE WHEN ft.Application = 'Excel' THEN 1 ELSE 0 END AS IsExcel,

CASE WHEN ft.Application = 'Word' THEN 1 ELSE 0 END AS IsWord,

CASE WHEN ft.Application = 'PowerPoint' THEN 1 ELSE 0 END AS IsPowerPoint

FROM dbo.Files f

INNER JOIN dbo.Groups g ON f.GroupID = g.GroupID

INNER JOIN dbo.SourceFiles sf ON g.BatchID = sf.BatchID

INNER JOIN dbo.Import i ON sf.ImportID = i.ImportID

INNER JOIN dbo.FileTypes ft ON f.FileType = ft.FileExtension

WHERE f.FileType IN (SELECT * FROM dbo.fn_Split(@FileType, ','))

AND f.LastModified > dateadd(day, - 1, CONVERT(DATETIME, @DateFrom, 102))

AND f.LastModified < dateadd(day, 1, CONVERT(DATETIME, @DateTo, 102))

AND sf.ImportDate > dateadd(day, - 1, CONVERT(DATETIME, @ImpDateFrom, 102))

AND sf.ImportDate < dateadd(day, 1, CONVERT(DATETIME, @ImpDateTo, 102))

AND CHARINDEX(dbo.PADL(sf.ImportId, 2, '0'), @ImportID) > 0

AND sf.ImportType = @ImportType) b ON a.n = b.monthModified

CROSS APPLY (SELECT

DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,a.n,MIN(f.LastModified))), 0)

FROM dbo.Files f

INNER JOIN dbo.Groups g ON f.GroupID = g.GroupID

INNER JOIN dbo.SourceFiles sf ON g.BatchID = sf.BatchID

INNER JOIN dbo.Import i ON sf.ImportID = i.ImportID

INNER JOIN dbo.FileTypes ft ON f.FileType = ft.FileExtension

WHERE f.FileType IN (SELECT * FROM dbo.fn_Split(@FileType, ','))

AND f.LastModified > dateadd(day, - 1, CONVERT(DATETIME, @DateFrom, 102))

AND f.LastModified < dateadd(day, 1, CONVERT(DATETIME, @DateTo, 102))

AND sf.ImportDate > dateadd(day, - 1, CONVERT(DATETIME, @ImpDateFrom, 102))

AND sf.ImportDate < dateadd(day, 1, CONVERT(DATETIME, @ImpDateTo, 102))

AND CHARINDEX(dbo.PADL(sf.ImportId, 2, '0'), @ImportID) > 0

AND sf.ImportType = @ImportType) c(modDate)

WHERE

a.n < DATEDIFF(mm,MIN(c.ModDate),GETDATE())

-- DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,a.n,c.modDate)), 0) <= GETDATE()

GROUP BY a.n;

blandry SSCarpal Tunnel Points: 4821 More actions · Answer 7

Hi Lynn...

Yes, I already thought about that - switching the "n" to "a.n" - still produces an error, same error.

I have also scanned through for unbalanced parenthesis - but they all look fine and balanced.

If I send you a backup file with the DB and some data will that do? If not, could you shoot me that link again as to what is needed? Thanks... I have to do this on the "quiet", but I know without something, I am asking you to Dance in the Dark.

This has to be something stupid, likely on my part, because that same line is in a few places and yet only this instance of it produces the error. Let me know if a BAK file might be okay.

Thanks for your continuing help.

There's no such thing as dumb questions, only poorly thought-out answers...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Link is easy, first article referenced below in my signature block.

Cadavre SSC-Forever Points: 41690 More actions · Answer 9

blandry (5/2/2012)
I am however getting an error and I cant figure it out! Look at this line that is choking...
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,Files.LastModified)), 0) AS monthModified

My bad, was being lazy and copy and pasted.

WITH CTE(n) AS(SELECT 1 UNION ALL SELECT 1),

CTE2(n) AS (SELECT 1 FROM CTE x, CTE y),

CTE3(n) AS (SELECT 1 FROM CTE2 x, CTE2 y),

CTE4(n) AS (SELECT 1 FROM CTE3 x, CTE3 y),

CTE5(n) AS (SELECT 0 UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM CTE4 x, CTE4 y)

SELECT

DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,a.n,modDate)), 0) AS modifiedMonth,

SUM(IsAccess) AS AccessCount,

SUM(IsExcel) AS ExcelCount,

SUM(IsWord) AS WordCount,

SUM(IsPowerPoint) AS PowerPointCount

FROM CTE5 a

LEFT OUTER JOIN (SELECT

f.FileName,

f.LastModified,

DATEADD(MONTH, DATEDIFF(MONTH, 0, f.LastModified), 0) AS monthModified,