Having trouble distilling something down to a single query

Ok, lets say I have 2 tables, A & B.

Lets say table a has the key A.record_id, and the field emp.

Say table b has the key B.record_id, a foreign key B.a_id that links it to table A, and the field B.date. Now, I want to join these tables as so:

SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" TABLE_A LEFT OUTER JOIN "DBA"."B" TABLE B ON
A."RECORD_ID" = B."A_ID"}

You see, I want a list of all A.record_id, whether or not I get a return from the B table.

The problem arises when I want to limit the dates via B.date. It's clear to me what the problem is here, I just don't know a way around it.

WHERE
(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))

So basically, now I'm not getting any a.record_id's for a's that are linked to a b that fall outside of that date range.

Summing up I want...

All A + B where there is a B.date in that range
No A+B for results that are not within the entered date range.
All A's, regardless of if there is a linked B.
All A's, even if there are linked B's outside of the date range.
All in 1 statement (due to environment limitations).

Thanks for your help. I'm pretty much self taught here, so I apologize for not having the language knowledge to make this question more concise. Of course if I knew better how to explain what I'm trying to do then I'd probably know how to do it. ;-)

Mock Sample Data

table A
A001 bill
A002 bill
A003 bill
A004 frank
A005 frank
A006 bob

table B
B001 A001 1/1/2004
B002 A001 1/15/2004
B003 A001 4/1/2004
B004 A003 5/1/2004
B005 A004 1/1/2004
B006 A005 3/3/2004

Mock Results

A001 bill B001 1/1/2004
A001 bill B002 1/15/2004
A002 bill NULL NULL
A003 bill NULL NULL
A004 frank B004 1/1/2004
A005 frank NULL NULL
A006 bob NULL NULL


edit: added mock data/resultsI'd use:(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))-PatP|||Sorry, that's how it's in there now, editing above to reflect.

Not the problem.|||What version of which database engine are you using?

-PatP|||Using Sybase 9.|||I just noticed some apparent inconsistancies in your query. Could you post the entire query as you are submitting it so that I can try it? I'm using version 8, but I'd expect that to be close enough.

-PatP|||SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" A LEFT OUTER JOIN "DBA"."B" B ON
A."RECORD_ID" = B."A_ID"}
WHERE
(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))

Mock Current Results From Earlier Mock Data

A001 bill B001 1/1/2004
A001 bill B002 1/15/2004
A002 bill NULL NULL
A004 frank B004 1/1/2004
A005 frank NULL NULL

Thanks for looking at this.|||Give up? ;-)|||Try a LEFT OUTER JOIN to a Subquery that restricts your table B.

SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" TABLE_A LEFT OUTER JOIN
(Select *
FROM "DBA"."B" TABLE B where ((B.DAte <='1/31/2004' and B.Date >='1/01/2004') OR B.DAte is NULL)) as S1
on A."RECORD_ID" = S1."A_ID"}

I may have a typo with the brackets up there, but something like that should work.

The key is that you are creating a subquery with results restricted to your data range, and then naming that subquery S1. Then the results of S1 are joined to table A.|||1. All A + B where there is a B.date in that range
2. No A+B for results that are not within the entered date range.
3. All A's, regardless of if there is a linked B.
4. All A's, even if there are linked B's outside of the date range.
5. All in 1 statement (due to environment limitations).
Unless I am missing something, - there are contradicting conditions in your requirements:

If #1 is to be met Then #3 & #4 cannot be
If #2 is to be met Then #4 cannot be
If #3 is to be met Then B.date is NULL, thus #1 cannot be
If #4 is to be met...see above

Can you clarify?

Having problems creating an SQL statement

I am having trouble getting the SQL statement to return stats from a survey the way I want them. The table is set up as:

ID Q1 Q2 Q3 Q4

Responses for each question (Columns Q1 – Q4) will be a numerical value between 1-5. I want to count how many 1s, 2s, 3s, etc. I have tried different joins, self joins, unions and sub selections but cannot get the correct output.

I would like to get the output for each question as a single record, and if possible have a final column with an average for the question. But I can do that in the data binding if needed.

Qs Ones Twos Threes Fours Fives

Q1 #of 1s #of 2s #of 3s #of 4s #of 5s

Q2 #of 1s #of 2s #of 3s #of 4s #of 5s

Q3 #of 1s #of 2s #of 3s #of 4s #of 5s

Any tips or SQL sample statements would be greatly appreciated.

It looks like 2 pivots will be needed to to transpose the data from your table layout to your desired output layout. If you're using Sql 2005, there is a Pivot feature but here i'll show you how you could do this using syntax that will work for Sql2000 or Sql2005.

I'll break each step down into it's own View. Each view will build off of the previous view(s).

The 1st View is named: VIEW_SurveyRotation1
In this View, we turn all the column headings (Q1, Q2 etc..) into data values and move all the answers into a single Answer column. This is actually the opposite of what we normally consider a pivot, but i still tend to think of it as a [reverse] pivot.

SELECT'Q1'AS Qs, Q1AS answerFROM dbo.SurveyUNIONALLSELECT'Q2'AS Qs, Q2AS answerFROM dbo.SurveyUNIONALLSELECT'Q3'AS Qs, Q3AS answerFROM dbo.SurveyUNIONALLSELECT'Q4'AS Qs, Q4AS answerFROM dbo.Survey

The 2nd View is named: VIEW_SurveyRotation2
In this View, we pivot the answer values back into column headings by querying against the results of our first View. As mentioned, there's more than one way to create a Pivot in Sql.

SELECT Qs,CASEWHEN answer = 1THEN 1ELSE 0END AS One,CASEWHEN answer = 2THEN 1ELSE 0END AS Two,CASEWHEN answer = 3THEN 1ELSE 0END AS Three,CASEWHEN answer = 4THEN 1ELSE 0END AS Four,CASEWHEN answer = 5THEN 1ELSE 0END AS FiveFROM dbo.VIEW_SurveyRotation1

The 3rd View is named: VIEW_SurveyAverages
Here we can create a simple set of Averages by querying against our first View

SELECT Qs,AVG(CAST(answerAS decimal))AS [Avg]FROM dbo.VIEW_SurveyRotation1GROUP BY Qs

The 4th and final View is named: VIEW_SurveyResult
In this View, we summarize the answer counts for each column and also join in the Averages

SELECT dbo.VIEW_SurveyRotation2.Qs, dbo.VIEW_SurveyAverages.[Avg],SUM(dbo.VIEW_SurveyRotation2.One)AS Ones,SUM(dbo.VIEW_SurveyRotation2.Two)AS Twos,SUM(dbo.VIEW_SurveyRotation2.Three)AS Threes,SUM(dbo.VIEW_SurveyRotation2.Four)AS FoursFROM dbo.VIEW_SurveyRotation2INNERJOIN dbo.VIEW_SurveyAveragesON dbo.VIEW_SurveyRotation2.Qs = dbo.VIEW_SurveyAverages.QsGROUP BY dbo.VIEW_SurveyRotation2.Qs, dbo.VIEW_SurveyAverages.[Avg]

I tend to work with complex sql queries by breaking it down into steps like this. It helps me to achieve the desired result. Then, once you've got it working, you can review it and see if you can eliminate any of the steps by consolidating them into fewer queries.

|||

Here is the code sample for UNPIVOT and PIVOT solution with SQL Server 2005:

SELECT Questionas Qs, [1]as Ones, [2]as Twos, [3]as Threes, [4]as Fours, [5]as FivesFROM

(SELECT Question, [Value]FROM pivotQuestions

UNPIVOT([Value]FOR [Question]in([Q1], [Q2], [Q3], [Q4], [Q5], [Q6])

)as unpvt) t

PIVOT(COUNT([Value])FOR [Value]IN([1], [2], [3], [4], [5])

)as pvt

--Table and test data

CREATETABLE [dbo].[pivotQuestions](

[ID] [int]NotNULL,

[Q1] [int]NULL,

[Q2] [int]NULL,

[Q3] [int]NULL,

[Q4] [int]NULL,

[Q5] [int]NULL,

[Q6] [int]NULL

)

