|
Tridip Bhattacharjee wrote: how to query address field in my xml or how to query Options field,how query CompanyOrName.
A XML-query will look somewhat more complicated then a "regular" query. I hope that the example below provides a good impression of the used construct;
DECLARE @ShipXML XML
SET @ShipXML =
'<OpenShipments>
<OpenShipment>
<NumberOfPackages>1</NumberOfPackages>
<ShipmentActualWeight>5</ShipmentActualWeight>
<DescriptionOfGoods>Car Parts</DescriptionOfGoods>
</OpenShipment>
<OpenShipment>
<NumberOfPackages>2</NumberOfPackages>
<ShipmentActualWeight>3</ShipmentActualWeight>
<DescriptionOfGoods>Bike Parts</DescriptionOfGoods>
</OpenShipment>
</OpenShipments>'
SELECT shs.sh.value('NumberOfPackages[1]','bigint') AS NumberOfPackages
,shs.sh.value('ShipmentActualWeight[1]','bigint') AS ShipmentActualWeight
,shs.sh.value('DescriptionOfGoods[1]','varchar(20)') AS DescriptionOfGoods
FROM @ShipXML.nodes('OpenShipments/OpenShipment') shs(sh)
WHERE shs.sh.exist('.[DescriptionOfGoods != "Car Parts"]') = 1 The example was built using sample-code from MSDN[^].I are Troll
|
|
|
|
|
We currently have a Crystal Report that uses a SQL 2005 database as it's datasource.
Both start date and end date are the only two parameters that are sent to stored procedure. These dates can vary - meaning it's not always MTD or YTD.
The user would like the report details to be exported to Excel.
I've used DTS in the past, but haven't had an opportunity to play with SSRS or SSIS.
Which should I focus on for this task?
We do have a Sharepoint portal and ideally, the process can start there by clicking a link.
How would the parameters be inputted by the user?
Thanks in advance.
|
|
|
|
|
One of the (minor) reasons we dumped (on) Crystal was the export to excel was so crappy.
We use SSRS and the excel export is one hell of a lot better than Crystal used to be. Having said that SSIS does not support data dumps, it shouldn't it is a REPORTING tool. I repeat this line to my management weekly, it does not help. They still insist that data dumps are a requirement.
So one of our devs has extended SSRS to export csv files via RS. This is not possible in CR and is pretty ugly even in SSRS, it means hacking the UI to a custom page that calls a stored proc that writes the file to a folder and then displays a link for the user to download the file. As I said ugly, but it works without having to pull major volume to the UI to export.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi
I Execute the following sql statement successfully for a Access Database.
CREATE TABLE Scores (
StudentCode Text(9) NOT NULL,
CourseCode Text(7) NOT NULL,
SemesterCode Text(3) NOT NULL,
Score Double
);
CREATE TABLE Courses (
CourseCode Text(7) NOT NULL,
CourseName Memo NOT NULL,
CourseTypeID Integer NOT NULL,
CourseCategoryID Integer NOT NULL
);
ALTER TABLE Scores ADD CONSTRAINT PK_Grades
PRIMARY KEY (StudentCode, CourseCode, SemesterCode);
ALTER TABLE Courses ADD CONSTRAINT PK_Courses
PRIMARY KEY (CourseCode);
ALTER TABLE Courses
ADD CONSTRAINT UQ_Courses_CourseCode UNIQUE (CourseCode);
But Finally I Get Error message(Syntax error in CONSTRAINT clause.) on this satement that is at the end of my execution list.
ALTER TABLE Scores ADD CONSTRAINT FK_Scores_Courses
FOREIGN KEY (CourseCode) REFERENCES Courses (CourseCode)
ON UPDATE CASCADE;
And I don't know Why?
Please Help me on how to establish a relation in access database through sql statement?
|
|
|
|
|
hi,
i wrote msAccess query like.
SELECT T1.[CID],T2.[Name]
FROM T1 INNER JOIN T2 ON T1.TID =T2.TID
WHERE T1.CID in ('select CID from T3')
here i'm getting error :
"Cannot join on memo,ole,or hyperlink object (T1.TID =T2.TID )"
please send solution for this
Thanks & Regards,
Vishnu.
|
|
|
|
|
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.
|
|
|
|