Showing posts with label somebody. Show all posts
Showing posts with label somebody. Show all posts

Monday, March 12, 2012

Need advice, new to VB 6.0 and SQL server 7

I'm young in programming, i need somebody to tell me the minimum requirement for a software developed in VB 6.0 and SQL server 7
Thanks alotHi there,

a standard Pentium 4 with 256 RAM will do i guess, take care my fren.. :)

Wednesday, March 7, 2012

need a subsetsorted by languageid can somebody help with stored proc..............

Here is part of my stored procedure. I would like to get a subsetsorted back from my query where languageid = @.languageid but I am lost. Can somebody help me out, thanks Newbie

-- Issue query

DECLARE @.sqlnvarchar(4000)

SET @.sql='SELECT [KeywordID], [Keyword], [LanguageID]

FROM

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '+ @.sortExpression+') AS RowNum

FROM syl_Keywords) AS KeywordInfo

WHERE RowNum BETWEEN '+CONVERT(nvarchar(10), @.startRowIndex)+

' AND ('+CONVERT(nvarchar(10), @.startRowIndex)+' + '

+CONVERT(nvarchar(10), @.maximumRows)+') - 1 AND [LanguageID] = @.LanguageID'

-- Execute the SQL query

EXECsp_executesql @.sql

If this answers your question... please mark this as the "answer" :)

DECLARE

@.SQLVARCHAR(8000)

SELECT @.SQL='
WITH _temp AS
(SELECT
KeywordID,
Keyword,
LanguageID,
ROW_NUMBER() OVER(ORDER BY '+ @.sortExpression+' ) AS RowNumber
FROM
syl_Keywords
WHERE
LanguageID = '''+ @.LanguageID+''')

SELECT TOP '+CONVERT(VARCHAR, @.maximumRows)+' *
FROM _temp WHERE RowNumber > '+CONVERT(VARCHAR, @.startRowIndex)

EXECsp_executesql @.SQL

Peace,

|||

Hi Nullable, thank you for your reply. I can't get it to work with your example. Here is my original stored procedure that returns a subset sorted by sortexpression which works. What I would like to accomplish is to sort it by LanguageID as well.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [dbo].[syl_KeywordGetSubsetSorted]

@.sortExpression

nvarchar(50),

@.startRowIndex

int,

@.maximumRows

int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SETNOCOUNTONBEGINTRYIFLEN(@.sortExpression)= 0SET @.sortExpression='KeywordID'-- Since @.startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), incrementSET @.startRowIndex= @.startRowIndex+ 1-- Issue queryDECLARE @.sqlnvarchar(4000)SET @.sql='SELECT [KeywordID], [Keyword], [LanguageID]

FROM

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '

+ @.sortExpression+') AS RowNum

FROM syl_Keywords) AS KeywordInfo

WHERE RowNum BETWEEN '

+CONVERT(nvarchar(10), @.startRowIndex)+' AND ('+CONVERT(nvarchar(10), @.startRowIndex)+' + '+CONVERT(nvarchar(10), @.maximumRows)+') - 1'

-- Execute the SQL queryEXECsp_executesql @.sqlRETURNENDTRY

BEGIN

CATCH--Execute LogError SPEXECUTE [dbo].[syl_LogError];--Being in a Catch Block indicates failure.--Force RETURN to -1 for consistency (other return values are generated, such as -6).RETURN-1ENDCATCH

END

|||

newbie06:

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '+ @.sortExpression+') AS RowNum

FROM syl_Keywords) AS KeywordInfo

Change that to:

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '+ @.sortExpression+', LanguageID) AS RowNum

FROM syl_Keywords) AS KeywordInfo

|||

Here is the working portion of the Stored Proc:

-- Issue query

DECLARE @.sqlnvarchar(4000)SET @.sql='SELECT [KeywordID], [Keyword], [LanguageID]

FROM

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '

+ @.sortExpression+') AS RowNum

FROM syl_Keywords

WHERE LanguageID = '

+CONVERT(nvarchar(10), @.LanguageID)+' )

AS KeywordInfo

WHERE RowNum BETWEEN '

+CONVERT(nvarchar(10), @.startRowIndex)+' AND ('+CONVERT(nvarchar(10), @.startRowIndex)+' + '+CONVERT(nvarchar(10), @.maximumRows)+') - 1'

Need a help on a SQL Query

Could somebody please help me on following SQL Query.
Single table data likes this:
SerialNumber PassedTestType
20001 1
20001 2
20001 3
20001 4
20001 5
20002 1
20002 2
20002 3
20003 1
20003 2
20004 1
.............
.............
There are total 5 tests (1-5 indicate each test). A new record will be
added in when it passed a test. Unit always start from test 1 to test 5
I want to create a simple report like this:
TestType Total number of units in this test stage
1 2356
2 456
3 4352
4 176
5 36253
How to write a SQL query like above Work In Process Report ?
Thanks.Try:
select
TestType
, count (*)
from
MyTable
group by
TestType
order by
TestType
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<wubin_98@.yahoo.com> wrote in message
news:1140653005.105032.132270@.g44g2000cwa.googlegroups.com...
Could somebody please help me on following SQL Query.
Single table data likes this:
SerialNumber PassedTestType
20001 1
20001 2
20001 3
20001 4
20001 5
20002 1
20002 2
20002 3
20003 1
20003 2
20004 1
.............
.............
There are total 5 tests (1-5 indicate each test). A new record will be
added in when it passed a test. Unit always start from test 1 to test 5
I want to create a simple report like this:
TestType Total number of units in this test stage
1 2356
2 456
3 4352
4 176
5 36253
How to write a SQL query like above Work In Process Report ?
Thanks.|||If I understand correctly, you only want the last test passed for each
SerialNumber to be counted.
SELECT LastPassed, count(SerialNumber)
FROM (select SerialNumber,
max(PassedTestType) as LastPassed
from TestHistory
group by SerialNumber) as T
GROUP BY LastPassed
ORDER BY LastPassed
Roy
On 22 Feb 2006 16:03:25 -0800, "wubin_98@.yahoo.com"
<wubin_98@.yahoo.com> wrote:

>Could somebody please help me on following SQL Query.
>Single table data likes this:
>SerialNumber PassedTestType
>20001 1
>20001 2
>20001 3
>20001 4
>20001 5
>20002 1
>20002 2
>20002 3
>20003 1
>20003 2
>20004 1
>.............
>.............
>There are total 5 tests (1-5 indicate each test). A new record will be
>added in when it passed a test. Unit always start from test 1 to test 5
>I want to create a simple report like this:
>TestType Total number of units in this test stage
>1 2356
>2 456
>3 4352
>4 176
>5 36253
>How to write a SQL query like above Work In Process Report ?
>Thanks.|||Roy,
It is just what I need.
Thank you very much!