GO

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(1, 3, 4, 5, 5, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(2, 3, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(3, 3, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(4, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(5, 5, 5, 5, 5, 5, 5)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(6, 3, 4, 1, 1, 1, 1)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(7, 3, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(8, 3, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(9, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(10, 5, 5, 5, 5, 5, 5)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(11, 5, 1, 1, 1, 1, 1)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(12, 2, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(13, 3, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(14, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(15, 5, 5, 5, 5, 5, 5)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(16, 5, 1, 1, 1, 1, 1)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(17, 3, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(18, 5, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(19, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(20, 5, 5, 5, 5, 5, 5)

|||

Thanks for the tips, I will give them a try.

|||

Do not have SQL 2005 so I did what you suggested mbanavige. I was able to combine it all into a single query and not use Views, just couldnt add in the averages that. But that was easy enough to do on the databind. Working like a charm. thanks.

|||

limno:

Here is the code sample for UNPIVOT and PIVOT solution with SQL Server 2005:

SELECT Questionas Qs, [1]as Ones, [2]as Twos, [3]as Threes, [4]as Fours, [5]as FivesFROM

(SELECT Question, [Value]FROM pivotQuestions

UNPIVOT([Value]FOR [Question]in([Q1], [Q2], [Q3], [Q4], [Q5], [Q6])

)as unpvt) t

PIVOT(COUNT([Value])FOR [Value]IN([1], [2], [3], [4], [5])

)as pvt

Hi,

I'm having a really similar problem, also with surveys.

The only 2 differences are that (a) I don't want to summarise my results at all (b) I have multiple surveys in the same table so need to use an extra clause to pick out info for the survey I am interested in.

So far I have come up with...

TABLE

=====

SurveyID RespondantID QuestionID Answer

PIVOT QUERY

===========

SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]
As Q10 FROM (SELECT RespondantlD, QuestionlD, Answer FROM "3_Temp" WHERE SurveylD=3) AS preData PIVOT (
COUNT(Answer) FOR QuestionlD IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) ) AS data ORDER BV RespondantlD

But it doesn't work and I can't figure out why.

What am I doing wrong?

Having problem with sql create

The following is a create that I was trying to run in SQL/2000 SQL
Analyser:

CREATE TABLE tblxyz as (Select * from tblPlayers);

tblPlayers has 2 rows in it.

I continue to get the error

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

I have tried it with and without the () and it works on Oracle but not
SQL/2K. Don't know why.

This has all developed because of another problem.

First I have select with a complex join all records with a certain
employee ID.

Then I have to select the top 20 (by date) records from the first
select.

From the second select results I have to select the top 10 by a
numeric field.

The only way I thought of doing it was to create temporary tables (see
original question).SELECT * INTO tblxyz FROM tblPlayers

if you want an empty table with only the schema of tblPlayers use a WHERE
1=0

"Jim R" <JimReid@.comcast.net> wrote in message
news:7eb6df4b.0407150949.15a9468c@.posting.google.c om...
> The following is a create that I was trying to run in SQL/2000 SQL
> Analyser:
> CREATE TABLE tblxyz as (Select * from tblPlayers);
> tblPlayers has 2 rows in it.
> I continue to get the error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'AS'.
> I have tried it with and without the () and it works on Oracle but not
> SQL/2K. Don't know why.
> This has all developed because of another problem.
> First I have select with a complex join all records with a certain
> employee ID.
> Then I have to select the top 20 (by date) records from the first
> select.
> From the second select results I have to select the top 10 by a
> numeric field.
> The only way I thought of doing it was to create temporary tables (see
> original question).|||> CREATE TABLE tblxyz as (Select * from tblPlayers);

The SQL Server equivalent is

SELECT * INTO tblxyz FROM tblPlayers ;

> The only way I thought of doing it was to create temporary tables (see
> original question).

Without more of a spec (DDL for the tables, some INSERT statements of sample
data) it's impossible for us to know if that's really the best way to
achieve what you want.

--
David Portas
SQL Server MVP
--|||Thank you. That works great. Now I create tbltemp and from tbltemp I
create a tbltempa and then drop tbltemp. All in a stored procedure.
Then after I'm done with my selection of the recordset I'll drop the
tbltempa.

Thanks again.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||SQL is a declarative language. To get the most out of SQL, start by writing
a query that will produce the end result you require rather than thinking of
procedural solutions like [Query 1] -> [Temp Table] -> [Query 2] -> etc.
From your description I doubt that temp tables are the most concise,
efficient or maintainable solution to your problem.

--
David Portas
SQL Server MVP
--|||David,

You are right. My problem was that I didn't know how to create a select
within a select. What I ended up with was just that and it is all in a
single stored procedure with no temporary tables. Works really well.

My biggest problem was all new things I was trying to do. I have always
done simple select statements before both with Views and Stored
Procedures.

Then I discovered that I needed to feed parameters to the first select
which knocked out Views. Then I needed only the top 20 by date and from
the top 20 the lowest 10(numerically) of those top 20.

It got very confusing real fast when I had a stored procedure that
select via param for the top 20. It would pick the top 20 first and
then the parms. Which wasn't the sequence I wanted.

Since I was only running on SQL/2k I ended up with the following stored
procedure:
CREATE PROCEDURE [SelectUSGA1]]
@.PlayerID integer
AS
SELECT TOP 10 WITH TIES dbo.tblScores.[Date], dbo.tblScores.Player,
dbo.tblScores.HandiDiff, dbo.tblScores.Tees, dbo.tblScores.H1,
dbo.tblScores.H2, dbo.tblScores.H3, dbo.tblScores.Course,
dbo.tblCourse.CourseID, dbo.tblCourse.CourseName, dbo.tblCourse.Hole1,
dbo.tblCourse.Hole2, dbo.tblCourse.Hole3, dbo.tblCourse.Hole4,
dbo.tblCourse.Hole5, dbo.tblCourse.Hole6, dbo.tblCourse.Hole7,
dbo.tblCourse.Hole8, dbo.tblCourse.Hole9, dbo.tblCourse.Hole10,
dbo.tblCourse.Hole11, dbo.tblCourse.Hole12, dbo.tblCourse.Hole13,
dbo.tblCourse.Hole14, dbo.tblCourse.Hole15, dbo.tblCourse.Hole16,
dbo.tblCourse.Hole17, dbo.tblCourse.Hole18, dbo.tblCourse.T,
dbo.tblCourse.Rating, dbo.tblCourse.Slope, dbo.tblPlayers.Player_ID,
dbo.tblPlayers.PlayerLastName, dbo.tblPlayers.PlayerFirstName,
dbo.tblPlayers.PlayerNickName, dbo.tblScores.H4, dbo.tblScores.H5,
dbo.tblScores.H6, dbo.tblScores.H7, dbo.tblScores.H8, dbo.tblScores.H9,
dbo.tblScores.H10, dbo.tblScores.H11, dbo.tblScores.H12,
dbo.tblScores.H13, dbo.tblScores.H14, dbo.tblScores.H15,
dbo.tblScores.H16, dbo.tblScores.H17, dbo.tblScores.H18,
dbo.tblScores.B9, dbo.tblScores.T18, dbo.tblScores.P1, dbo.tblScores.F9,
dbo.tblScores.P2, dbo.tblScores.P3, dbo.tblScores.P4, dbo.tblScores.P5,
dbo.tblScores.P6,
dbo.tblScores.P7, dbo.tblScores.P8, dbo.tblScores.P9, dbo.tblScores.P10,
dbo.tblScores.P11, dbo.tblScores.P12, dbo.tblScores.P13,
dbo.tblScores.P15, dbo.tblScores.P14,
dbo.tblScores.P16, dbo.tblScores.P17, dbo.tblScores.P18,
dbo.tblScores.FW1, dbo.tblScores.FW2, dbo.tblScores.FW3,
dbo.tblScores.FW4, dbo.tblScores.FW5, dbo.tblScores.FW6,
dbo.tblScores.FW7, dbo.tblScores.FW8, dbo.tblScores.FW9,
dbo.tblScores.FW10, dbo.tblScores.FW11, dbo.tblScores.FW12,
dbo.tblScores.FW13, dbo.tblScores.FW14, dbo.tblScores.FW15,
dbo.tblScores.FW16, dbo.tblScores.FW17,
dbo.tblScores.FW18, dbo.tblScores.FWT, dbo.tblScores.TotalPutts,
dbo.tblScores.ActualDiff, dbo.tblScores.TotPars, dbo.tblScores.TotBirds,
dbo.tblScores.TotEagles, dbo.tblScores.TotBogys,
dbo.tblScores.Tot2Bogys, dbo.tblScores.AvgPutts
FROM dbo.tblScores
LEFT OUTER JOIN
dbo.tblCourse ON dbo.tblScores.Course = dbo.tblCourse.CourseID
LEFT OUTER JOIN
dbo.tblPlayers ON dbo.tblScores.Player =
dbo.tblPlayers.Player_ID
WHERE tblPlayers.Player_ID=@.PlayerID
order by date, HandiDiff;
GO

Works really well now.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Looks like you just need date in DESCending order (the default is
ascending).

SELECT TOP 10 WITH TIES
...
ORDER BY date DESC, handidiff

Note that you may or may not want the WITH TIES option. Without it, TOP may
give non-deterministic results - if there are values where the sort columns
are equal (tied) then only some subset of the rows may be shown if the
result would otherwise give more than 10 rows.

--
David Portas
SQL Server MVP
--

> I posted another message because with your solution it gave me 10 rows by
> date which isn't exactly what I was looking for.

> I have to selection for a row of golf scores, the most current 20 rows by
> date for a particular player.

> Then I have to select the lowest 10 scores from that subset.

> Then I use those 10 to calculate the USGA Handicap Index.

> What I'm getting is the newest 10 by date which may not be the best scores|||Still have a problem with SQL. I have tried the following:

Select top 10 with ties, date, score
From tblScores
Where playerID = 3
Order by date desc, score;

The problem is that it selects the top 10 scores by date when in fact I
want the most current top 20 scores and from the most current top 20 I
want to pick the top 10 scores by lowest score.

So if I had 100 rows I would get the most current 20. From that group I
would select by score the lowest 10.

I'm not sure how TIES works but it's not doing what I wanted.

Any help would be appreciated.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Try this:

SELECT TOP 10 WITH TIES *
FROM
(SELECT TOP 20 WITH TIES
S.[date], S.player, S.handidiff, S.tees, S.h1, S.h2, S.h3, S.course,
C.courseid, C.coursename, C.hole1, C.hole2, C.hole3, C.hole4, C.hole5,
C.hole6, C.hole7, C.hole8, C.hole9, C.hole10, C.hole11, C.hole12,
C.hole13, C.hole14, C.hole15, C.hole16, C.hole17, C.hole18, C.t,
C.rating, C.slope, P.player_id, P.playerlastname, P.playerfirstname,
P.playernickname, S.h4, S.h5, S.h6, S.h7, S.h8, S.h9, S.h10, S.h11,
S.h12, S.h13, S.h14, S.h15, S.h16, S.h17, S.h18, S.b9, S.t18,
S.p1, S.f9, S.p2, S.p3, S.p4, S.p5, S.p6, S.p7, S.p8, S.p9, S.p10,
S.p11, S.p12, S.p13, S.p15, S.p14, S.p16, S.p17, S.p18, S.fw1, S.fw2,
S.fw3, S.fw4, S.fw5, S.fw6, S.fw7, S.fw8, S.fw9, S.fw10, S.fw11,
S.fw12, S.fw13, S.fw14, S.fw15, S.fw16, S.fw17, S.fw18, S.fwt,
S.totalputts, S.actualdiff, S.totpars, S.totbirds, S.toteagles,
S.totbogys, S.tot2bogys, S.avgputts
FROM dbo.tblScores AS S
LEFT OUTER JOIN
dbo.tblCourse AS C
ON S.course = C.courseid
LEFT OUTER JOIN
dbo.tblPlayers AS P
ON S.player = P.player_id
WHERE P.player_id = @.PlayerID
ORDER BY S.[date] DESC, S.handidiff) AS T
ORDER BY handidiff

--
David Portas
SQL Server MVP
--sql

HAVING prob !

Bonjour,

CREATE TABLE [dbo].[MAND](
[Mat] [varchar](5)
[Dur] [varchar](1)
) ON [PRIMARY]

Mat Dur
16030d
16030i
31217i
10000d
12000i
10000d
31217d
35000d
36000i
35000d

Je voudrais avoir le resulat suivant (i need this result) :

10000 d
35000 d

Car ils ont tous les deux "d". J'ai beau faire un regroupement (group
by) par Mat avec un having ne marche pas.

Comment faire ?
Merci d'avanceIs this what you want?

SELECT Mat,
Dur
FROM MAND
GROUP BY Mat,Dur
HAVING COUNT(*)=2|||Try:

select
Mat
, Dur
from
MAND
group by
Mat
, Dur
having
count (*) > 1

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<daoud97@.yahoo.com> wrote in message
news:1149245350.517939.8790@.c74g2000cwc.googlegrou ps.com...
Bonjour,

CREATE TABLE [dbo].[MAND](
[Mat] [varchar](5)
[Dur] [varchar](1)
) ON [PRIMARY]

Mat Dur
16030 d
16030 i
31217 i
10000 d
12000 i
10000 d
31217 d
35000 d
36000 i
35000 d

Je voudrais avoir le resulat suivant (i need this result) :

10000 d
35000 d

Car ils ont tous les deux "d". J'ai beau faire un regroupement (group
by) par Mat avec un having ne marche pas.

Comment faire ?
Merci d'avance|||markc...@.hotmail.com schreef:

> Is this what you want?
>
> SELECT Mat,
> Dur
> FROM MAND
> GROUP BY Mat,Dur
> HAVING COUNT(*)=2

No.

When i group by 'Mat' i need to filter by 'Mat' having only 'd' in
'Dur'. I d'ont want Mat with 'i' in Dur.|||Maybe this

SELECT Mat,
Dur
FROM MAND
WHERE Dur='d'
GROUP BY Mat,Dur
HAVING COUNT(*)=2|||other example to help you :

Mat Dur
16030 d
16030 i
31217 i
10000 d
12000 i
10000 d
31217 d
35000 d
36000 i
35000 d
35000 i

i need this result :

10000 d

Now, 35000 have one 'i' in 'Dur', so i don't need it.

daoud97@.yahoo.com schreef:

> markc...@.hotmail.com schreef:
> > Is this what you want?
> > SELECT Mat,
> > Dur
> > FROM MAND
> > GROUP BY Mat,Dur
> > HAVING COUNT(*)=2
> No.
> When i group by 'Mat' i need to filter by 'Mat' having only 'd' in
> 'Dur'. I d'ont want Mat with 'i' in Dur.|||SELECT Mat,
'd' AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1|||markc600@.hotmail.com schreef:

> SELECT Mat,
> 'd' AS Dur
> FROM MAND
> GROUP BY Mat
> HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1

Sorry, it's not correct :

if i insert

36000 i
36000 i

and apply your SQL, i have some result like :

10000d
16030d
31217d
36000d (??)

perhaps, i d'ont expline vers well !|||daoud97@.yahoo.com schreef:

> markc600@.hotmail.com schreef:
> > SELECT Mat,
> > 'd' AS Dur
> > FROM MAND
> > GROUP BY Mat
> > HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1
> Sorry, it's not correct :
> if i insert
> 36000 i
> 36000 i
> and apply your SQL, i have some result like :
> 10000d
> 16030d
> 31217d
> 36000d (??)
> perhaps, i d'ont expline vers well !

I will try to expline more :

this is the table with groups:

Mat Dur
16030 d
16030 i

31217 i
31217 d
31217 d
31217 d

10000 d
10000 d
10000 d

35000 d

I'am interested only by the 2 groups : 1000 and 35000 because the
haven't any 'i' in the colonn Dur.|||daoud97@.yahoo.com schreef:

> daoud97@.yahoo.com schreef:
> > markc600@.hotmail.com schreef:
> > > SELECT Mat,
> > > 'd' AS Dur
> > > FROM MAND
> > > GROUP BY Mat
> > > HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1
> > Sorry, it's not correct :
> > if i insert
> > 36000 i
> > 36000 i
> > and apply your SQL, i have some result like :
> > 10000d
> > 16030d
> > 31217d
> > 36000d (??)
> > perhaps, i d'ont expline vers well !
> I will try to expline more :
>
> this is the table with groups:
> Mat Dur
> 16030 d
> 16030 i
> 31217 i
> 31217 d
> 31217 d
> 31217 d
> 10000 d
> 10000 d
> 10000 d
> 35000 d
>
> I'am interested only by the 2 groups : 10000 and 35000 because the
> haven't any 'i' in the colonn Dur.|||
SELECT Mat,
'd' AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1
AND MAX(Dur)='d'|||markc600@.hotmail.com schreef:

> SELECT Mat,
> 'd' AS Dur
> FROM MAND
> GROUP BY Mat
> HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1
> AND MAX(Dur)='d'

sorry, it's not correct

Example:
16030d
16030d
31217d
10000d
12000i
10000d
31217d
35000d
36000i
35000d
35000d
36000i
45d
45d
45i
250d
250i
10i
10i

result with your query :
10000d
16030d
31217d

reult that i need:
10000 d
16030 d
31217 d
35000 d

why COUNT(*)=2 ?|||The problem is I'm trying to guess your business rules

SELECT Mat,
MAX(Dur) AS Dur
FROM MAND
GROUP BY Mat
HAVING COUNT(*)>1 AND COUNT(DISTINCT Dur)=1
AND MAX(Dur)='d'|||Try:

SELECT Mat,
Dur
FROM MAND
GROUP BY Mat, Dur
HAVING SUM (CASE WHEN Dur = 'i' THEN 1 ELSE 0 END) = 0

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<daoud97@.yahoo.com> wrote in message
news:1149251877.853856.250370@.y43g2000cwc.googlegr oups.com...

daoud97@.yahoo.com schreef:

> markc600@.hotmail.com schreef:
> > SELECT Mat,
> > 'd' AS Dur
> > FROM MAND
> > GROUP BY Mat
> > HAVING COUNT(*)=2 AND COUNT(DISTINCT Dur)=1
> Sorry, it's not correct :
> if i insert
> 36000 i
> 36000 i
> and apply your SQL, i have some result like :
> 10000 d
> 16030 d
> 31217 d
> 36000 d (??)
> perhaps, i d'ont expline vers well !

I will try to expline more :

this is the table with groups:

Mat Dur
16030 d
16030 i

31217 i
31217 d
31217 d
31217 d

10000 d
10000 d
10000 d

35000 d

I'am interested only by the 2 groups : 1000 and 35000 because the
haven't any 'i' in the colonn Dur.|||markc600@.hotmail.com schreef:

> The problem is I'm trying to guess your business rules
>
> SELECT Mat,
> MAX(Dur) AS Dur
> FROM MAND
> GROUP BY Mat
> HAVING COUNT(*)>1 AND COUNT(DISTINCT Dur)=1
> AND MAX(Dur)='d'

mmm very interresting. Thanks

Having major problems with my insert query logic

I have a perl program that is looping through a hash of a hash. I need to Update any existing records but also insert any new records in the table using collected data in the hash.

Life would be very simple if it was possible to use a Where Clause in an Insert statement but not does not work.

Here is some example code from my program:
sub Test{
foreach my $table(keys %$HoH){
foreach my $field(keys %{$HoH->{$table}}){
if($table eq "CPU"){
my $CPUstatement = "INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
Values ('$field',
'$HoH->{CPU}{$field}{Name}',
'$HoH->{CPU}{$field}{MaxClockSpeed}' ,
'$HoH->{Host}{SystemNetName}')";
print "$CPUstatement\n";
if ($db->Sql($CPUstatement))
{
print "Error on SQL Statement\n";
Win32::ODBC::DumpError();
}
else
{
print "successful\n";
}
}
}


}
}

Thanks,
LauraI'm assuming that your hash values are printing as expected. The construction looks strange (but it could be fine) to me.

Is the CPUNumber the primary key for the CPU table? If so, you could use something like:my $CPUstatement = "IF EXISTS (SELECT * FROM CPU WHERE CPUNUMBER = '$field')
THEN UPDATE CPU
SET Name = '$HoH->{CPU}{$field}{Name}'
, MaxClockSpeed = '$HoH->{CPU}{$field}{MaxClockSpeed}'
, SystemNetName = '$HoH->{Host}{SystemNetName}'
WHERE CPUNumber = '$field'
ELSE INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
Values (
'$field'
, '$HoH->{CPU}{$field}{Name}'
, '$HoH->{CPU}{$field}{MaxClockSpeed}'
, '$HoH->{Host}{SystemNetName}')";-PatP|||I tried your code and I am getting an error -> Incorrect Systax near the keyword 'THEN'

What do you suppose that means? I copied and pasted the code as is.

Thanks,
Laura|||That error means that I don't proofread very well ;) I was composing as I typed, and simply got ahead of myself then didn't clean up afterwards. Just remove the word THEN from that statement. Sorry.

-PatP|||oh wow. That's so cool, it worked.

Thanks Pat for your help. I learn new things everyday.

-Laura|||I just love it when I can make a lovely lady happy!

-PatP

Having issue inserting large text colum into DB

I have a large text colum I am trying to insert into a DB
This colum is about 800 chars longs

I have set the colum type in the table to text

I have set the table option for text in row to on

I have set the table option for text in row to 1000

But it is still chopping the text at the 256 char mark on insert.

Anyone have any ideas ?? This is SQL 2000.

ChrisAre you sure it does? How do you check for the length of the inserted value? By doing SELECT?

Try this:

select datalength(<your_text_field>) from <your_table>

And why do you need TEXT IN ROW setting? Are you searching on that field? If that's the case, - you should implement Full-Text Search.|||In Query Analyzer:

1) press Shift-Ctrl-O to bring up the Options dialog.
2) Click on the Results tab
3) Check the value of the Maximum Characters per Column

If it is too small, make it larger, but keep in mind that this is a VERY RAM expensive operation in the GUI. Don't make it any larger than 255 unless you really need it!

-PatP|||In Query Analyzer:

1) press Shift-Ctrl-O to bring up the Options dialog.
2) Click on the Results tab
3) Check the value of the Maximum Characters per Column

If it is too small, make it larger, but keep in mind that this is a VERY RAM expensive operation in the GUI. Don't make it any larger than 255 unless you really need it!

-PatP
That's why I suggested to use DATALENGTH, because it does not rely on this setting. Besides, what if the value that is inserted greater than 8192? Or you'd think that you inserted 8192 characters?|||That's why I suggested to use DATALENGTH, because it does not rely on this setting. Besides, what if the value that is inserted greater than 8192? Or you'd think that you inserted 8192 characters?You did fine, as far as giving them what they needed to figure out the answer. I just like my solution better because then they can SEE the answer, which is often better than being able to deduce it.

-PatP

having difficulty inserting into the database table

I created a web form where the user fills in some data and when he submits the form, I do an insert into he database table. The problem is, how can I get the data from the form into the insert statement?. here is the code:
Dim Message As String
Dim connStr As String
Dim myConnection As SqlConnection
Dim mySqlCommand As SqlCommand
connStr = "server=SIMI\VSdotNET;Trusted_Connection=yes;database=AeroSea"
myConnection = New SqlConnection(connStr)
mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,name.text)", myConnection)

If I execute the above code, then nothing gets updated. When I change the insert staement into the following,
mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,'myname')", myConnection) then,
value 1 for customerID field and myname in the namefield is added.
I know I am doing something stupid, but can't figure it out.
I am confused. please help me.One possibility is this (presuming textbox is name):

