Conversion of oracle query to SQL

Question

Post reply

Conversion of oracle query to SQL

Minaz Amin

SSChampion

Points: 11052
More actions
January 13, 2012 at 5:27 am

#249076

Hi,
I am finding difficult to resolve this oracle query to SQL.
select substr((pagename), 1,instr(pagename,'/',1,1)-1) as sitename,
count(substr((pagename), 1,instr(pagename,'/',1,1)-1)) as cachecount
from systempagecache
group by substr((pagename), 1,instr(pagename,'/',1,1)-1)
order by cachecount desc
Please help me out...
"More Green More Oxygen !! Plant a tree today"

Viewing 6 posts - 1 through 5 (of 5 total)

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

azdzn SSCarpal Tunnel Points: 4128 More actions · Answer 1

Hi

It should be something like this :

select

substring(pagename, 1,charindex('/',pagename)-1) as sitename, -- or LEFT(pagename, charindex('/',pagename)-1)

count(*) as cachecount

from systempagecache

group by substring((pagename), 1,charindex('/',pagename)-1)

order by count(*) desc

If not, please post your table definition, a few test data and the result expected.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 2

Just a quick and silly reminder:

select

substring(pagename, 1,charindex('/',pagename)-1) as sitename, -- or LEFT(pagename, charindex('/',pagename)-1)

count(*) as cachecount

from systempagecache

group by substring((pagename), 1,charindex('/',pagename)-1)

order by cachecount desc

It's valid because the ORDER by is the last step and it occurs when the aliases are already applied to the columns.

To the OP: more code or explanations could help us understand what you're after. 😉

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

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

I have to agree with codebyo, if this is a system table in Oracle, it doesn't exist in SQL Server (at least not with this name).

Could you explain what it is you are attempting to accomplish in SQL Server?

Minaz Amin SSChampion Points: 11052 More actions · Answer 4

I really appreciate your time and sorry I did not explained much on this we have content management App and the table systempagecache have the pagename information. We want to know how many cache count per sitebasis.

The data till the first '/' gives me the site name and data between first and second '/' with _c is the cache

MYSITE_Lion/EEStaticHeaderIncludes

MYSITE_Lion/EEEditorial_C/EEAssignPageProperties

MYSITE_Lion/Page/EELink

MYSITE_Lion/EEEditorial_C/EESummaryHomeLanding

MYSITE_Lion/EEEditorial_C/EESummary

MYSITE_Lion/EEEditorial_C/EESummaryIdeaTeaserContent

Visitor_Site/VBEditorial_C/VBWrapperHubDetail

Visitor_Site/VBEditorial_C/VBHeroModule

Visitor_Site/VBListOfLinks_C/Summary

Visitor_Site/VBListOfLinks_C/SummaryListOfLinksGroup

Example

========

Sitename Cachecount

MYSITE_Lion 6

Visitor_Site 4

I receive the folowing errors

Msg 537, Level 16, State 3, Line 1

Invalid length parameter passed to the LEFT or SUBSTRING function.

Let me know your valuable input.

"More Green More Oxygen !! Plant a tree today"

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 5

It appears that your first line of data has only a single "/":

MYSITE_Lion/EEStaticHeaderIncludes

Searching for the second "/" will return an error because the location of the starting position for the second substring call cannot be determined.