Database

9-Feb-10 4:14

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 you

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/

Not Active9-Feb-10 5:52

Not Active

9-Feb-10 5:52

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

Vimalsoft(Pty) Ltd9-Feb-10 19:41

9-Feb-10 19:41

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

Thanks

nainakarri10-Feb-10 17:55

nainakarri

10-Feb-10 17:55

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

Vimalsoft(Pty) Ltd10-Feb-10 19:35

10-Feb-10 19:35

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 Guys

Mycroft Holmes10-Feb-10 20:02

Mycroft Holmes

10-Feb-10 20:02

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

Vimalsoft(Pty) Ltd10-Feb-10 20:06

10-Feb-10 20:06

You are right.

Thanks Smile | :)

Reading variables in Insert Query

Mycroft Holmes9-Feb-10 13:38

Mycroft Holmes

9-Feb-10 13:38

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

gregarion9-Feb-10 2:47

gregarion

9-Feb-10 2:47

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

loyal ginger9-Feb-10 2:54

loyal ginger

9-Feb-10 2:54

If you are using .NET, you may want to look into the "Parameters" property of the command object.

gregarion9-Feb-10 2:58

gregarion

9-Feb-10 2:58

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.

Maximilien9-Feb-10 4:18

Maximilien

9-Feb-10 4:18

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.

David Skelly9-Feb-10 5:27

David Skelly

9-Feb-10 5:27

You say you are using Netbeans which is a Java IDE, so I'm guessing that's the language in question.

If you are using JDBC then you can use a PreparedStatement which allows you to bind variables:

http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html[^]

gregarion9-Feb-10 5:29

gregarion

9-Feb-10 5:29

hmm no, im using c++ actually in netbeans.

David Skelly10-Feb-10 6:04

David Skelly

10-Feb-10 6:04

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++.

doubt in multiple criteria

Richard MacCutchan9-Feb-10 6:32

Richard MacCutchan

9-Feb-10 6:32

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?

Mogamboo_Khush_Hua9-Feb-10 2:44

Mogamboo_Khush_Hua

9-Feb-10 2:44

Please take a look in both where clause in store procedure

USE [INVENTORY_ MANAGEMENT]
GO
/****** Object:  StoredProcedure [dbo].[GetEngagementStandardsByCriteria]    Script Date: 02/05/2010 15:25:25 ******/
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

Mycroft Holmes9-Feb-10 13:21

Mycroft Holmes

9-Feb-10 13:21

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

WoutL9-Feb-10 20:12

WoutL

9-Feb-10 20:12

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

Hesham Amin9-Feb-10 20:34

Hesham Amin

9-Feb-10 20:34

I agree to WoutL's post. But I want to clarify that null comparisons fail. unless you have ANSI_NULLS set to OFF.
So take care of nulls in your queries. Also check: Another place when NULL gets tricky[^]

Hesham A. Amin
My blog
twitter: @HeshamAmin

Mogamboo_Khush_Hua10-Feb-10 5:31

Mogamboo_Khush_Hua

10-Feb-10 5:31

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]