mySqlCommand = New SqlCommand("INSERT INTO TravelRequestEntry (CustomerID,Name) Values (1,'" + name.text + "')", myConnection)

Better,use parameters, as the code above is subject to SQL Injection attacks, and as written will fail if the name.Text is "O'Reilly".|||Thanks, I used the parameters and it works fine.

HAVING clause is a case statement??

i have wrote a query which compares two tables, returning anywhere the qty is not the same in each table:

(simple ex)

Select order_no
from table1
inner join table2
on table1.order_no = table2.order_no
group by order_no
having table1.Qty<> table2.Qty

BUT... I need to add a table3, where there maybe (or may not be enters - thus a left join). If there is an entry in table3 then use qty in table3 and not table1... so having becomes:

CASE WHEN table1.Qty<> table3.Qty
THEN table3.Qty<> table2.Qty
ELSE table1.Qty<> table2.Qty END

but how do i actually write this?perhaps if you would care to explain what you're doing?

are you comparing individual Qty values, or the SUMs?

because the HAVING clause may refer only to columns in the GROUP BY or to aggregate functions|||perhaps if you would care to explain what you're doing?

are you comparing individual Qty values, or the SUMs?

because the HAVING clause may refer only to columns in the GROUP BY or to aggregate functions

Sorry I am trying to compare Sum(qty) for each product in an order (product maybe in the order more than 1ce) I am trying to retrieve any product lines where Sum qties in table1 and table2 are not the same.

