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
Hi,
I've an UDF which inside has two query joined by union and it 's similar to this
select * from Table1 .... (several conditions)
union
select * from Table2 .... (several conditions) (this could takes long time to run)
Since i can't write dynamic sql into UDF , i can't avoid to insert Table2 into the query but to improve permormance I've seen how costant can help me.
For Example if I change my UDF in
select * from Table1 .... (several conditions)
union
select * from Table2 Where 1=2 AND (several conditions)
Optimazer is able to skip completely the second execution, so i need to transform 1=2 into a dynamic condition for example test a field table existence.
select * from Table2 Where Exist (select * from Table3 where Field1=1)
That is why i try to write a single UDF can adapt itself to several situations using second condition only where is necessary and not always.
The problem is the dynamic condition for simple could be, wasn't recognize as costant.
For Example
select top 1 * from MyTable where (select 1)=2
select top 1 * from MyTable where 1=2
If you see the execution plan of these 2 queries you could see that the first takes more than 80% of execution time and in the second less than 20%.
Moreover the second plan use a costant scan unlike the first doesn't it.
Do anyone know a way to tell to optimizer to use a simple condition as constant ? This improve drastically my UDF performance..... :( :(
Thanks.
I have been given some data from a Mainframe (AS400?) which has some fields coded in Packed Decimal. I have been able to load the data into a SQL2005 database table, but I now need to convert the Packed Decimal data in the binary(6) field to the appropriate integer (or float) value.
The field contains values such as the following:-
0x20202020200C
0x202020022025
0x20202020DFFA
I don't know how to interpret these. Has anyone got a function that can do this for me?
I've read several articles online that explain how packed decimal works, but none tell me how to interpret the last of my three examples. Can you help?
Thanks!
Hi i'm developing a website for my final year project with ASP.net and SQL Server 2005 databse. The problem i'm facing since last week is that, i'm unable to use "Select" query to fetch the data from my SQL Server database.
i've used the following code to INSERT the database which is successfull.SqlDataSource sample= new SqlDataSource();
sample.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ToString();sample.InsertCommandType = SqlDataSourceCommandType.Text;
sample.InsertCommand = "INSERT INTO Table_Name(Fields) VALUES (@Fields)";
sample.InsertParameters.Add("Fields", FieldsTxtBox.Text);
int row = 0;
try
{
row = sample.Insert();
}catch (Exception ex)
{
// Response.Redirect ("Failed.aspx");
Label1.Text = ex.ToString();
}
finally
{sample= null;
}if (row != 0)
{Response.Redirect("Success.aspx");
}
else
{Response.Redirect("Failed.aspx");
}
It successfully updates my database fields. I've tried many codes that i've found over the internet but none worked. can anyone please tell me how to use SELECT query in the fashion as the above one?
I shall be very thankfull for the help.
Regards,
Jigzy
I am struggling with a query and need urgent help.
I have a table with fields
canid
skillLevel
percent
date
and example of the results wud be
canid skillLevel Percent Date
704 1 20 n/a
705 2 10 n/a
775 1 50 n/a
what i want to do is produce a query that would return a result from the following search critera
return all results that match
skilllevel 1 and 20 percent AND skillLevel 2 and 10 percent
I tried this below but obviously im doing something wrong
select * from simulatedHistory where
((skilllevel = 1 and percent = 20) And (skilllevel = 2 and percent = 10))
I need the query returning
canid skillLevel Percent Date
704 1 20 n/a
705 2 10 n/a
any suggestions??? any help wud be much appreciated
I have records which are like below.
create table testedit
(
editid int
,Tguid varchar(20)
,ttime numeric(4,2)
,numApp int
,numOrg int
,custid varchar(1)
)
INSERT INTO testedit values(1,'ABC',12.52,40,11,'Z')
INSERT INTO testedit values(2,'ABC',12.52,500,33,'Z')
INSERT INTO testedit values(3,'ABC',12.53,500,33,'Z')
Out of this records I would like to select only the 1st and the 3rd record. ie. My result should only have the below rows
1,'ABC',12.52,40,11,'Z'
3,'ABC',12.53,500,33,'Z'
Any help?
I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Example:
Create Table #EmpIDs (
EmplID int
)
Create Table #EmpRoles (
EMPID int,
[Code] ....
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
EmpID Role
1 Role1
2 Role2
3 Role1
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
Hi all,
I was wondering whether there is a way to show the user an error when the user did not select a specific parameter.
In my case, I have two optional parameters. The user has to select either one to view the report. If the user does not select either one, I would like to show some sort of an error page indicating so. However, all i am getting is a complete blank with no report in sight. I as a developer know this is an error as a result of missing parameters, but i was wondering whether I could produce a page or direct it to a page so that the user doesn't go all horrified at an empty page ?
Thanks
Bernard
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
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?
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 View RelatedHi 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
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.
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
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
Can I write a function inside a Select statement in sql server 7.0
If so HOW ?
Manish Mehta
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.
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?
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 RelatedCan 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.
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
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
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!
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!
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?)
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?
using below code to replace the city names, how to avoid hard coding of city names in below query and get from a table.
select id, city,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(city,
'JRK_Ikosium', 'Icosium'), 'JRK_Géryville', 'El_Bayadh'),'JRK_Cirta', 'Constantine'),'JRK_Rusicade', 'Philippeville'),
'JRK_Saldae', 'Bougie')))
New_city_name
from towns
I use a database that has user names stored in Encrypted format usingthe following API.Declare Sub Encrypt2 Lib "QPRO32.DLL" (ByVal Work As String, ByValPASSWORD As String)Every time i require the user name i have to again decrypt the nameusing the same function.My problem is that when i fetch a large number of records i have toloop through every record and call the encrypt function for eachrecord.Instead of binding the recordset to my control i need to loopthrough and fill my controlA MSHFlexGrid in Vb6.0.Is there a way out to this problem that will make my record populatiogfaster withoutout changing the current Encrypted users.Thanx in Advance
View 2 Replies View RelatedHi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.
View 4 Replies View Relatedi have a column with mulitple ids stored with commas . i want to pass ids and get data along with name from the table..how to get multiselect value in a variable in sql server function
View 4 Replies View RelatedHow Do I fix View(below) or Multi-Table select(below) to use this Function to return distinct rows via qcParent_ID?
Following Function populates a field (with concat list of related titles) with other required fields:
Create Function [dbo].openItemsIntoList(@Delimeter varchar(15),@qcparent_ID varchar(1000))
Returns Varchar(8000) as
Begin
Declare @Lists as varchar(8000);
Select @Lists = '';
Select @Lists = @Lists + itemTitle + @Delimeter From z_QClocate_openAll_Qualifier
Where @qcParent_ID = qcParent_ID;
Return Substring(@Lists,1,len(@Lists)-len(@Delimeter));
End
works perfect against single table select (returning 54 distinct rows by qcParent_ID):
Select a.qcParent_ID, a.Facility, a.Modality, openItemListToFix
From dbo.a2_qcEntryForm a
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
But data is needed from 3 tables...
- Created a VIEW that returns all (82) rows (negating distinct of the function on qcParent_ID)
- Failed Miserably Integrating Function call into a multi-table select (inexperienced with complex joins)
This VIEW returns ALL (82) rows in table:
CREATE VIEW z_QClocate_openAll AS
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
Calling like this returns ALL 82 rows (negating the functions distinct):
Select a.qcParent_ID, a.qcStatus, a.qcAlert, a.itemComplete, a.Facility, a.Modality, openItemListToFix
From z_QClocate_openAll a
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
AND THEN THERES...
Failing miserably on Integrating the Function call into This SELECT ON MULTI-TABLES:
How to integrate the Function call:
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
into the multi-table Select relationships (while maintaining Where & Order By):
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
Hello,
I am trying to figure out how to use the select maximum command in SQL Server 2005. I have already created a database and I have it populate it with multiple fields and multiple records. I Would like to create a new column or field which contains the maximum value from four of the fields. I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column.
Any help you can provide will be greatly appreciated!
Thank you,
Nathan