Parse Column and Sort

Hi can anyone give your idea on how this can be implemented.
I have a table which has a column whose values looks like
Page 10 Line 1
Document
1
3
Sheet: Landscape
Sheet: Part 2
Page: 29 Line: 2
Page91
Page 10
Sheet: Sheet1
i need to sort by Page number and line number if no line number then page
number alone or if no page number then sort by sheet number else normal sort
Can anyone throw some light on itBoss
Why not do that on client?
CREATE TABLE #Test
(
col1 VARCHAR(15)
)
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Document')
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('3')
INSERT INTO #Test VALUES ('Sheet: Landscape')
INSERT INTO #Test VALUES ('Page: 29 Line: 2')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page91')
INSERT INTO #Test VALUES ('Page 10')
INSERT INTO #Test VALUES ('Sheet: Sheet1')
SELECT * FROM #Test ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END DESC
"Boss" <Boss@.discussions.microsoft.com> wrote in message
news:E77C9A37-3E0B-4847-AB07-D7B0C976C5D0@.microsoft.com...
> Hi can anyone give your idea on how this can be implemented.
> I have a table which has a column whose values looks like
> Page 10 Line 1
> Document
> 1
> 3
> Sheet: Landscape
> Sheet: Part 2
> Page: 29 Line: 2
> Page91
> Page 10
> Sheet: Sheet1
> i need to sort by Page number and line number if no line number then page
> number alone or if no page number then sort by sheet number else normal
sort
>
> Can anyone throw some light on it
>|||Can't you clean up the data and create a table where these attributes
are properly represented in atomic columns? Storing meaningful data in
this form is an extremely poor and inconvenient design. That's why it's
usual to scrub and transform external data before it is loaded into
tables.
If a redesign is impossible for you then take a look at CHARINDEX and
PATINDEX. You can extract the different string components into separate
calculated columns and then sort on those.
If you need more help, please post DDL (CREATE TABLE statement
including keys and constraints), some sample data (as INSERT
statements) and show your required end result.
David Portas
SQL Server MVP
--|||Hi Thanks for your reply
It dosent seem to return correct records
CREATE TABLE #Test
(
col1 VARCHAR(25)
)
-- drop table #test
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Document')
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('3')
INSERT INTO #Test VALUES ('Sheet: Landscape')
INSERT INTO #Test VALUES ('Page: 29 Line: 2')
INSERT INTO #Test VALUES ('Page: 29 Line: 1')
INSERT INTO #Test VALUES ('Page: 29 Line: 10')
INSERT INTO #Test VALUES ('Page: 2 Line: 18')
INSERT INTO #Test VALUES ('Page: 71 Line: 1')
INSERT INTO #Test VALUES ('Page: 201 Line: 1000')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page91')
INSERT INTO #Test VALUES ('Page1')
INSERT INTO #Test VALUES ('Page 20')
INSERT INTO #Test VALUES ('Page 10')
INSERT INTO #Test VALUES ('Page 8')
INSERT INTO #Test VALUES ('Sheet: Sheet1')
INSERT INTO #Test VALUES ('Sheet: Sheet21')
INSERT INTO #Test VALUES ('Sheet: Sheet10')
SELECT * FROM #Test ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END ASC
The result is
Document
1
3
Sheet: Landscape
Sheet: Sheet1
Sheet: Sheet21
Sheet: Sheet10
Page: 201 Line: 1000 - this is in wrong order
Page1
Page 8
Page91
Page 10
Page 20
Page 10 Line 1
Page 10 Line 1
Page 10 Line 1
Page: 2 Line: 18 - this is in wrong order
Page: 29 Line: 1
Page: 29 Line: 2
Page: 71 Line: 1 - this is in wrong order
Page: 29 Line: 10
"Uri Dimant" wrote:

> Boss
> Why not do that on client?
> CREATE TABLE #Test
> (
> col1 VARCHAR(15)
> )
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Document')
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('3')
> INSERT INTO #Test VALUES ('Sheet: Landscape')
> INSERT INTO #Test VALUES ('Page: 29 Line: 2')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page91')
> INSERT INTO #Test VALUES ('Page 10')
> INSERT INTO #Test VALUES ('Sheet: Sheet1')
> SELECT * FROM #Test ORDER BY CASE WHEN
> CHARINDEX('Line',col1)>0 OR
> CHARINDEX('Page',col1)>0
> THEN RIGHT('000000000000000'+col1,15)
> END DESC
>
>
>
> "Boss" <Boss@.discussions.microsoft.com> wrote in message
> news:E77C9A37-3E0B-4847-AB07-D7B0C976C5D0@.microsoft.com...
> sort
>
>|||SELECT col1 FROM
(
SELECT replace(col1,' ','')as col1 FROM #Test
) AS Der
ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END ASC
If it does not help ,post expected result, as I and David stated you will be
better off to do that on the client side, but befor that clean up the table
from redundant data
"Boss" <Boss@.discussions.microsoft.com> wrote in message
news:B175FA7A-14E9-4A57-A018-F7B48C805937@.microsoft.com...
> Hi Thanks for your reply
> It dosent seem to return correct records
> CREATE TABLE #Test
> (
> col1 VARCHAR(25)
> )
> -- drop table #test
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Document')
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('3')
> INSERT INTO #Test VALUES ('Sheet: Landscape')
> INSERT INTO #Test VALUES ('Page: 29 Line: 2')
> INSERT INTO #Test VALUES ('Page: 29 Line: 1')
> INSERT INTO #Test VALUES ('Page: 29 Line: 10')
> INSERT INTO #Test VALUES ('Page: 2 Line: 18')
> INSERT INTO #Test VALUES ('Page: 71 Line: 1')
> INSERT INTO #Test VALUES ('Page: 201 Line: 1000')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page91')
> INSERT INTO #Test VALUES ('Page1')
> INSERT INTO #Test VALUES ('Page 20')
> INSERT INTO #Test VALUES ('Page 10')
> INSERT INTO #Test VALUES ('Page 8')
> INSERT INTO #Test VALUES ('Sheet: Sheet1')
> INSERT INTO #Test VALUES ('Sheet: Sheet21')
> INSERT INTO #Test VALUES ('Sheet: Sheet10')
> SELECT * FROM #Test ORDER BY CASE WHEN
> CHARINDEX('Line',col1)>0 OR
> CHARINDEX('Page',col1)>0
> THEN RIGHT('000000000000000'+col1,15)
> END ASC
> The result is
> Document
> 1
> 3
> Sheet: Landscape
> Sheet: Sheet1
> Sheet: Sheet21
> Sheet: Sheet10
> Page: 201 Line: 1000 - this is in wrong order
> Page1
> Page 8
> Page91
> Page 10
> Page 20
> Page 10 Line 1
> Page 10 Line 1
> Page 10 Line 1
> Page: 2 Line: 18 - this is in wrong order
> Page: 29 Line: 1
> Page: 29 Line: 2
> Page: 71 Line: 1 - this is in wrong order
> Page: 29 Line: 10
>
> "Uri Dimant" wrote:
>
page
normal

Parameters in named queries

Hi,

I try to build a named query that would either take a parameter or run a procedure to get a value.

The idea is to have a named query used by the cube partition. The named query must limit the lower boud time item according to a complex logic build in a stored procedure.

First I am not sure if it will work even if I can get the named query to behave this way, however I would rather avoid to alter the view behind the cube.

If possible, how can I create a parameter in a named query, something like this in pseudo-code

? = (EXEC up_FirstCrawl_Qtr)
SELECT *
FROM dbo.V_ST_Crawl
WHERE (RSD_QRTR = ?)

Or

SELECT *
FROM dbo.V_ST_Crawl
WHERE (RSD_QRTR = (EXEC up_FirstCrawl_Qtr) )

Thanks,

Philippe

Not to answer your question directly, but I think you would find this very useful.

Project REAL just released entire set of scripts and packages and whole lot of other stuff you can use to create your data warehouse and Analysis Services cubes.

I think this is great material and you should be able to find answers to many of your questions there:

Here is the link: http://www.microsoft.com/downloads/thankyou.aspx?familyId=b61a37b6-5852-4018-bba9-795a34123ed0&displayLang=en&oRef=

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Woowh, Great.
I always wanted to get more from RealProject than abstracts.
Looks like the real meat is there now, 240MB or so of it.

I am sure going to spend a week-end my wife will remember :-)

In the meantime and to cope with management deadline, I will create another view. I am under the gun.

I always wanted to avoid providing things that work now but require more maintenance, however it is not always possible. problem is that management sees value only in brand new stuff never done before.
No budget nor time is allowed to re-create "working" existing legacy stuff in a much better way, but, they complain when the stuff is down for maintenance... The Chicken or the Egg? No both please.

Have a nice week-end

Philippe

Parameterized SSIS Packeges

How i can create parameterized sql query .This is my basic idea to implement.

select * from dimemployee

where name = ?

or

exec proc sp_para_employee ?

how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.

did you try using the execute sql task, mapping the parameter to a variable?|||

JSR2005 wrote:

How i can create parameterized sql query .This is my basic idea to implement.

select * from dimemployee

where name = ?

or

exec proc sp_para_employee ?

how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.

How hard did you look?

Execute SQL Task
(http://www.sqlis.com/default.aspx?58)

-Jamie

|||thank you it is working fine