Select Max(xxx) Function: How Do I Retrieve The Value??
Jun 9, 2006
I have a sql query where i want to find the highest number in a column. I believe i am querying for the data properly, but i think my problem is that i don't know how to retrieve that information in code.
The error message i get is this:
"Conversion from type 'DBNull' to type 'Integer' is not valid"
Here is my code:
-------------------------------------------------------------
queryString = "SELECT MAX(SortOrder) as HighestNumber " & _
"FROM uQI_Questions WHERE (SurveyID = @SurveyID)"
........ more code in between .......
' Make sure there is 1 and only 1 record returned.
If ds.Tables(0).Rows.Count = 1 Then
' retrieve the number and add 1 to it.
NextNumber = ds.Tables(0).Rows(0)("HighestNumber")
NextNumber += 1
Else
' Else it must be the FIRST record so return '1'.
NextNumber = 1
End If
-------------------------------------------------------------
The line that generates the error is "NextNumber = ds.Tables(0).Rows(0)("HighestNumber")".
Any suggestions?
View 1 Replies
ADVERTISEMENT
May 5, 2008
Hi...
I made some code which is to return DataReader.
And then I want to use it in other procedure. But I don't know how to extract value from Reader.
Public Function GetMyProfile(ByVal uid As string) As SqlDataReader
Using conn As New SqlConnection(ConnString)
.
.
Dim Reader As SqlDataReader = cmd.ExecuteReader()Return Reader.RecordsAffectedcmd.Dispose()End UsingEnd Function
Sub Button_Click()
dim dr as SqlDataReader
dr=GetMyProfile(ByVal uid As string)
....Is it right way ?
End Sub
View 4 Replies
View Related
Mar 25, 2008
Here's a table function that returns a table variable containing various file properties (creation date, size, etc.). The one limitation to remember is that it won't work with files on mapped drives; you have to use the full \<Server><Share><Folder1..Folder n><Filename> syntax. Still, it's a pretty useful thing to have around. :)
/****************************************************************************/
CREATE FUNCTION fnc_GetFileProps
(@FileName VARCHAR (1024))
RETURNS @Results TABLE (
ErrorCode TINYINT DEFAULT (0),
PropName VARCHAR (255),
PropValue SQL_VARIANT
)
AS
BEGIN
DECLARE @OLEResult INT
DECLARE @FS INT
DECLARE @FileID INT
DECLARE @Message VARCHAR (8000)
DECLARE @ErrorSource VARCHAR (255)
DECLARE @ErrorDesc VARCHAR (255)
DECLARE @INT INT
DECLARE @VARCHAR VARCHAR (1024)
DECLARE @DATETIME DATETIME
DECLARE @BIGINT BIGINT
-- Create an instance of the file system object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FS, @ErrorSource OUT, @ErrorDesc OUT
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, @ErrorSource, @ErrorDesc)
RETURN
END
EXEC @OLEResult = sp_OAMethod @FS, 'GetFile', @FileID OUT, @Filename
IF @OLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FS, @ErrorSource OUT, @ErrorDesc OUT
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, @ErrorSource, @ErrorDesc)
RETURN
END
EXEC @OLEResult = sp_OAGetProperty @FileID, 'Attributes', @INT OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'Attributes', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('Attributes', @INT)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'DateCreated', @DATETIME OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'DateCreated', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('DateCreated', @DATETIME)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'DateLastAccessed', @DATETIME OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'DateLastAccessed', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('DateLastAccessed', @DATETIME)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'DateLastModified', @DATETIME OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'DateLastModified', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('DateLastModified', @DATETIME)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'Name', @VARCHAR OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'Name', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('Name', @VARCHAR)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'Path', @VARCHAR OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'Path', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('Path', @VARCHAR)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'ShortPath', @VARCHAR OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'ShortPath', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('ShortPath', @VARCHAR)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'Size', @BIGINT OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'Size', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('Size', @BIGINT)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'Type', @VARCHAR OUT
IF @OLEResult <> 0
BEGIN
INSERT @Results (ErrorCode, PropName, PropValue)
VALUES (1, 'Type', '<ERROR RETRIEVING PROPERTY>')
END
ELSE
INSERT @Results (PropName, PropValue)
VALUES ('Type', @VARCHAR)
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
RETURN
END
/****************************************************************************/
I geek, therefore I am
View 1 Replies
View Related
Jan 31, 2008
I suppose that this could also be called: Can you retrieve a parameterized view with JDBC? Either accomplishes the same basic task.
I have looked in the documentation, and there really isn't much that talks about JDBC and functions - just stored procedures - which do not returned a CURSOR type according to Microsoft's documentation.
So far, I can retrieve scaler type data in both procedures and functions, and retrieve data sets through executeQuery with a provided sql query. How to return a resultset in Java using procedure or function has still eluded me. (<sarcasm intended> And I thought Java could do just about anything. Who would've thought...)
Any ideas would be greatly appreciated. Resources, books, etc. Google hasn't offered much help with SQL Server and JDBC.
Thanks,
Ken
View 1 Replies
View Related
Aug 22, 2007
Hi
I would like to return data for working days only. This will need to exclude holidays.For eg In the Month of August we have 31 Days and every 1st day of 1st week is holiday.So my output should retrieve me 31-4=27 .
Any ideas?
Thanks...
View 2 Replies
View Related
Sep 12, 2006
EXEC master.dbo.xp_msver ProductVersion can be used to return the server version in a resultset. I need this to do some conditional coding between varchar and varchar(max) in a UDF, so size of the text I return must be different between the SQL2000 and SQL2005.
I cant call an xp_ that returns a resultset within a UDF can I, so how can I get the SQL version?
View 4 Replies
View Related
Jul 17, 2015
I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.
"2 8 7"
"2 8"
"2"
"22"
"3 2 8"
I have tried the following
Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,
Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,
Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),
View 5 Replies
View Related
May 10, 2007
I have used the following Windows C codes to retrieve records from the bus_newjob table in SQL server:
==========================================================
// construct command buffer to be sent to the SQL server
dbcmd( dbproc, ( char * )"select job_number, job_type," );
dbcmd( dbproc, ( char * )" disp_type, disp_status," );
dbcmd( dbproc, ( char * )" start_time, end_time," );
dbcmd( dbproc, ( char * )" pickup_point, destination," );
dbcmd( dbproc, ( char * )" veh_plate, remark," );
dbcmd( dbproc, ( char * )" customer, cust_contact_person," );
dbcmd( dbproc, ( char * )" cust_contact_number, cust_details" );
dbcmd( dbproc, ( char * )" from bus_newjob" );
dbcmd( dbproc, ( char * )" where disp_status = 0" );
dbcmd( dbproc, ( char * )" order by job_number asc" );
result_code = dbsqlexec( dbproc ); // send command buffer to SQL server
// now check the results from the SQL server
while( ( result_code = dbresults( dbproc ) ) != NO_MORE_RESULTS )
{
if( result_code == SUCCEED )
{
memset( ( char * )&disp_job, 0, sizeof( DISPATCH_NEWJOB ) );
dbbind( dbproc, 1, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.job_number );
dbbind( dbproc, 2, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.job_type );
dbbind( dbproc, 3, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.disp_type );
dbbind( dbproc, 4, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.disp_status );
dbbind( dbproc, 5, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.start_time );
dbbind( dbproc, 6, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.end_time );
dbbind( dbproc, 7, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.pickup_point );
dbbind( dbproc, 8, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.destination );
dbbind( dbproc, 9, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.veh_plate );
dbbind( dbproc, 10, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.remark );
dbbind( dbproc, 11, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.customer );
dbbind( dbproc, 12, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_contact_person );
dbbind( dbproc, 13, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_contact_number );
dbbind( dbproc, 14, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_details );
// now process the rows
while( dbnextrow( dbproc ) != NO_MORE_ROWS )
{
new_job = malloc( sizeof( DISPATCH_NEWJOB ) );
if( !new_job )
return( 0 );
memcpy( ( char * )new_job, ( char * )&disp_job, sizeof( DISPATCH_NEWJOB ) );
append_to_list( &Read_Job_List, new_job );
}
}
else
{
sprintf( str, "Results Failed, result_code = %d", result_code );
log_str( str );
break;
}
==========================================================
where the job_type columIn is of the char(1) type, NTBSTRINGBIND is the vartype argument in the dbbind() function.
However, what I have gotten is nothing more than a null string from the job_type column. I have alternatively changed the vartype argument to STRINGBIND, CHARBIND and even INTBIND, but the results are the same.
Who can tell me the tricks to retrieve a char(1) column from SQL server?
View 1 Replies
View Related
Mar 31, 2008
If I had a bunch of paragraphs stored in a database field, is there a way, when displaying that data, that I can write instructions to only retrieve the very first paragraph?
View 4 Replies
View Related
Jan 10, 2014
Is it possible to retrieve Distinct rows from this Select Statement?
I'm getting the correct results, but duplicate rows because some customers place more than one order on the same day.
Code:
SELECT dbo.Customers.CustomerID, dbo.Customers.Title, dbo.Customers.FirstName, dbo.Customers.LastName, dbo.Customers.CustomerEmail, dbo.Customers.DateCreated,
CONVERT(char, dbo.Customers.DateCreated, 103) AS [DD/MM/YYYY], dbo.loyalty_points.LPoints, dbo.Orders.OrderID
FROM dbo.Customers INNER JOIN
dbo.loyalty_points ON dbo.Customers.CustomerID = dbo.loyalty_points.CustomerID INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
WHERE (CONVERT(char, dbo.Customers.DateCreated, 103) = CONVERT(char, GETDATE() - 6, 103))
View 8 Replies
View Related
Sep 6, 2007
I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.
Query:
Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@State,Default)f on State = f.str
Function:
CREATE FUNCTION charlist_to_table
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))
RETURN
END
GO
Anyone have any ideas?
Thanks,
Deb
View 5 Replies
View Related
Apr 8, 2006
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB
I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName)
I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER "
error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'.
Source Error:
Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
View 2 Replies
View Related
Jul 17, 2006
Time Price Volume
090048 510 6749
090122 510 2101
090135 510 1000
090204 505 2840
090213 505 220
090222 505 1260
090232 505 850
090242 505 200
090253 510 1200
090313 510 570
090343 510 250
090353 510 160
Criteria
Retrieve records with independent price and its total volume per minute
SELECT SUBSTRING(st,1,4) AS Ttime,d_price AS Price,SUM(l_cum) AS Volume FROM cmd4
WHERE sd='20060717' AND serial='0455'
GROUP BY SUBSTRING(st,1,4),d_price,l_cum
Result of the above query: -
Time Price Volume
0900 510 6749
0901 510 1000
0901 510 2101
0902 505 200
0902 505 220
0902 505 850
0902 505 1260
0902 505 2840
0902 510 1200
0903 510 160
0903 510 250
0903 510 570
THE FOLLOWING RESULT STILL NOT A TOTAL FOR A MINUTE]
E.G
0901 510 1000
+
0901 510 2101
=
0901 510 3101 <- I NEED THIS
Can any one advice or give me tips over this. Please
View 3 Replies
View Related
Mar 5, 2014
ItemIDItemNamePrice
1Item1120.00
2Item2234.00
3Item3250.00
4Item4300.00
5Item5300.00
6Item6290.00
7Item7170.00
8Item890.00
9Item9170.00
the above is the existing table and i need a query to retrieve max price with out using max function and sub query
View 6 Replies
View Related
Nov 2, 2006
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
View 3 Replies
View Related
Sep 22, 2007
hello,
i have 2 tables joined, the Pictures and the aspnet_Users
at Pictures table i have some 2 rows (UserId and LastUser - both are uniqueidentified)
how can i select the "UserName" from the UserId and the LastUser (witch corresponds with an user from the aspnet_Users table) like so:
in place to show "80a2c487-9981-412f-b4e4-e0f8acda8cea" i awnt to show the UserName witch corresponds with that code (from UserId and from LastUser witch may be different)
here is my stored procedure SELECT Pictures.PictureID, Pictures.UserId, Pictures.Name,
Pictures.LastUser, aspnet_Users.UserId, aspnet_Users.UserName
FROM aspnet_Users INNER JOIN
Pictures ON aspnet_Users.UserId = Pictures.UserIdshould i make another table, the third one, in witch i have 2 rows the PictureId and UserId and make a relationship between them...?But still, how can i take the UserName witch corresponds with LastUser code?please help me, thanks
View 4 Replies
View Related
Oct 24, 2006
Hi,
What I want to do is include a function in my select:
select myFunc(aTable.aFieldValue), aTable.aFieldValue from aTable
but where myFunc actually returns a Table instead of a scalar. How can I do this please?
for example, I know:
select * from myFunc(123) from aTable
will work fine.
and
select myScalarFunc(aTable.aFieldValue) from aTable
will also work.
But how can i get the 123 value from a table?
View 3 Replies
View Related
Sep 24, 2004
Hi all, in SQL 2000 I can select data from a different server using the sintax
"select * from server_name.db_name.owner.table_name".
Can i do the same thing with a table function (e.g "select * from server_name.db_name.owner.MyFun_name()")?
Is there a way to do it?
Thank you all, Andrew
View 3 Replies
View Related
Apr 11, 2008
I have a table like this below:
<PRE>
Page Book Release MaxPages
1234ABC A1
1234ABC B2
9999ABC D1
9999ABC E2
7777ABC A1
7777ABC C2
</PRE>
I want to select every page of the book, but only the highest release of that page.
Something Like:
Select Book, Page, Max(Release), MaxPages
From Table
But I can't quiet figure it out.
Thanks for any help.
View 11 Replies
View Related
Jan 21, 2004
CREATE FUNCTION GetPerson (@SSN integer, @NamePrefix varchar(10), @FirstName varchar(30), @MiddleName varchar(30), @LastName varchar(40), @NameSuffix varchar(10), @HomeID integer, @MailID integer, @DOB timestamp, @Gender varchar(1), @MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @PersonID integer
set @PersonID=0
if @SSN>0 and @SSN<999999999
Begin
select Min(lngPersonID) AS PersonID from Persons where lngSSN=@SSN
End
ELSE
if @SSN is not null
BEGIN
IF @LastName is not null and @FirstName is not null and @MiddleName is not null and @NamePrefix is not null and @NameSuffix is not null
Begin
select MIN(lngPersonID) AS PersonID from Persons
where strNamePrefix= @NamePrefix and strFirstName=@FirstName
and strMiddleName=@MiddleName and strLastName=@LastName
and strNameSuffix=@NameSuffix and lngSSN=@SSN
End
ELSE
if @LastName is not null
BEGIN
select MIN(lngPersonID) as PersonID from Persons
where strLastName=@LastName
and lngSSN = @SSN
END
END
return (@personID)
END
I m having problem with the "Select" function
the error I m getting is
Select statements included within a function cannot return data to a client (error 444)
Cann I use "select" statement in the function? If not what is the alternative?
I reduced the size of the sproc because it is a big one, I donn have any proble with syntax.
Thanks
View 10 Replies
View Related
Apr 17, 2008
I have a select statement running within stored procedure as below. What I need to do is format the results of the select into one string by concatanting the below, and removing any null fields by using ISNULL. I am struggling with syntax. Can anyone advise? Should I be using a function? is so any sample syntax or pointers would be great..thanks marco
SELECT address1, address2, address3, address4, address5
FROM customer
View 5 Replies
View Related
May 22, 2008
Hi,
I've got a complex UDF I need to call twice in a select statement, as shown below:
SELECT
dbo.myFunction(colName),
dbo.myFunction(colName) * 2
FROM
tableName
The problem is, the result of "dbo.myFunction(colName)" is not being cached, so the function is executed again for "dbo.myFunction(colName) * 2". This is having a significant impact on performance (doubling the amount of time it takes for the query to execute).
Is there any way I can write the query so that dbo.myFunction is only executed once?
Thanks for your help,
Andrew
View 5 Replies
View Related
Aug 23, 1999
Can I write a function inside a Select statement in sql server 7.0
If so HOW ?
Manish Mehta
View 2 Replies
View Related
Feb 3, 2015
I'm trying to use the DateDiff function within my select statement, but I'd like to add the parameter of greater than 30 days. This will have the query only return records where my bill stop date is greater than 30 days from the completion date. I currently have the datediff function within my select statement as
DATEDIFF (d,A.StopBillDate, a.CompletionDate) as [DIFFERENCE]
I would prefer to keep the datediff function within the select statement so as to have difference in days appear as a column within my output.I have been unable to add the parameter of > 30 days to the query without getting an error.
View 2 Replies
View Related
Mar 3, 2015
I have select to split FullName on LastName and FirstName columnselect
Substring(FullName, 1,Charindex(',', FullName)-1) LName
,Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) FName
from Table1
Is it possible to create function based on that select? If yes. How it to do?
View 12 Replies
View Related
Oct 15, 2015
if I have table XXXX with columns a,b,c,d,e,f,g,h,i and I need a function or stored procedure.If I use SELECT a,b,c,d from XXXX and the function returns the result set with columns e,f,g,h,i only Means the columns used in Select must not be included in the result set.
View 10 Replies
View Related
Apr 16, 2008
Can you provide me a way to avoid using the aggregate function Max in the following select statement?
SELECT a.clmssn,
a.cossn,
Max(b.clm_seq_num) as clm_seq_num,
Max(c.chrs_seq_num) as chrs_seq_num,
Max(b.clm_dcn_dt) as clm_dcn_dt
FROM Claim a
LEFT OUTER JOIN clmdcn b
ON a.clmssn = b.clmssn
AND a.cossn = b.cossn
AND a.clm_seq_num = b.clm_seq_num
INNER JOIN clmchrs c
ON a.clmssn = c.clmssn
AND a.cossn = c.cossn
AND a.clm_seq_num = c.clm_seq_num
Group By a.clmssn, a.cossn
Order By a.clmssn, a.cossn
I want to join another table, Juris, to get the Crnt_Office column but the aggregate function Max forces me to include Crnt_Office it in the group by clause. This results in returning all rows containing unique office codes. I only want the office code from the row with the highest Clm_Seq_Num and Juris_Seq_Num.
Juris
Clmssn = key
Cossn = key
Clm_seq_num = key
Juris_seq_num = key
Crnt_Office
The following returns incorrect results:
SELECT a.clmssn,
a.cossn,
Max(a.clm_seq_num) as clm_seq_num,
Max(c.chrs_seq_num) as chrs_seq_num,
Max(d.juris_seq_num) as juris_seq_num,
Max(b.clm_dcn_dt) as clm_dcn_dt,
d.crnt_office
FROM mt16ic_Claim a
LEFT OUTER JOIN mt16ic_clmdcn b
ON a.clmssn = b.clmssn
AND a.cossn = b.cossn
AND a.clm_seq_num = b.clm_seq_num
LEFT OUTER JOIN mt16ic_juris d
ON a.clmssn = d.clmssn
AND a.cossn = d.cossn
AND a.clm_seq_num = d.clm_seq_num
INNER JOIN mt16ic_clmchrs c
ON a.clmssn = c.clmssn
AND a.cossn = c.cossn
AND a.clm_seq_num = c.clm_seq_num
Group By a.clmssn, a.cossn, d.crnt_office
Order By a.clmssn, a.cossn
If more information is needed, I have data examples but could not include them due to character size limit. Thank you for your help.
View 12 Replies
View Related
Oct 19, 2004
Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls.
currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do.
Problem:
i need to call an aggregate count on the results of another aggregate function (sum) with a group by.
*ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales
This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid.
thanks,
Tom Anderson
Software Engineer
Custom Business Solutions
View 3 Replies
View Related
Aug 14, 2006
Hi,
Could some one help me how to do
insert into test2(id,name) values ((select max(id) from test1),'user1')
in MS SQL Server
its throwing "Subqueries are not allowed in this context. Only sc
alar expressions are allowed" Exception. Help is appreciated.
Thanks,
Murali
View 2 Replies
View Related
Jan 21, 2008
Hello all:
How can I return the result of a SELECT statement from a stored procedure (function)?
CREATE FUNCTION returnAllAuthors ()
RETURNS (what do i put here??)
BEGIN
// Is this right??
RETURN SELECT * FROM authors
END
Thanks!
View 12 Replies
View Related
Oct 24, 2006
Hi all,
I have a problem with an SQL-query and I don't know what the best solution would be to solve the problem.
/*INSERT INTO WERKS (
WERKS.Z8601,
WERKS.Z8602,
WERKS.Z8603,
WERKS.Z8604,
WERKS.Z8605,
WERKS.Z8606,
WERKS.Z8607,
WERKS.Z8608,
WERKS.Z8609,
WERKS.Z8610,
WERKS.Z8611,
WERKS.Z8621,
WERKS.Z8622,
WERKS.Z8623,
WERKS.Z8624,
WERKS.Z8625,
WERKS.Z8626,
WERKS.Z8627,
WERKS.Z8628,
WERKS.Z8629,
WERKS.Z8630,
WERKS.Z8631,
WERKS.Z8632) */
SELECT
0,
Stati.z4414,
Stati.z4402,
'',
'',
'',
Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), ''),
Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), ''),
'',
'',
'',
sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End)
FROM STATI
WHERE
(Stati.z4402 Between '40000' AND 'ZONE6') AND
(Stati.z4414 Between '2005028' AND '2005028') AND
(Stati.z4417 = 'A')
GROUP BY Stati.z4414, Stati.z4402
I get the following error:
Msg 8120, Level 16, State 1, Line 25
Column 'STATI.Z4400' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I know it has something todo with the select on the table SRTAS, but what's the best way to solve this problem without the chance of getting a wrong result.
The SELECT on SRTAS is to get the "description" of STATI.Z4414 who's stored in the table SRTAS.
I only want to group on the fields that will be inserted in WERKS.Z8602, WERKS.Z8603, WERKS.Z8604, WERKS.Z8605, WERKS.Z8606. So adding STATI.Z4400 to the GROUP BY would give me wrong results?
This query is dynamicly generated from within my program from what the user selected.
Also, if there are better ways to write the query, I would be happy to get some hints and tips, but if possible without stored procedures.
Thanks in advance!
View 5 Replies
View Related
Oct 10, 2007
We have SELECT INTO queries that use the IDENTITY function to assign a sequential row number to a result set based upon a sort order. This has been helpful in SQL Server 2000 for median determination. It appears, however, that in SQL Server 2005, the row numbers are not assigned sequentially, or maybe they are assigned before the sort order is applied.
Can anyone verify whether the IDENTITY function has changed behavior between 2000 and 2005? We would prefer not to have to make changes to existing queries. Thank you.
(BTW, the workaround we found so far is to put the initial SELECT...ORDER BY in a subquery, but then we had to include a phrase like TOP 10000000 to pass syntax check. Is there a better way?)
View 4 Replies
View Related
Jul 9, 2014
I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.
Right now I am achieving this by the following case statement
SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class'
WHEN Stcount>=10 and StCount<=50THEN 'Medium Class'
WHEN Stcount>50 THEN 'Big Class'
END
FROM(
select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl
But can I do this with just one select statement?
View 2 Replies
View Related
