|
So one of your fields is in this data format, check each field in the join and change the data type of the offending field. Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I need to query a csv file with C++ .NET and get a distinct list which is ordered by multiple criterion. If I write regular SQL, then i get the error "order by clause conflicts with distinct". If I use the prefix "SELECT * FROM ( SELECT" and suffix ")", then the order is lost.
My current workaround is to manually modify the results to a distinct set using C++.modified on Wednesday, February 10, 2010 5:24 AM
|
|
|
|
|
What is the exact query you are using? "No matter how many fish in the sea; it will be so empty without me." - From song "Without me" by Eminem
|
|
|
|
|
The query with the distinct is below. However, this returns an error unless distinct is removed. I am using Provider=Microsoft.Jet.OLEDB.4.0
SELECT DISTINCT ID_BB_SEC_NUM_DES
FROM Curncy_FORWARD2010020801.csv
WHERE ID_BB_SEC_NUM_DES LIKE '%HKD%'
AND NAME NOT LIKE '%/%'
AND NAME NOT LIKE '%+%'
AND UCASE(NAME) NOT LIKE '%FWD POINTS%'
AND UCASE(NAME) NOT LIKE '%IMPLIED YLD%'
AND UCASE(NAME) NOT LIKE '%OUTR%'
AND LEN(ID_BB_SEC_NUM_DES) BETWEEN 5 AND 10
AND UCASE(MARKET_SECTOR_DES( = 'CURNCY'
AND SECURITY_TYP='FORWARD'
AND ID_BB_SEC_NUM_SRC IS NOT NULL
AND RIGHT(ID_BB_SEC_NUM_DES,1) IN ('W','M','Y')
ORDER BY
RIGHT(ID_BB_SEC_NUM_DES,1)<>'W' DESC,
RIGHT(ID_BB_SEC_NUM_DES,1),
LEFT(RIGHT(ID_BB_SEC_NUM_DES,LEN(ID_BB_SEC_NUM_DES)-3),LEN(ID_BB_SEC_NUM_DES)-4)+0
|
|
|
|
|
Hi all,
is there a way to restrict the form.showdialog method (I mean the blockage) to the calling parent-window and not to the whole app ?
thank you !
frankmodified on Wednesday, February 10, 2010 3:44 AM
|
|
|
|
|
fracalifa wrote: Hi all,
is there a way to restrict the form.showdialog method to the calling parent-window and not to the whole app ?
thank you !
frank
From a database, no!
|
|
|
|
|
sorry I am in the wrong message board...
|
|
|
|
|
Hi..
Im using following query for getting DB Backup But i got error
Pls help me.. thhis is correct for getting DB backup
else pls post some code DB backup & restore
Thanks in advance..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- backup a database
ALTER PROCEDURE [dbo].[sp_BackupDatabase]
@databasename varchar(32),
@path varchar(256),
@filename varchar(64)AS
set nocount on
declare @sql varchar(4000)
select @sql ='BACKUP DATABASE ' + ltrim(rtrim( @databasename ))
select @sql = @sql +'TO DISK = ''' + ltrim(rtrim(@path)) + ltrim(rtrim(@filename)) + ''' '
select @sql = @sql +'WITH INIT'
--print @sql
execute ( @sql )
select 'Database successfully backed up!' [Result]
|
|
|
|
|
|
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?
|
|
|
|