However, if stock was not found, then an allocated qty is recorded in table 3...so in this case I want to compare qtyies in table3 and table2
?|||select t1.order_no
, t1.sumqty
, t2.order_no
, t2.sumqty
from (
select order_no
, sum(qty) as sumqty
from table1
group
by order_no
) as t1
full outer
join (
select order_no
, sum(qty) as sumqty
from table2
group
by order_no
) as t2
on t2.order_no = t1.order_no
and t2.sumqty <> t1.sumqtythat's the general strategy -- do your sums in derived tables

for table 3, you're on your own :)

Having a stored procedure copy tables & also preserve indexing/sch

Hello,
I created a stored procedure that renames a table to OLD_xxxxx and replaces
that table with another (copy) that resides on a different database. I pull
the
tablename names through the use of a cursor table and construct a SELECT INTO
statement as follows
'SELECT * INTO DB1.dbo.' + @.tableName + ' FROM DB2.dbo.' + @.tableName
It works great especially since there are 80+ tables that need to be copied
from one database to another. The drawback is that it doesn't preserve the
indexing/foriegn key constraints. Is there a way to do this without having to
deal with DTS or creating additional scripts? Ideally I would like to
replace
the "SELECT * INTO" statement with something that not only does a copy but
also preserves the indexing! Does such a command exist? Any help from the
Microsoft guru's would be greatly appreciated!!!!
SELECT ... INTO <tablename> doesn't create any of the PRIMARY KEY, UNIQUE,
FOREIGN KEY, CHECK, NOT NULL constraints and doesn't define DEFAULT and
IDENTITY column properties for the new table.
You will have to write seperate statements into your stored procedure to
create them.
--Vishal.
"Peter S." wrote:

