|
|
Good Day All
i have the Following table
DESCR CYCLETEMPLATE ACTV
===========================================
Al-Dujaili E Dr 2 7688
Al-Dujaili E Dr 4 7688
Al-Dujaili E Dr 6 7688
Allan I Mr 20 8575
Allan I Mr 21 8575
Anderson J Mr 10 7910
Anderson J Mr 11 7910
Anderson J Mr 12 7910
as you can see certain description appears more than once with different CYCLETEMPLATE field but with the same ACTV, what i want to achieve is that i need one record for all the records the match in DESCR AND ACTV and i want the CYCLETEMPLATE to be appended separated by a comma like this
DESCR CYCLETEMPLATE ACTV
===========================================
Al-Dujaili E Dr 2,4,6 7688
Allan I Mr 20,21 8575
Anderson J Mr 10,11,12 7910
i don't want to use UDF
Thank youVuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
You just need to create a join table.
CycleTemplate
ID - PK
MyTable_ID - PK
MyTable
Descr I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Thank you guys, The Common Table Expression was slow , i found a Solution and i wrote it this way
SELECT DESCR,ACTV,[CYCLETEMPLATE] = STUFF((SELECT ',' + CAST(CYCLETEMPLATE AS VARCHAR(MAX))
FROM #temp
WHERE DESCR = t.DESCR
AND ACTV = t.ACTV
FOR XML PATH('')),1,1,'')
into #TempSummary FROM #temp t
GROUP BY DESCR,ACTV
now i want to sort the CYCLETEMPLATE. I have this UDF
ALTER FUNCTION [dbo].[GetSortedString]
(
@inputString varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
if dbo.IsStringNumeric( @inputString) = 1
begin
return dbo.GetSortedStringNumeric( @inputString)
end
return @inputString --dbo.GetSortedStringNormal(@inputString)
END
and
ALTER FUNCTION [dbo].[GetSortedStringNumeric]
(
@inputString varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
declare @outputString varchar(max)
set @outputString = ''
select @outputString = ltrim(rtrim(str(item))) + ',' + @outputString from dbo.fnSplit(@inputString,',') order by item desc
declare @Results int
set @Results = (select dbo.IsMatching(@outputString,'[A-Z ]'))
if SUBSTRING(@outputString,len(@outputString),1) = ',' AND @Results = 0
begin
set @outputString = SUBSTRING(@outputString,1,len(@outputString)-1)
end
else if SUBSTRING(@outputString,len(@outputString),1) = ',' AND @Results > 1
begin
return @outputString
end
return @outputString
END
if i don't sort its Faster but if i sort its a little bit slower. This is how i incoporate it in my query. i just Build a Temp table and sort it there.
SELECT DESCR, dbo.GetSortedString(Cast(NULL AS varchar(8000))) AS CycleIdList,ACTV
INTO #TempSummary
FROM temp (NOLOCK)
GROUP BY DESCR,ACTV
ORDER BY DESCR,ACTV
ThanksVuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Hi
When using function in select query , the query will run very slowly.
So you can use views for this which will increase the performance
Create a view calling the function (the view should return what ever the function returns). \
Now in your query you can call the view rather than calling the function.
This makes performance faster.
This is my personal experiance. There may be other ways to do . Do let me know how you achieved it.....Naina
|
|
|
|
|
Thanks that is a GOOD Idea. I have resolved it with the Following statement
SELECT DESCR,ACTV,
[CYCLETEMPLATE] = STUFF((SELECT ',' + CAST(CYCLETEMPLATE AS VARCHAR(MAX))
FROM #temp
WHERE DESCR = t.DESCR
AND ACTV = t.ACTV Order by CYCLETEMPLATE
FOR XML PATH('')),1,1,'')
into #TempSummary FROM #temp t
GROUP BY DESCR,ACTV
Look at the Bold part it sorts the String.
Thanks GuysVuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Vuyiswa Maseko wrote: STUFF((SELECT ',' + CAST(CYCLETEMPLATE AS VARCHAR(MAX))
I TOLD you there was a cute way of doing this, just could not remember it. Please add this to tips and tricks with concatenate in the keywords for next time.....Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You are right.
Thanks Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I found this link[^], take a look at the recursive CTE method used.
I was sure there was a cute way of doing this but dammed if I can find it now.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SOrry, I posted this question earlier but it was in the wrong area , so i had to repost it here
mysql_query(connection, "INSERT INTO main (Name , FriendName) VALUES('Mark', 'John')");
This coding works fine when i run it in my Netbeans.
What i would like to do is instead of having to hardcore the inserted values into the query, i would like it to read from a variable. For exmaple the data to be inserted is from a string... like....
string NewName= "Monaco";
string NewFriendName = "Polo";
How do i edit my sql statements so variables can be inserted into the Insert statement so the query will read the variables and then store their values in the database. Really need some help in this
|
|
|
|
|
If you are using .NET, you may want to look into the "Parameters" property of the command object.
|
|
|
|
|
Sorry i am using netbeans. Is there a way to allow the insert query to read stored variables instead of only having to hardcode it? for example a user enters in the input he wants to store, and then the query will use the variables to be put inside the insert statement.
|
|
|
|
|
why don't you edit your original question stating your current environment so that people know what you are using and what are your problems and where you want to go.
now, it's like watching a bad ping-pong game.This signature was proudly tested on animals.
|
|
|
|
|
|
hmm no, im using c++ actually in netbeans.
|
|
|
|
|
Rather than guessing this time, let me ask straight out: what library are you using for the SQL functionality? Is it mysqlclient?
As far as I am aware mysqlclient has support for prepared statements, at least since version 5, I think. I don't think it supports binding parameter variables otherwise (I could be wrong, so it might be best to RTFM on that one).
If you don't want to or can't use prepared statements, then the question pretty much boils down to a straightforward question about string handling in C++.
|
|
|
|
|
First, an apology to CPers; I suggested moving this query to the Database forum as it seemed to be getting bogged down in the C++ forum.
The OP is using C++ (ignore NetBeans, it's just an IDE) and wants to use the above SQL statement but substitute (local)variables for the value fields. I am sure I have seen this done somewhere else something like:
string NewName= "Monaco";
string NewFriendName = "Polo";
mysql_query(connection, "INSERT INTO main (Name , FriendName) VALUES($1, $2)", NewName, NewFriendName);
I know that the above is not it but I hope you get the idea; I'm sure one of you SQL experts knows the answer.MVP 2010 - are they mad?
|
|
|
|
|
Please take a look in both where clause in store procedure
USE [INVENTORY_ MANAGEMENT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Create PROCEDURE [dbo].[Engagement_Get_StandardsByCriteria ]
-- Add the parameters for the stored procedure here
(
@ClientName NVARCHAR(255)=NULL,
@Year FLOAT=NULL,
@Application NVARCHAR(255) =NULL,
@Database NVARCHAR(255)=NULL,
@OS NVARCHAR(255)=NULL,
@QuickName NVARCHAR(MAX)=NULL
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
---This query gives me 985 rows when i didn't pass any values to parameter
SELECT [Client Name] AS Client,[Year],[Application],
[Database],OS,[MLP Heading] As Heading,
[Standard Observation] AS Observation,[Standard Impact]AS Impact,
[Standard Recommendation] AS Recommendation
FROM Engagement_Standards
WHERE (@ClientName IS NULL OR [Client Name]=@ClientName)
And
(@Year IS NULL OR [Year]=@Year)
And
(@Application IS NULL OR [Application]=@Application)
And
(@Database IS NULL OR [Database]=@Database)
And
(@OS IS NULL OR OS=@OS)
And
(@QuickName IS NULL OR [Quick Name]=@QuickName);
-- <big>But this query gives me 956 rows on same criteria mentioned above why it is so</big>
SELECT Std_MLPID,[Client Name] AS Client,[Year],[Application],
[Database],OS,[MLP Heading] As Heading,
[Standard Observation] AS Observation,[Standard Impact]AS Impact,
[Standard Recommendation] AS Recommendation
FROM Engagement_Standards
WHERE isNull(@ClientName, [Client Name]) = [Client Name]
AND isNull(@Year, [Year]) = [Year]
AND isNull(@Application, [Application]) = [Application]
AND isNull(@Database, [Database]) = [Database]
AND isNull(@OS, [OS]) = OS
AND isNull(@QuickName, [Quick Name]) = [Quick Name];
END
|
|
|
|
|
Here is how I would test this.
Change the content to * select * from table this will remove any field requirements
comment one of the where lines in each query until you find the discrepancy
Once you have identified the clause causing the problem you then need to inspect the data to find the cause.
That is a weird where clause, I have never used the 2nd style of isnull, I'd be interested in the resolution you come up with.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Could it be that any of the columns in your where clause contains Null values? In that case the second query will not return that rows because the where clause will say
Where null = null . The firs query however is will say
where null is null or null = null , which will work.Wout Louwers
|
|
|
|
|
|
yes it contains null values
Can u explain me , how sql server evaluates below clause, if the column contains null value
where null is null or null = null
and in the case isNull(@Year, [Year]) = [Year]
|
|
|
|
|
Where null is null or null = null
Evaluates to
Where true or false
Evalutes to
Where true
where isNull(@Year, [Year]) = [Year]
evaluates to (is @year is null and [year] is null)
where IsNull(Null, Null) = Null
evaluates to
Where Null = Null
evaluates to
Where false
I hope this helpsWout Louwers
|
|
|
|
|
Hey guys
I want to insert values into a MySql database. The following line works fine:
mysql_query(connection, "INSERT INTO main (NumofShows , Title) VALUES('12', 'Home')");
What i would like to do is instead of having to hardcore the inserted values into the querty, i would like it to read from a variable. For exmaple the data to be inserted is from a string... like....
string NewTitle = "Monaco";
string NumofShows = "2";
How do i edit my sql statements so variables can be inserted into the Insert statement so the query will read the variables and then store their values in the database. Really need some help in this
|
|
|
|
|
Use a parameters with the command object so you don't hard code values in the SQL statement.
|
|
|
|
|