|
Thanks for the suggestion, Mika, and I appreciate the conversation you and I have been having on this. It was very helpful.
|
|
|
|
|
Mike Ellison wrote: It was very helpful.
Glad to hear that  You're welcome.
|
|
|
|
|
Hi,
I'm trying to use the query below in MSSQL.
<br />
SELECT [mxDelete],[mxContactID] <br />
FROM mxContact.dbo.Contacts <br />
WHERE mxContactID IN <br />
<br />
(SELECT [mxContactID], [mxzCopies]<br />
FROM <br />
(SELECT [mxContactID], [mxzCopies]<br />
FROM mxContact.dbo.Associations_Groups_to_Contacts <br />
WHERE mxGroupID='35EA110C-FE44-4A85-8D9A-AE973E2C5F85' <br />
OR mxGroupID='3CF66F58-F8E4-44CD-84A0-CB62F85E129B') <br />
AS COL<br />
GROUP BY mxContactID HAVING (COUNT(mxContactID) > 1))<br />
My question is, how can I make it so that the code in bold will give me the mxzCopies column as well.
If I select just the contactID, then it works fine, but with the addition of mxzCopies, it gives me:
Msg 8120, Level 16, State 1, Line 1
Column 'COL.mxzCopies' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Cheers,
Mark.
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
Nevermind figured it out.
<br />
SELECT mxContactID, mxzCopies<br />
FROM <br />
(<br />
SELECT [mxContactID], [mxzCopies]<br />
FROM mxContact.dbo.Associations_Groups_to_Contacts <br />
WHERE mxGroupID='35EA110C-FE44-4A85-8D9A-AE973E2C5F85' <br />
OR mxGroupID='3CF66F58-F8E4-44CD-84A0-CB62F85E129B'<br />
)<br />
AS COL<br />
GROUP BY mxContactID, mxzCopies HAVING (COUNT(mxContactID) > 1)<br />
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
Click here to view my blog
|
|
|
|
|
whenever i was trying to do this .. exception occurred.
Please help me out.
Thanks in Advance.
|
|
|
|
|
In short, no.
However, you can export the database in 2005 (scripts, csv etc) and import the data in 2000 as long as you don't use 2005 specific features or datatypes.
|
|
|
|
|
XQuery - its a tricky beast.
Have a look at the script below, I'm creating a simple xml document in a single row in a temporary table. What I want to do is update EVERY value element and set it to D. There's three, A, B and C, and I want all three to be updated to D.
The script complains that I'm attempting to update more than one node. Does this mean I have to use some iterative approach to do this? Any ideas anyone?
CREATE TABLE #temp ( myXml xml )
INSERT INTO #temp VALUES ('<document><value>A</value><value>B</value><value>C</value></document>')
select * from #temp
update #temp set myXml.modify('replace value of (//value/text()) with "D"')
drop table #temp
Regards,
Rob Philpott.
|
|
|
|
|
Rob Philpott wrote: Does this mean I have to use some iterative approach to do this?
Yes, you'll have to iterate through the nodes. SQL Server doesn't support multiple node updates at once.
One way you could do this is something like this:
declare @counter int
set @counter = 1
while (@counter <= 3)
begin
update #temp
set myXml.modify('replace value of (/document/value[sql:variable("@counter")]/text())[1] with "somevalue"')
set @counter = @counter + 1
end
|
|
|
|
|
Perfect. Thanks Mika.
Regards,
Rob Philpott.
|
|
|
|
|
|
Hi,
I am considering using Mono to develop an specific purpose image browser to run on Windows, MAC/OS and Linux.
To meet performance requirements the application requires a database to store thumbnails and search data.
I am looking for suggestions for database products that run on Windows, MAC/OS and Linux that do not require use of technologies such as VMWare or Parallels, which I could use from within a Mono/C# environment.
The application along with the database and the images will be distributed at no cost to "qualified" users, so I'm looking for a zero cost run time database system. The application must run stand-alone - i.e. no network, no servers,
The thumbnails and search data would be imported from the original image files.
The search data would be sourced from embedded IPTC metadata such as keywords and supplemental categories.
The thumbnail and IPTC metadata are injected into the image files using a commercial Digital Asset Management (DAM) application.
I don't insist that the "database" be SQL compliant, b-tree indexes would probably suffice. As long as the same database or index files can be used from within the same Mono application running on Windows, MAC/OS or Linux.
TIA
|
|
|
|
|
|
Hi Mika,
An earlier experience with MySQL was not a happy one so I'd more or less dismissed it, also like many of the others its overkill for my needs.
However SQLite looks good, I like the idea of a using library rather than a separate process. Wonder if anyone's used in a Mono/C# app. I found this[^] nearby, it should get me off the ground.
Thanks heaps - PhilD 
|
|
|
|
|
|
Mika Wendelius wrote: If you don't need a "byrocratic" installation, SQLite is most propably a good choice
Nuh, I prefer "graphitic" installs, they're much easier to rub out.
Thanks for doing my research, I put myself on the ximian mailing lists earlier this evening.
rgds PhilD
|
|
|
|
|
Hello everybody,
i have 3 tables Student, Instructor, and Allocation. I have 5 students and 2 instructors how can I allocate 2 instructors to 5 students for project supervision.
StudentId StudentName
1 Learner
2 Paul
3 Griffin
4 Inese
5 Kelly
InstructorId InstructorName
1 Sharma
2 Bill
this is out put table
AllocationId InstructorName StudentName
001 Sharma Learner
002 Sharma Paul
003 Bill Griffin
004 Bill Inese
005
where the Kelly will go?
please any body could give me a clue how should I do it either with queries or mathematical calculations.
let suppose if I write a stored procedure 'Allocation' which take two inputs students and instructors like Allocation 5,2 how it will work.
thnx in adv for any kind help.
regards
learner
|
|
|
|
|
usually, when creating this sort of association you use the ID fields from Student and Instructor table.
But you've answered your own question - If Kelly's instructor is Bill then:
001 Sharma Learner
002 Sharma Paul
003 Bill Griffin
004 Bill Inese
005 Bill Kelly
|
|
|
|
|
thnx for your response
actually it is my fault that i didn't explain properly. Allocation table is only the format that how i need to display it but I'm not sure how can I get this format as (Allocation Table)
cos I got double minded that I should use joins or should i do mathematical calculation first like this
create proc AllocatIns
as
Declare @NumOfRec int,@NumOfRe int, @Result float
select @NumOfRec= count(*) from Student
select @NumOfRe= count(*) from Instructor
Print @NumOfRec
Print @NumOfRe
select @Result =@NumOfRec % @NumOfRe
Print @Result
if you give me the idea how to allocate instructor to students that would be helpful. cos it is all automated process no need for operator to allocate one by one. as we do in GUI Number of students should be assigned from dropdownlist. may be I m not thinking in right direction.
thanx in adv.
regards
learner
|
|
|
|
|
You've still provided a shockingly bad description of your problem and your naming of variables does not help! I think you want to allocate any given number of students to an instructor.
If that is right the process is easy. Insert x number of students who have not been allocated an instructor. I have assumed you hgave changed the Allocation table to hold the StudentId and InstructorId as already suggested. I have also assumed that AllocationId is an identity field.
CREATE PROC AllocateInstructor
@numStudents INT,
@InstructorId INT
AS
INSERT INTO Allocation (InstructorId, StudentId)
SELECT TOP @numStudents
@instructorId,
studentId
FROM Students
WHERE studentId NOT IN(SELECT studentId FROM Allocation)
|
|
|
|
|
your assumption is absolutely fine.
let me try to explain this time may be better this time
we have two table student and instructor
number of records can be change of both student and Instructor tables
lets suppose
Number of students Number of Instructor
30 11
42 9
21 13
16 5
1-what we need to do we have to allocate 11 instructor to 30 students.
2- 9 instructor to 42 students.
3- 13 instructor to 21 students.
4- 5 instructor to 16 students.
let suppose 1,2,3,and 4 represents 2001,2002,2003,2004.
every year number of students and instructor changes and we have to allocate instructor to new students.
any way I really appreciate your patience and time you gave to understand my requirements.
regards
learner.
|
|
|
|
|
How to start a stored procedure, type code and End it? And how to execute the procedure in SQL? Can anyone explain it?
|
|
|
|
|
whether you try to search on google ? if no, then click Google[^]
|
|
|
|
|
|
I think you really need to buy a book, or at least google for some examples as this is one of the most basic parts of database programming. If you don't know this, I suspect you will not know what T-SQL to use in it either.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi Team,
We need a SSIS functional to move file from source (Local path) to FTP location. (Destination )
The task have been done successfully and move file via objects FTP TASK and ForEach Control provided by SSIS.
Now the given functional is quiet changes, that is we need to create folder date wise dynamically on FTP and move the files.
So could it be feasible to achieve the target and help me out from this scenario.
Thanks and Regards
Tarun Singh
tksingh@zenta.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
