|
This article[^] should provide the solution to your question.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
can i use the table datatype in stored procedure sql server?
actually i have few rows in the gridview asp.net 2.0 , i want those rows data in the stored procedure.
is there any table like datatype available?
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
|
Sonia Gupta wrote: is there any table like datatype available?
Yes, table variables are available.
Declare @table_name Table(column_name1 datatype,column_name2 datatype)
Ref: Table[^]
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
Hi,
I am creating a procedure in which I want to check that if a database is already exists then drop the database and create the new database and if the database is not exist then just create the database.
How can I do this.
Plz help me....
Thanks in Advance
Alok...
|
|
|
|
|
You can check database existence like this:
if exists (select * from sys.databases where name = 'demo')
// database exists, drop it and create new
else
// database doesn't exist so just create it
|
|
|
|
|
|
You are welcome 
|
|
|
|
|
I am attempting to get a result set from a query and it is not how I expected. (Go figure!)
Tables Structure:
Production: Date, WOID (FK)
WorkOrder: WOID (PK), ProdID (FK)
Product: ProdID (PK), PCatID (FK)
Category: PCatID (PK), Category
FormTrans: Date, WOID (FK), Kgs
I want to return results in the following Table structure:
Date, CategoryA, CategoryC, CategoryC,..., CategoryN, Date Kgs, Ave, Running Total
I have tried the following:
DECLARE @Start DateTime,
@End DateTime
SET @Start = '01/01/2007'
SET @End = '12/31/2007'
Select Distinct(pr.[Date],
Case When pc.Category IN ('Mini','Maxi','OSCS')
Then SUM(ft.Kgs)
Else 0
End As 'DUCT',
Case When pc.Category IN ('DSPETG','DSPVC')
Then SUM(ft.Kgs)
ELSE 0
END AS 'DS",
SUM(ft.Kgs) As 'Kgs' -- Date Kgs
-- Average for distinct date required here?
-- Running Total for Dates here?
FROM Production pr
JOIN WorkOrder wo
ON pr.WOID = wo.WOID
JOIN Product p
ON wo.ProdID = p.ProdID
JOIN ProdCategory pc
ON p.PCatID = pc.PCatID
JOIN Formula f
ON wo.FormulaID = f.FormulaID
JOIN FormTrans ft
ON ft.FormulaID = f.FormulaID
WHERE pr.[Date] BETWEEN @Start AND @End
GROUP BY pr.[Date],pc.Category
ORDER BY pr.Date
1, When I run this I am getting data but a single line for each category calculation per date and not a single date row for each date with multiple results on the same row.
2, How to implement the average, and running total columns?
I hope I have explained myself clearly.  
Any help is most appreciated.
Andy.
I don't speak Idiot - please talk slowly and clearly
modified on Monday, February 25, 2008 1:29 AM
|
|
|
|
|
I don't have enough information to help you - I would need to understand the relationships between tables - however one hint I will give is that I notice that there are no right/left outer joins.
I have rarely seen queries with so many inner joins and none of them being left/right outer joins.
By using inner joins you are restricting your data set - are you sure you want to do this?
One suggestion:
Rewrite the query one join at a time - this way you can check at each join if the results are what you expect.
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
Thanks Guy.
I will play around with the Join clauses - I am not too flash on Outer Joins (as you could probable tell)
By the way, All tables are related via Keys.
Ie: WorkOrder PK is WOID, Category PK is PCatID etc...
Where these are found in other tables, they are foreign keys.
Cheers
Andy
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
|
Hi there,
I got two tables, lets say X and Y.
Table X has a column ID, and table Y has a column ID_TO_X and VALUE.
I have to make a select that returns the ID of each row in table X and another column named HOMOGENIC, that returns "YES" if for each ID_TO_X = ID has the same VALUE or "NO" if not.
for example
TABLE X TABLE Y QUERY
ID ID_TO_X VALUE ID HOMOGENIC
1 1 10 1 YES
2 1 10 2 NO
3 2 20 3 YES
2 30
3 40
I'm using oracle 9
Can someone help me??
Thanks a lot,
Caio
|
|
|
|
|
TABLE X
ID
1
2
3
2
3
TABLE Y
ID_TO_X | VALUE
1 | 10
1 | 10
2 | 20
2 | 30
3 | 40
QUERY
ID | HOMOGENIC
1 | YES
2 | NO
3 | YES
|
|
|
|
|
i need to declare variables dynamic , then this my code for make that .but i can't used
why????????????????????
Declare @TableName Char(100)
Set @TableName = 'Ar_PromotionDetails' --{Columns}PromotionId
Declare Cur_Sys_DataAdapterTable Cursor
For
Select Cln.name As [Columns] , Type.Name Types , Cln.Length ,
Cln.xprec , Cln.xscale
From sysobjects Obj
inner join syscolumns Cln
On Obj.Id = Cln.id
Inner join systypes Type
On Type.xtype = Cln.xtype
Where obj.id = object_Id(@TableName)
Declare @ColumnName char(120) , @TypeName char(120)
Declare @Length SmallInt
Declare @xprec Tinyint , @xscale Tinyint
Open Cur_Sys_DataAdapterTable
Fetch Next From Cur_Sys_DataAdapterTable
Into @ColumnName , @TypeName , @Length ,@xprec , @xscale
Declare @DeclareVariable NChar(300)
Set @DeclareVariable =N''
While @@fetch_status = 0
begin
if ( LOWER(Rtrim(@TypeName)) in ( 'datetime' , 'bit' , 'int' , 'bigint',
'smallint' , 'tinyint' , 'bigint' ,
'smalldatetime' , 'float') )
Set @DeclareVariable = Rtrim(@ColumnName) + space(5) + Rtrim(@TypeName)
else
if ( LOWER (Rtrim(@TypeName)) in ('numeric' , 'decimal' ) )
Set @DeclareVariable = Rtrim(@ColumnName) + space(5)+Rtrim(@TypeName) +
N'('+ Rtrim(@xprec)+ N',' + Rtrim(@xscale) +N')'
else
Set @DeclareVariable = Rtrim(@ColumnName) + space(5)+Rtrim(@TypeName)+N'(' + Rtrim(@Length) + N')'
Set @DeclareVariable = N'Declare @@' + @DeclareVariable + Char(13)
Exec sp_Executesql @DeclareVariable
print 'load ok...'
Fetch Next From Cur_Sys_DataAdapterTable
Into @ColumnName , @TypeName , @Length , @xprec , @xscale
End
Close Cur_Sys_DataAdapterTable
deallocate Cur_Sys_DataAdapterTable
Select @@PromotionId --when i use this command make error (Server: Msg 137, Level 15, State 2, Line 62
Must declare the variable '@@PromotionId'.)
how i can resolve this problem?
123
|
|
|
|
|
I Need to list all employees and their date of hire (HireDate) in the
format "ALLEN was hired on the 20TH of FEBRUARY."
Emp Table
EmpNo Ename Job Mgr HireDate
7369 SMITH CLERK 7902 12/17/1980
7499 ALLEN SALESMAN 7698 2/20/1981
7521 WARD SALESMAN 7698 2/22/1981
7566 JONES MANAGER 7839 4/2/1981
Month table
Month MonthName
1 January
2 February
3 March
4 April
ordinals table
DayNumber DayOrdinal
1 1st
2 2nd
3 3rd
4 4th
|
|
|
|
|
Have you tried to solve the problem yourself?
Or are you looking for someone to do your homework for you?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Looks pretty straightforward to me. What have you tried ?
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
You are going to need to use some of the functions in Access to pull different parts from your query result. What have you tried so far?
"That's the problem with a spell checker. It only helps with bad spelling, not stupidity." - Rob Graham
|
|
|
|
|
Can someone please help show me how to combine 2 unrelated tables? for example
I have:
TABLE A with values
DESC
DM
ED
and TABLE B with values
DATE
01/07/2008
01/14/2008
02/18/2008
I want TABLE C to look like this
DESC -- DATE
DM -- 01/07/2008
DM -- 01/14/2008
DM -- 02/18/2008
ED -- 01/07/2008
ED -- 01/14/2008
ED -- 02/18/2008
Thanks..
|
|
|
|
|
I tried this and it worked..
Select desc, date
from TableA, TableB
|
|
|
|
|
SELECT * FROM tableA
CROSS JOIN tableb
|
|
|
|
|
I used the crystal report wizard to connect a Ms SQL Server. During the procedure the wizard requested for the username and password to the database.
However, when i run the application and want to pull up a report the page request for the database password.
What am i doing wrong?
JESA
|
|
|
|
|
Does anyone know of, or can point me in the direction of a database address schema? Ideally I would like a schema that can handle addresses throughout the world.
My current design uses the lowest element (excluding street address) in the address as one location id (eg in Australia it would be the suburb and I can then recurse to find postcode, state and country.
In (say UK), this doesn't work too well as the postcode is the lowest element in an address followed by city, county and country (and no doubt other variations). I'm guessing some other countries have more elements in the address as well, so it would be nice to be able to handle them as well.
|
|
|
|
|
What do you mean by 'lowest element' ? I'd look at a few sites like Amazon, those guys have solved the issue of a generic address, I am willing to bet.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
I had a look at Amazon and its very basic. They must rely on server power rather than elegent design. There is no difference between data in different countries. Ebay is much better, groups by country and then county/state/province etc. Doesn't appear to then divide into city and locality though, so although its part way to what I want, its seems to be less than my current design.
When I say lowest element, I think the official term is granularity, eg in Australia, my address is 'my place', THORNTON, NSW, 2322, Australia. Thornton is the most granular element (apart from the actual street address) which is a member of postcode(2322) which itself is a member of state (NSW) et seq.
In contrast the UK addresses most granular element is the postcode followed by region/city, county et seq.
Perhaps I'm just getting too picky!
|
|
|
|
|
Not quite mate 
Postcodes are seperate to locales (thornton). If you grab the data from auspost you'll find that while similar, postcodes can span multiple locales and locales can span multiple postcodes.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