> Hello,
> I created a stored procedure that renames a table to OLD_xxxxx and replaces
> that table with another (copy) that resides on a different database. I pull
> the
> tablename names through the use of a cursor table and construct a SELECT INTO
> statement as follows
> 'SELECT * INTO DB1.dbo.' + @.tableName + ' FROM DB2.dbo.' + @.tableName
> It works great especially since there are 80+ tables that need to be copied
> from one database to another. The drawback is that it doesn't preserve the
> indexing/foriegn key constraints. Is there a way to do this without having to
> deal with DTS or creating additional scripts? Ideally I would like to
> replace
> the "SELECT * INTO" statement with something that not only does a copy but
> also preserves the indexing! Does such a command exist? Any help from the
> Microsoft guru's would be greatly appreciated!!!!
>

Have Subreport NOT Keep Together

I have a subreport inside a table on the main report. It seems
everything I
try the subreport will keep together on a new page if it cannot fit
itself
on the rest of the first page. How can I have the subreport display
it's
data on the rest of the first page and continue on to the second page?
I have looked at everything I can think of. Keep Together is set to
false
everywhere.
Please help.
Thanks,Same thing here !
We have reports with 2-3 levels of subreports and the data "migrates"
sometimes to the 4th page, leaving the first 2-3 pages just with a small
header on top... it's very difficult to explain to a client why their report
looks like that. And this problem is active since the beginning of reporting
services. A few service packs later, a new version, and it's still out
there...
For some of the reports we managed to eliminate the subreports, but just
imagine how the datasets look like in the main report :(
Or, we transferred the burden of "subreporting" to the processing part,
creating a separate table to keep all the pre-calculated data, with headers
and subreport information, etc., leaving to Reporting Services the only task
of displaying the content of the table. But it's much more difficult and it
lacks flexibility !
Please, if anyone has a solution to this, make it public !
We regularly search the newsgroups for a solution to this problem, but
untill now, we just noticed a lot of people having this problem...
Thank you,
Andrei.
<sjackson@.nexterna.com> wrote in message
news:1168641290.758091.292180@.m58g2000cwm.googlegroups.com...
>I have a subreport inside a table on the main report. It seems
> everything I
> try the subreport will keep together on a new page if it cannot fit
> itself
> on the rest of the first page. How can I have the subreport display
> it's
> data on the rest of the first page and continue on to the second page?
> I have looked at everything I can think of. Keep Together is set to
> false
> everywhere.
> Please help.
> Thanks,
>|||The sad thing is that I heard it is an issue that Microsoft has added
to their wish list. So you really need to limit the use of sub reports
or you can run into this issue.
Hopefully they can expedite the resolution of this issue.
Andrei wrote:
> Same thing here !
> We have reports with 2-3 levels of subreports and the data "migrates"
> sometimes to the 4th page, leaving the first 2-3 pages just with a small
> header on top... it's very difficult to explain to a client why their report
> looks like that. And this problem is active since the beginning of reporting
> services. A few service packs later, a new version, and it's still out
> there...
> For some of the reports we managed to eliminate the subreports, but just
> imagine how the datasets look like in the main report :(
> Or, we transferred the burden of "subreporting" to the processing part,
> creating a separate table to keep all the pre-calculated data, with headers
> and subreport information, etc., leaving to Reporting Services the only task
> of displaying the content of the table. But it's much more difficult and it
> lacks flexibility !
> Please, if anyone has a solution to this, make it public !
> We regularly search the newsgroups for a solution to this problem, but
> untill now, we just noticed a lot of people having this problem...
> Thank you,
> Andrei.
>
>
> <sjackson@.nexterna.com> wrote in message
> news:1168641290.758091.292180@.m58g2000cwm.googlegroups.com...
> >I have a subreport inside a table on the main report. It seems
> > everything I
> > try the subreport will keep together on a new page if it cannot fit
> > itself
> > on the rest of the first page. How can I have the subreport display
> > it's
> > data on the rest of the first page and continue on to the second page?
> >
> > I have looked at everything I can think of. Keep Together is set to
> > false
> > everywhere.
> >
> > Please help.
> >
> > Thanks,
> >|||Will someone from Microsoft please address this question? This is a very
basic reporting need. Is a service pack in the works?|||"sjackson@.nexterna.com" wrote:
> I have a subreport inside a table on the main report. It seems
> everything I
> try the subreport will keep together on a new page if it cannot fit
> itself
> on the rest of the first page. How can I have the subreport display
> it's
> data on the rest of the first page and continue on to the second page?
> I have looked at everything I can think of. Keep Together is set to
> false
> everywhere.
> Please help.
> Thanks,
>|||I have the same problem.
I find many posts related to this problem and recentily I published my own
post with several links to other posts, but yet any answer from MS.
Here is my post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1279676&SiteID=1
"sjackson@.nexterna.com" wrote:
> I have a subreport inside a table on the main report. It seems
> everything I
> try the subreport will keep together on a new page if it cannot fit
> itself
> on the rest of the first page. How can I have the subreport display
> it's
> data on the rest of the first page and continue on to the second page?
> I have looked at everything I can think of. Keep Together is set to
> false
> everywhere.
> Please help.
> Thanks,
>|||Did anybody find a solution to this problem ?
Maybe somebody from Microsoft can be of assistance ?
<sjackson@.nexterna.com> wrote in message
news:1168641290.758091.292180@.m58g2000cwm.googlegroups.com...
>I have a subreport inside a table on the main report. It seems
> everything I
> try the subreport will keep together on a new page if it cannot fit
> itself
> on the rest of the first page. How can I have the subreport display
> it's
> data on the rest of the first page and continue on to the second page?
> I have looked at everything I can think of. Keep Together is set to
> false
> everywhere.
> Please help.
> Thanks,
>|||Also wrestling with this one. Also irritated by the "No fields in headers or
footers thing" but got that working.
I am now at the point where I am possibly going to play with the byte stream
output by Localreport.Render("PDF"...) to try and make it work the way I
want. Fortunately only PDF is reuired.
Will come back to this post if I find something...
--
Programming is fun but paralgiding is better!|||Same thing here as well sjackson. No one has any answers? Let's add another
client to the 'I'm Not impressed with that SQL Reporting service thing mister
contractor' list.
"sjackson@.nexterna.com" wrote:
> I have a subreport inside a table on the main report. It seems
> everything I
> try the subreport will keep together on a new page if it cannot fit
> itself
> on the rest of the first page. How can I have the subreport display
> it's
> data on the rest of the first page and continue on to the second page?
> I have looked at everything I can think of. Keep Together is set to
> false
> everywhere.
> Please help.
> Thanks,
>

Have Stored Procedure with input parameters and want to Use Spreadsheet

I have a stored procedure that is able to ultimately do an create a temp table and insert into another table. I'm trying to figure out how I could execute the stored procedure that would get it's input parameters from a spreadsheet rather than execute it line by line. Can anyone suggest anything?

CREATE PROCEDURE osp_xmlSvc_UpdateItemAttribute

@.PartNumber varchar(50) = '',

@.Rev varchar(50) = '',

@.AttributeName varchar(256) = '',

@.AttributeValue varchar(1024) = '',

@.EditSource varchar(255) = '',

@.UserName varchar(50) = 'admin',

@.ReturnState int = 0 Output

WITH ENCRYPTION

AS

DECLARE @.ItemID int

DECLARE @.RevID int

DECLARE @.AssignAllRevs int

DECLARE @.ParamIndexID int

DECLARE @.CurrentValue varchar(1024)

DECLARE @.UserID int

SET @.ReturnState = -1

SET @.ItemID = -1

SET @.RevID = -1

SET @.ParamIndexID = -1

SET @.CurrentValue = ''

SET @.AssignAllRevs = 0

SELECT @.UserID = ID FROM UserProfile WHERE UserName = @.UserName

SELECT @.ItemID = ID FROM Entry WHERE PartNumber = @.PartNumber

if(NOT(@.ItemID = -1) AND @.ItemID IS NOT NULL) begin

SELECT @.RevID = ID FROM Rev WHERE ItemID = @.ItemID AND Rev=@.Rev

if(@.RevID = -1 OR @.RevID IS NULL) begin

SET @.AssignAllRevs = 1

SELECT @.RevID = ID FROM Rev WHERE ItemID = @.ItemID AND Expired=0

end

SELECT @.ParamIndexID = ID FROM ParamIndex WHERE [Name]=@.AttributeName

if(NOT(@.ParamIndexID = -1) AND @.ParamIndexID IS NOT NULL) begin

SET @.ReturnState = 0

CREATE TABLE tmp_xml_AV (AttVal varchar(1024))

DECLARE @.tmpSQL nvarchar(2024)

SET @.tmpSQL = 'INSERT INTO tmp_xml_AV (AttVal) SELECT [' + @.AttributeName + '] FROM ParamValue WHERE Expired=0 AND ItemID=' + CAST(@.ItemID AS VARCHAR)+ ' AND RevID=' + CAST(@.RevID AS VARCHAR)

EXECUTE sp_executesql @.tmpSQL

SELECT @.CurrentValue = IsNull(AttVal, '') FROM tmp_xml_AV

DROP TABLE tmp_xml_AV

if(NOT(@.CurrentValue = @.AttributeValue) OR @.CurrentValue IS NULL) begin

SET @.ReturnState = 1

SET @.tmpSQL = 'UPDATE ParamValue SET [' + @.AttributeName + ']=''' + @.AttributeValue + ''' WHERE ItemID=' + CAST(@.ItemID AS VARCHAR)

if(@.AssignAllRevs = 0) begin

SET @.tmpSQL = @.tmpSQL + ' AND RevID=' + CAST(@.RevID AS VARCHAR)

end

EXECUTE sp_executesql @.tmpSQL

-- Record history

DECLARE @.tmpInt int

SELECT @.tmpInt = Max(ID)+1 FROM EntryChangeAction

INSERT INTO EntryChangeAction (ID,EntryAffected,RevID,ActionType,Details,Tool,UserID)

VALUES (@.tmpInt,@.ItemID,@.RevID,6,@.AttributeName + ': ' + @.CurrentValue + ' to: ' + @.AttributeValue,@.EditSource,@.UserID)

end

end

end

GRANT EXECUTE ON [dbo].[osp_xmlSvc_UpdateItemAttribute] TO [public]

GO

Thanks.

Amy

In the stored procedure, you could open and read the xls file and put the values into variables. (An xml file would be simpler -Excel could save the file as xml.)

Check in Books Online about [OpenXML].

have SQL Server Mgmt Studio save alter table script when change table?

Is there a way to have SQL Server Management Studio save the script for
everything necessary to alter a table when changing the design of a table?
The older Visual Studio interfaces would allow this - maybe I can discover w
here
to turn that on in Visual Studio 2005?
--
Thanks in advance, Les CaudleHello Les,
If I understand the issue correctly, you'd like to save the script for
alterring a table when you change the design of the table in Managment
Studio.
You could use the following method to see if it meets your requirement:
1. Right click a table->Design to open the table in design view.
2. Do the change you want for the table.
3. Right click the blank area of the design view->Genegrate Change Script.
Also, If you have any feedback or wishes on SQL Server, I encourage you
submit via the link below and our product team would like to hear your
voice:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Please let's know if you have any further qusetions. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Les,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

have SQL Server Mgmt Studio save alter table script when change table?

Is there a way to have SQL Server Management Studio save the script for
everything necessary to alter a table when changing the design of a table?
The older Visual Studio interfaces would allow this - maybe I can discover where
to turn that on in Visual Studio 2005?
Thanks in advance, Les Caudle
Hello Les,
If I understand the issue correctly, you'd like to save the script for
alterring a table when you change the design of the table in Managment
Studio.
You could use the following method to see if it meets your requirement:
1. Right click a table->Design to open the table in design view.
2. Do the change you want for the table.
3. Right click the blank area of the design view->Genegrate Change Script.
Also, If you have any feedback or wishes on SQL Server, I encourage you
submit via the link below and our product team would like to hear your
voice:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Please let's know if you have any further qusetions. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Les,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
sql

have SQL Server Mgmt Studio save alter table script when change table?

Is there a way to have SQL Server Management Studio save the script for
everything necessary to alter a table when changing the design of a table?
The older Visual Studio interfaces would allow this - maybe I can discover where
to turn that on in Visual Studio 2005?
--
Thanks in advance, Les CaudleHello Les,
If I understand the issue correctly, you'd like to save the script for
alterring a table when you change the design of the table in Managment
Studio.
You could use the following method to see if it meets your requirement:
1. Right click a table->Design to open the table in design view.
2. Do the change you want for the table.
3. Right click the blank area of the design view->Genegrate Change Script.
Also, If you have any feedback or wishes on SQL Server, I encourage you
submit via the link below and our product team would like to hear your
voice:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Please let's know if you have any further qusetions. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Les,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Have Insert statement, need equivalent Update.

Using ms sql 2000
I have 2 tables.
I have a table which has information regarding a computer scan. Each
record in this table has a column called MAC which is the unique ID for
each Scan. The table in question holds the various scan results of
every scan from different computers. I have an insert statement that
works however I am having troulbe getting and update statement out of
it, not sure if I'm using the correct method to insert and thats why or
if I'm just missing something. Anyway the scan results is stored as an
XML document(@.iTree) so I have a temp table that holds the relevent
info from that. Here is my Insert statement for the temporary table.
INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)
Now here is the insert statement for the table I am having trouble
with.
INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, #temp.ID, #temp.ParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
#temp.scanattribute, DateCreated = getdate(), LastModified =
getdate()
FROM tblScan, tblScanAttribute JOIN #temp ON tblScanAttribute.Name =
#temp.Name
If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.Because your procedure don't use sp_executeSql you can use a table variable,
not a temp table.
Declare @.Tab table
(
Field1 nvarchar(10),
Field2 int,
...(exactly the fields in the xml file)
)
INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, @.Tab.ID, @.TabParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
@.Tab.scanattribute, getdate(), getdate()
FROM tblScan
INNER JOIN tblScanAttribute
JOIN@.Tab ON tblScanAttribute.Name =
@.Tab.Name
Of course fields must match...
Hope it helps
Benga.
"rhaazy" <rhaazy@.gmail.com> wrote in message
news:1151351218.116752.197980@.m73g2000cwd.googlegroups.com...
> Using ms sql 2000
> I have 2 tables.
> I have a table which has information regarding a computer scan. Each
> record in this table has a column called MAC which is the unique ID for
> each Scan. The table in question holds the various scan results of
> every scan from different computers. I have an insert statement that
> works however I am having troulbe getting and update statement out of
> it, not sure if I'm using the correct method to insert and thats why or
> if I'm just missing something. Anyway the scan results is stored as an
> XML document(@.iTree) so I have a temp table that holds the relevent
> info from that. Here is my Insert statement for the temporary table.
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
> 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
>
> Now here is the insert statement for the table I am having trouble
> with.
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, #temp.ID, #temp.ParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> #temp.scanattribute, DateCreated = getdate(), LastModified =
> getdate()
> FROM tblScan, tblScanAttribute JOIN #temp ON tblScanAttribute.Name =
> #temp.Name
> If there is a way to do this without the temporary table that would be
> great, but I haven't figured a way around it yet, if anyone has any
> ideas that would be great, thanks.
>|||While this is good to know my real problem is that I need the statement
that will do what my insert does accept I need it to be an update
statement. I need the update because an insert is only going to happen
once for each client.
Benga wrote:
> Because your procedure don't use sp_executeSql you can use a table variabl
e,
> not a temp table.
> Declare @.Tab table
> (
> Field1 nvarchar(10),
> Field2 int,
> ...(exactly the fields in the xml file)
> )
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, @.Tab.ID, @.TabParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> @.Tab.scanattribute, getdate(), getdate()
> FROM tblScan
> INNER JOIN tblScanAttribute
> JOIN@.Tab ON tblScanAttribute.Name =
> @.Tab.Name
> Of course fields must match...
> Hope it helps
> Benga.
> "rhaazy" <rhaazy@.gmail.com> wrote in message
> news:1151351218.116752.197980@.m73g2000cwd.googlegroups.com...|||Fixed it, no problems.
rhaazy wrote:
> While this is good to know my real problem is that I need the statement
> that will do what my insert does accept I need it to be an update
> statement. I need the update because an insert is only going to happen
> once for each client.
> Benga wrote:

Have Insert statement, need equivalent Update.

Using ms sql 2000
I have 2 tables.
I have a table which has information regarding a computer scan. Each
record in this table has a column called MAC which is the unique ID for

each Scan. The table in question holds the various scan results of
every scan from different computers. I have an insert statement that
works however I am having troulbe getting and update statement out of
it, not sure if I'm using the correct method to insert and thats why or

if I'm just missing something. Anyway the scan results is stored as an

XML document(@.iTree) so I have a temp table that holds the relevent
info from that. Here is my Insert statement for the temporary table.

INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)

Now here is the insert statement for the table I am having trouble
with.

INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, #temp.ID, #temp.ParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
#temp.scanattribute, DateCreated = getdate(),
LastModified =
getdate()
FROM tblScan, tblScanAttribute JOIN #temp ON
tblScanAttribute.Name =
#temp.Name

If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.rhaazy (rhaazy@.gmail.com) writes:
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
> 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
> Now here is the insert statement for the table I am having trouble
> with.
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, #temp.ID, #temp.ParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> #temp.scanattribute, DateCreated = getdate(),
> LastModified =
> getdate()
> FROM tblScan, tblScanAttribute JOIN #temp ON
> tblScanAttribute.Name =
> #temp.Name
> If there is a way to do this without the temporary table that would be
> great, but I haven't figured a way around it yet, if anyone has any
> ideas that would be great, thanks.

I have some difficulties to understand what your problem is. If all
you want to do is to insert from the XML document, then you don't
need the temp table, but you could use OPENXML directly in the
query.

But then you talk about an UPDATE as well, and if your aim is to insert
new rows, and update existing, it's probably better to use a temp
table (or a table variable), so that you don't have to run OPENXML twice.
Some DB engines support a MERGE command which performs the task of
UPDATE and INSERT in one statement, but this is not available in
SQL Server, not even in SQL 2005.

If this did not answer your question, could you please clarify?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||My app runs on all my companies PCs every month a scan is performed and
the resulst are stored in a database. So the first time a scan is
performed for any PC it will be an insert, but after that it will
always be an update. I tried using openxml in my insert statement but
kept getting an error stating my sub query is returning more than one
result... So since I couldn't do it that way I'm trying this method.
All the relevent openxml is there I just couldn't figure out how to
insert each column using it. If you have any suggestions I'm open to
give it a try.

Erland Sommarskog wrote:
> rhaazy (rhaazy@.gmail.com) writes:
> > INSERT INTO #temp
> > SELECT * FROM openxml(@.iTree,
> > 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> > WITH(
> > ID nvarchar(50) './@.ID',
> > ParentID nvarchar(50) './@.ParentID',
> > Name nvarchar(50) './@.Name',
> > scanattribute nvarchar(50) '.'
> > )
> > Now here is the insert statement for the table I am having trouble
> > with.
> > INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> > ScanID, AttributeValue, DateCreated, LastModified)
> > SELECT @.MAC, #temp.ID, #temp.ParentID,
> > tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> > #temp.scanattribute, DateCreated = getdate(),
> > LastModified =
> > getdate()
> > FROM tblScan, tblScanAttribute JOIN #temp ON
> > tblScanAttribute.Name =
> > #temp.Name
> > If there is a way to do this without the temporary table that would be
> > great, but I haven't figured a way around it yet, if anyone has any
> > ideas that would be great, thanks.
> I have some difficulties to understand what your problem is. If all
> you want to do is to insert from the XML document, then you don't
> need the temp table, but you could use OPENXML directly in the
> query.
> But then you talk about an UPDATE as well, and if your aim is to insert
> new rows, and update existing, it's probably better to use a temp
> table (or a table variable), so that you don't have to run OPENXML twice.
> Some DB engines support a MERGE command which performs the task of
> UPDATE and INSERT in one statement, but this is not available in
> SQL Server, not even in SQL 2005.
> If this did not answer your question, could you please clarify?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Fixed it no problems.
rhaazy wrote:
> My app runs on all my companies PCs every month a scan is performed and
> the resulst are stored in a database. So the first time a scan is
> performed for any PC it will be an insert, but after that it will
> always be an update. I tried using openxml in my insert statement but
> kept getting an error stating my sub query is returning more than one
> result... So since I couldn't do it that way I'm trying this method.
> All the relevent openxml is there I just couldn't figure out how to
> insert each column using it. If you have any suggestions I'm open to
> give it a try.
> Erland Sommarskog wrote:
> > rhaazy (rhaazy@.gmail.com) writes:
> > > INSERT INTO #temp
> > > SELECT * FROM openxml(@.iTree,
> > > 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> > > WITH(
> > > ID nvarchar(50) './@.ID',
> > > ParentID nvarchar(50) './@.ParentID',
> > > Name nvarchar(50) './@.Name',
> > > scanattribute nvarchar(50) '.'
> > > )
> > > > Now here is the insert statement for the table I am having trouble
> > > with.
> > > > INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> > > ScanID, AttributeValue, DateCreated, LastModified)
> > > SELECT @.MAC, #temp.ID, #temp.ParentID,
> > > tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> > > #temp.scanattribute, DateCreated = getdate(),
> > > LastModified =
> > > getdate()
> > > FROM tblScan, tblScanAttribute JOIN #temp ON
> > > tblScanAttribute.Name =
> > > #temp.Name
> > > > If there is a way to do this without the temporary table that would be
> > > great, but I haven't figured a way around it yet, if anyone has any
> > > ideas that would be great, thanks.
> > I have some difficulties to understand what your problem is. If all
> > you want to do is to insert from the XML document, then you don't
> > need the temp table, but you could use OPENXML directly in the
> > query.
> > But then you talk about an UPDATE as well, and if your aim is to insert
> > new rows, and update existing, it's probably better to use a temp
> > table (or a table variable), so that you don't have to run OPENXML twice.
> > Some DB engines support a MERGE command which performs the task of
> > UPDATE and INSERT in one statement, but this is not available in
> > SQL Server, not even in SQL 2005.
> > If this did not answer your question, could you please clarify?
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> > Books Online for SQL Server 2005 at
> > http://www.microsoft.com/technet/pr...oads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Have DTS Package prompt for a file name

I have a user in the IT department that wants a process to take his text file and import it into a SQL Server table. Simple enough with a DTS package.

The rub is that he want's to execute the package and have it prompt him at that point for where the file resides. I tried to get him to go into the DTS pacakge and update the connection, but he doesn't want to do it that way. I suggested renaming the file to a common name to be used each time the application runs, and he wasn't interested in that solution either.

Any help you can give would be greatly appreciated. Thanks!

Have a look at this:

http://www.sqldts.com/default.aspx?226

Have an Idea but not the answer.... :( needed help

Hi,

I have a table that has the ff:

LastName varchar(50)

FirstName varchar(50)

PhotoPath varchar(50)

Now I want to create a form that can accept the LastName,Firstname and also can upload a picture which in turn the filename of the image will be the value for the PhotoPath field, and eventually displays it using the repeater control.

Your Help/Info. is highly appreciated...

Jeff

This answers your question

http://forums.asp.net/t/1113089.aspx

|||

Check this tutorialhttp://www.asp.net/Learn/DataAccess/#binary

It explains you both options to save the photos (as a file as well as an image object in the database)

Another link:http://aspalliance.com/articleViewer.aspx?aId=138&pId

Thanks

|||

Hi Jeff,

Saving first and last names are simple enough, you can search for any good ADO.NET tutorial for this.

For uploading an image file, check this article:

http://www.4guysfromrolla.com/webtech/091201-1.shtml

After uploading, save the filepath in the DB table and then show it as the image url in the repeater control.

Hope this helps,

Vivek

sql

Have 100+ columns, or reduce it down to about 3?

Basically, what I'm doing is storing answers to questions in a survey. I have two ways I can organize the table:

1. Just having a table with lots of columns - one for each question

2. A table with only about maybe 3 columns:

SurveyID
QuestionID
QuestionAnswer

In this second case, the the primary key would be both SurveyID and QuestionID combined, of course.

I don't fully know the pros and cons of the two approaches, and both look like they would work. Right now, I'm using option 1, but I keep wondering if option 2 might be better. Whenever I change the questions, I currently have to drop and recreate the table (altering it is too much effort), and I know option 2 would be a way of avoiding that. By the way, the questions themselves are stored in an xml file, if it means anything. Anyhow, once the survey is being used, there shouldn't be any further changing of questions. And there's also just too much I don't know about (how is performance affected, for example?).

Any ideas which is better and why?if you're currently doing it like option 1, then presumably you are able to write some sql for it?

okay, a couple of sample problems, may we see your sql please

1) which surveys had more than half the questions answered correctly
2) which surveys had the same number of answers as survey 23|||None of the surveys have been answered already. I'm still writing the code that will store the answers given. The questions themselves aren't final yet, but the xml file is getting constantly updated as we decided what to ask and not to ask. So when the xml file is changed, I generate a new (rather large) Create Table statement, drop the old table, and create the new one so things can be tested out. It's just extra work while developing the survey.

When a new survey is started, a new record is created for it right from the beginning, with all columns set to null. As questions get answered, values get inserted. This way, it's possible to come back later for whatever reason.

I don't see how posting the entire Create Table statement helps anything. The questions' answers are stored as bits, ints, reals, datetimes, or varchars, depending on the kinds of questions. Unanswered questions remain null, of course. It looks like there are about 160 columns in the table right now that are specifically for answers to the questions.

Really, my question simply is am I going to be ok with having a large number of columns in the table, or am I better off using what I mentioned above as "option 2", reducing the number of columns in the table.|||Think about R937's question a little more. Suppose you inherited a system like your option 1. Further, suppose you were given requirements to write reports based on R937's questions. How would you go about it?

Design questions need to take into account how you store the data AND how you get it back out. Unless of course, you are designing a black hole, in which case, it does not matter how to get data out.|||didn't want to see the CREATE TABLE statement

wanted you to think about the SQL that you would have to write against the humungous table to get meaningful stats out of it

my advice: use whichever structure you feel most comfortab le with|||oh...so in your opinion, it doesn't really matter? I was wondering if maybe one way was technically superior and/or more efficient for some reason that I may not have already seen.|||oh...so in your opinion, it doesn't really matter? no, i didn't really say that :)

i think 1NF might be applicable here

actually, i would almost always implement option 2

but since you were asking for advice, and since you seem to have settled on option 1 already, i figured it was important to let you know that being comfortable with a technique also has some importance|||Whenever I change the questions, I currently have to drop and recreate the table (altering it is too much effort)Learn ALTER TABLE syntax - it is substantially less effort than the method you describe - especially once you have data in your tables.|||You have to go option 2. There is no other way...Don't even consider option 1 - total train wreck in the making.

(sorry to diffuse the suspense created by r937 but it was driving me nuts!)|||my current project is both 1 and 2 because we are processing gigs of claims every week. 1 for OLAP and 2 for OLTP. So my answer is both.sql

hash table (#) order by problem with more records

We have one single hash (#) table, in which we insert data processing
priority wise (after calculating priority).
for. e.g.

Company Product Priority Prod. QtyProd_Plan_Date
C1 P11100
C1 P22 50
C1 P33 30
C2 P11200
C2 P42 40
C2 P53 10

There is a problem when accessing data for usage priority wise.
Problem is as follows:

We want to plan production date as per group (company) sorted order and
priority wise.

==>With less data, it works fine.
==>But when there are more records for e.g. 100000 or more , it changes
the logical order of data

So plan date calculation gets effected.

==Although I have solved this problem with putting identity column and
checking in where condition.

But, I want to know why this problem is coming.

If anybody have come across this similar problem, please let me know
the reason and your solution.

IS IT SQL SERVER PROBLEM?

Thanks & Regards,
T.S.Negi> when there are more records for e.g. 100000 or more , it changes
> the logical order of data

Are you referring to the perceived order in the table? Rows in tables
have NO logical order in a relational database. If you require a
particular order you have to query them using a SELECT statement with
an ORDER BY clause otherwise the ordering is undefined.

If that doesn't answer your question then please describe your problem
with DDL (including keys), sample data INSERT statements and show your
required end result.

--
David Portas
SQL Server MVP
--|||While inserting records in hash table. It is already order by on some
fields.
But when selecting/updating records, I want the same order of records
should be updated/selected.

"Rows in tables have NO logical order in a relational database"
I think, True for hash(#) and permanent table.

T.S.Negi

David Portas wrote:
> > when there are more records for e.g. 100000 or more , it changes
> > the logical order of data
> Are you referring to the perceived order in the table? Rows in tables
> have NO logical order in a relational database. If you require a
> particular order you have to query them using a SELECT statement with
> an ORDER BY clause otherwise the ordering is undefined.
> If that doesn't answer your question then please describe your
problem
> with DDL (including keys), sample data INSERT statements and show
your
> required end result.
> --
> David Portas
> SQL Server MVP
> --|||There is an update condition. Which I want to make sure, performing on
ordered data (order by used at the time of insert).
I want to avoide loop.

Reason: "Rows in tables have NO logical order in a relational database"
!!!!

So Please advice.
Thanks,
T.S.Negi

Sample SQL:
===========

UPDATE #WK_PDR_ProcessingData SET
@.Opn_Stock_Qty= CASE WHEN (
@.Customer_Cd = Customer_Cd
AND @.Product_No = Product_No
AND @.Product_Site_Cd = Product_Site_Cd
AND @.Assy_Company_Cd = Assy_Company_Cd
AND @.Assy_Section_Cd = Assy_Section_Cd
AND @.Line_Cd = Line_Cd
) THEN @.Opn_Stock_Qty + @.Production_Qty - @.Requirement_Qty
ELSE begin_Stock_Qty END,
Calc_Stock_Qty= @.Opn_Stock_Qty + Production_Qty - Requirement_Qty,
@.Customer_Cd = Customer_Cd,
@.Product_No = Product_No,
@.Product_Site_Cd= Product_Site_Cd,
@.Assy_Company_Cd= Assy_Company_Cd,
@.Assy_Section_Cd= Assy_Section_Cd,
@.Line_Cd = Line_Cd,
@.Production_Qty = Production_Qty,
@.Requirement_Qty= Requirement_Qty
FROM #WK_PDR_ProcessingData|||tilak.negi@.mind-infotech.com (tilak.negi@.mind-infotech.com) writes:
> While inserting records in hash table. It is already order by on some
> fields.

And once it is inserted, there is no longer any order.

> But when selecting/updating records, I want the same order of records
> should be updated/selected.
> "Rows in tables have NO logical order in a relational database"
> I think, True for hash(#) and permanent table.

Well, obviously you have some operation that does not give you the
desired result, and you posted an UPDATE statement, which is a little
funny, because all you do is to assign a variable.

I suggest that you follow the standard recommendation and post:

o CREATE TABLE statement for your table(s)
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of what ou are trying to achieve.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||UPDATEs are not ordered either. The result of your UPDATE statement is
undefined, unreliable and, in my view, not useful.

Please specify the whole problem rather than post fragments of your
non-working solution. The best way to specify the problem is to post
DDL, sample data and required end results. See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--