|
Now I need you to tell me how to write method for executing a procedure and returnd a dataset
The stored procedure (statement outlined at the bottom of the word document) will give you a list of orders (first table) and order items (second table) that contain mail lists.
The first table will contain the order info itself. This is where you will get the additional fields for the “Master Mail list file” such as order number, name of person who placed the order etc. The second table will contain the specific item info (such as item name/description).
You will use the Order number (the actual column name is SOPNumbe) to reference the path where the order files are stored.
An order folder will have one or more package folders.
Add a method to DataObjects.FeedsDO.vb to execute the stored procedure and return a DataSet.
So once you have your dataset.
the below one is the SQl statement so I also need Code for writing a procedure which gives me two records
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ClientCode varchar(5), @ClientID int
SELECT @ClientCode = 'cmc'
SELECT TOP 1 @ClientID = ClientID FROM ODClient WHERE Code = @ClientCode AND ParentID IS NULL
DECLARE @Orders TABLE (OrderID bigint)
--Set the id of the mdb output data feed for reference in next statement
DECLARE @DataFeedID int
SELECT @DataFeedID = 5
INSERT INTO @orders
SELECT o.OrderID FROM ODOrder o
OUTER APPLY (SELECT TOP 1 oh1.CreateDate, oh1.OrderID, oh1.StatusID FROM ODOrderHistory oh1 WHERE oh1.OrderID = o.OrderID AND oh1.StatusID = 22) oh
WHERE o.ClientID IN (SELECT ChildID FROM dbo.funGetClientChildren(@ClientID))
AND o.OrderID IN
(SELECT OrderID FROM ODOrderShipment os
INNER JOIN ODOrderItem oi ON oi.OrderShipmentID = os.OrderShipmentID
WHERE oi.HasMailingList = 1)
AND
(oh.StatusID = 22 AND oh.CreateDate >= (SELECT MAX(CreateDate) FROM ODDataFeedLog WHERE DataFeedID = @DataFeedID AND DataFeedLogEventTypeID=3) OR NOT EXISTS (SELECT 1 FROM ODDataFeedLog WHERE DataFeedID = @DataFeedID))
SELECT o.OrderID, o.SOPNumbe, su.LoginID, c.FirstName, c.LastName,
StatusDate = (SELECT TOP 1 CreateDate FROM ODOrderHistory oh WHERE oh.OrderID = o.OrderID ORDER BY CreateDate DESC)
FROM ODOrder o
INNER JOIN ODSysUser su ON su.SysUserID = o.SysUserID
INNER JOIN ODContact c ON c.ContactID = su.ContactID
WHERE o.OrderID IN (SELECT OrderID FROM @Orders)
SELECT * FROM ODOrderShipment os
INNER JOIN ODOrderItem oi ON oi.OrderShipmentID = os.OrderShipmentID
INNER JOIN ODPackage p ON p.PackageID = oi.PackageID
WHERE oi.HasMailingList = 1
AND os.OrderID IN (SELECT OrderID FROM @Orders)
|
|
|
|
|
Sorry, but this is obviously a homework question. Have a try and post your code if it doesn't work and someone will probably help you. Nobody will do your homework for you.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Friends,
I have a data like in a name field like below
Martins
Martin's
how can I get both record when some one passes in search input string only Martins
i.e if I pass Martins It should return both Martins and Martin's
it is a specific case, think in general where uer will pass Martins not Martin.
johns not john only
can i remove ( ' ) it from field in where condition if yes then how?
If you need further explination please do not hesitate to ask.
modified on Thursday, May 15, 2008 8:25 AM
|
|
|
|
|
select [name] from myTable where [name] like '%Martin%'
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Use like operator like this:
Select * from tablename where name like 'martin%'
Piyush Vardhan Singh
p_vardhan14@rediffmail.com
http://holyschoolofvaranasi.blogspot.com
http://holytravelsofvaranasi.blogspot.com
|
|
|
|
|
select * from myTable<br />
where soundex(ColValue) = soundex('john''s')
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
select organisationname from organisation
where soundex(organisationname) = soundex('martins')
I used your query , my question was if Input martins it also return record including martin and martin's.
so its not returnt record which have martin's in it.
|
|
|
|
|
I have tested by giving input string 'martins' and 'martin''s' and query returns two rows with values martin and martin's (those values are stored on database).
select organisationname from organisation<br />
where soundex(organisationname) = soundex('martins')
and the next query gives me same result
select organisationname from organisation<br />
where soundex(organisationname) = soundex('martin''s')
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Dear all,
I need one help for installing sql server 2000 reporting services.
Iam getting the following error while installing Sql server 2000 Reporting service software sp2.
The Upgrade patch cannot be installed by the windows installer service because the program to be upgraded may be missing, or the upgrade patch may update a different version of the program. verify that the program to be upgraded exists on your computer and that u have the correct upgrade patch.
I have downloaded reporting service from Microsoft site.
I have the following with these
--Visual studio 2005
--Sql server 2000 with SP4
Please help me on this.
How to install sql server 2000 reporting service.
Regard's
Veeresh
i want to join this group
|
|
|
|
|
Hi Veeresh,
Please know that:
1. SQL Server Reporting services require SQL Server SP3a for SQL Server 2000. Install that first. You can dowload this from the following location.
http://www.microsoft.com/sql/downloads/2000/sp3.asp
After installing, do a reboot.
2. If you have installed whidbey or longhorn bits, then you need do some registry hack. Otherwise Reporting services will give an error “ASP.NET v1.1 not Installed” so it wont install server components. This error is coming because when you install whidbey, ASP.NET version is mapped to 2.0*.. However reporting services requires 1.1 only.. I dont why such requirement is there as 1.x ASP.NET pages will work in 2.0 version also..
To avoid this just change this reqistry setting,
\\HKLM\SOFTWARE\Microsoft\ASP.NET\RootVer] = "1.1.4322.573"
For more details, refer this blog
3. If you provide service account for running reporting service, then you will get a error “install an hotfix XXXX if you want to run this service in NT Service account”.. To avoid this give any domain account credentials.
4. After you install reporting service, install reporting service service pack1. you can download it from here,
http://www.microsoft.com/downloads/details.aspx?FamilyId=580FEBF7-2972-40E7-BCCF-6CD90AC2F464&displaylang=en
Hope this helps 
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
Hi,
Thanks a lot.I have one more thing.Can i use sql server 2000 reporting service with visual studio 2005.?
Regard's
Veeresh
i want to join this group
|
|
|
|
|
Hi Experts,
I have to find discrepancy between two tables
I have Table 1 with columns
OrderID
Date
Quantity
Symbol
Table 2 with columns
OrderID
Date
Quantity
Symbol
I want a script to find out a list of OrderIDs present in table1 but not in table 2 and a list of OrderIDs present in table2 but not in table 1.
Could any one help me in writing this query?
Thanks in advance
|
|
|
|
|
You can use the IN oprator for that:
SELECT *<br />
FROM Table1<br />
WHERE OrderID NOT IN<br />
( <br />
SELECT OrderID<br />
FROM Table2<br />
)
and then reverse the tabels for the other way:
SELECT *<br />
FROM Table2<br />
WHERE OrderID NOT IN<br />
( <br />
SELECT OrderID<br />
FROM Table1<br />
)
|
|
|
|
|
If you are on SQL Serer 2005
then you can use Intersetion, Difference and Union rules of Set Theroy.
select * from Table1
intersect
select * from Table2
It will give you the common records in both
select * from Table1
EXCEPT
select * from Table2
above will give you difference
|
|
|
|
|
When it is just the orderid's it is faster (performance wise) to just use the IN syntax.
|
|
|
|
|
I have this query that suppose to show the first name and last name of users (workers) in a milestone where the names of the users should not be repeated
For example if john smith is repeated two times it should be selected once in the display.
I have written this statement but it has an error.
SELECT Profile.FirstName, Profile.LastName
FROM AssignedTo INNER JOIN
Profile ON AssignedTo.Username = Profile.Username CROSS JOIN
Milestone
WHERE (Milestone.MilestoneID = @MilestoneID) AND (AssignedTo.ProjectID = @ProjectID)
GROUP BY Profile.Username
Note: we can distinguish between users by using Username
Thank you in advance fro your corporation
|
|
|
|
|
The problem there is the Group By clause.
A Group by is for aggregation (e.g. Averages, Sums, etc.) and your first name and last name aren't part of an aggregation.
Try adding a DISTINCT clause after your select. See if that helps.
SELECT DISTINCT
Profile.FirstName,
Profile.LastName
FROM
AssignedTo
INNER JOIN Profile ON AssignedTo.Username = Profile.Username
CROSS JOIN Milestone
WHERE
(Milestone.MilestoneID = @MilestoneID)
AND (AssignedTo.ProjectID = @ProjectID)
Dad always thought laughter was the best medicine, which I guess is why several of us died of tuberculosis.
I can picture in my mind a world without war, a world without hate. And I can picture us attacking that world, because they'd never expect it.
|
|
|
|
|
Just so you know the following is no different than the query you wrote. In fact, using a group by in some instances is faster than using a distinct (atleast I read that somewhere about Oracle, although it could have changed with a new release of the database).
SELECT Profile.FirstName, Profile.LastName
FROM AssignedTo
INNER JOIN Profile ON AssignedTo.Username = Profile.Username
CROSS JOIN Milestone
WHERE (Milestone.MilestoneID = @MilestoneID)
AND (AssignedTo.ProjectID = @ProjectID)
GROUP BY Profile.FirstName, Profile.LastName
Mike Lasseter
|
|
|
|
|
Ya, I'm not sure about Oracle but as far as I know with Microsoft SQL there's no performance difference as the execution plan is normally identical.
It's purely personal syntax preference. I've always found DISTINCT easier to read than a heap of groups.
Dad always thought laughter was the best medicine, which I guess is why several of us died of tuberculosis.
I can picture in my mind a world without war, a world without hate. And I can picture us attacking that world, because they'd never expect it.
|
|
|
|
|
Hi.
We have an issue where we have a (hummingbird doc management) database that has a collation that is different to the SQL server's (2005) collation. In this case the database's collation has to match the server's collation.
Changing the collation on the database will be a bit of a nightmare and I was looking changing the server's collation.
It is really necessary to rebuild[^] the master database?
Could I not just backup/detach all the databases, uninstall SQL, reinstall it with the right collation and then restore/re-attach the databases? What sort of things should I look out for if I do that?
Thanks,
Nick
Dad always thought laughter was the best medicine, which I guess is why several of us died of tuberculosis.
I can picture in my mind a world without war, a world without hate. And I can picture us attacking that world, because they'd never expect it.
modified on Thursday, May 15, 2008 3:37 AM
|
|
|
|
|
Dears,
I've a web application in which every this is working okay offline, but after piblishing the app online i had a problem saving arabic data from the application into the database which in my case sql server 05 it is saved as ???????????????????? 'question marks' columns collations is set to Arabic_CI_AS and my fields are all nvarchar or nchar and when i manually enter any arabic data into the database server online it is saved correctly and i can also read it correctly from my application
it would be gr8 if some one has got a solution for this.
Thanks in advance
Yours
|
|
|
|
|
Asked in three forums
Does it matter what I write here? Someone just has to say something about it.
|
|
|
|
|
Hi all,
i want to know how can i save a picture in Access database and,
see when i retrived the information from database.
i m using a String for this,and store the path of picutre when i browse the file for open and then store it in database.
when i want to retrived the information from database then i reterive this string and set picutre with this help.
Is it right way?
Thanks in advance.
IN A DAY, WHEN YOU DON'T COME ACROSS ANY PROBLEMS - YOU CAN BE SURE THAT YOU ARE TRAVELLING IN A WRONG PATH
|
|
|
|
|
|
Hi guys
I would like to query the following but sql does not allow this
<br />
CREATE PROCEDURE sp_GetMinPunchForShift <br />
@fromDateTime DATETIME,<br />
@toDateTime DATETIME<br />
AS<br />
SELECT <br />
MIN(Tap_DateAndTime_DT), <br />
Tap_IsInPunch<br />
FROM TimeAtendancePunches<br />
WHERE Tap_DateAndTime_DT >= @fromDateTime AND <br />
Tap_DateAndTime_DT <= @toDateTime AND <br />
Tap_IsInPunch IS NOT NULL<br />
<br />
GO<br />
What i would like to do is return only the field "Tap_IsInPunch" where i find the min(Tap_DateAndTime_DT) in the where clause.
Thanx in advance
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
