|
I have a couple of newbie questions for SQL Server. On the SQL Server Management Studio, I used the following script to delete and create a table on the master databse:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PARTNOTE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[PARTNOTE]
GO
CREATE TABLE [dbo].[PARTNOTE] (
[NUMBER] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTE_NUM] [float] NULL ,
[NOTE] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I can run this as many times as I want. Both deletion and creation work well. My first question is "After the creation, where is the table creates?" I couldn't find where the table object from Object Explorer.
Now I modify the script to perform the same tasks not on the master, but on "MyDatabase":
if exists (select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[PARTNOTE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [MyDatabase].[dbo].[PARTNOTE]
GO
CREATE TABLE [MyDatabase].[dbo].[PARTNOTE] (
[NUMBER] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTE_NUM] [float] NULL ,
[NOTE] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
The creation still works but the deletion does not. It couldn't find where the table was creates.
Could someone please explain what goes wrong in the modified script? Thanks!
Best,
Jun
|
|
|
|
|
After you execute script, refresh Tables node by right click on it and click refresh, it should shows created tables.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Thanks. The table created shows up after the refreshing.
Another question. My modified script
if exists (select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[WL_HEADU]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [MyDatabase].[dbo].[WL_HEADU]
GO
cannot detect the existence of the table I created. If I just execute
drop table [MyDatabase].[dbo].[WL_HEADU]
GO
it works well. What is a proper query I should make in order to detect the table created in "MyDatabase"?
Best,
Jun
|
|
|
|
|
Look to the if statement. Check what
select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[WL_HEADU]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 gives you when the file is known to exist. I have 2008 and it uses sys.object and I do not remember how to check for exists from prior.
|
|
|
|
|
Figured it out...adding
USE [MyDatabase]
GO
before the query can detect the table created.
Best,
Jun
|
|
|
|
|
That's great 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
The table is on the cloud
|
|
|
|
|
Jun Du wrote: create a table on the master databse:
You need to learn to create your own database and then tables go in that.
And once you do delete everything that you created in 'master'.
|
|
|
|
|
Hi All,
I have two table like this
Table1
-------
empcode basic salary
------- --------------
160 3000
170 4000
Table 2
---------
Amount Additions empcode
------ ---------- -------
180 Bonus 160
25 Transport 160
so i want the output like this
empcode basic Salary Bonus Transport
------- ------------ ----- ----------
160 3000 180 25
179 4000 175 45
Please help me with this ..I am new to DB
Ramkumar
("When you build bridges you can keep crossing them. ")
http://ramkumarishere.blogspot.com
|
|
|
|
|
Looks like you need a join.
|
|
|
|
|
Thanks
My Table 2 is dynamic value ..so header should change based on Addition column
Table 2
---------
Amount Additions empcode
------ ---------- -------
180 Bonus 160
25 Transport 160
Ramkumar
("When you build bridges you can keep crossing them. ")
http://ramkumarishere.blogspot.com
|
|
|
|
|
Assuming you are using SQL Server.
I would suggest that you read up on pivot queries. MSDN: Pivot Query[^]
Nagy Vilmos wrote: And eat bacon. Bacon's real important for 'puters.
|
|
|
|
|
Assuming SQL Server, You can achieve this using the following PIVOT
select empcode, basicSalary, ISNULL(Bonus,0) as Bonus, ISNULL(Transport,0) as Transport
from
(SELECT s.empcode, s.basicSalary, a.description, a.amount
FROM salary s
LEFT JOIN additions a
ON s.empcode=a.empcode ) AS SalaryWithAdditions
PIVOT(
SUM(amount)
FOR description IN ([Bonus],[Transport])
) AS PivotTable
Output with your test data:
empcode basicSalary Bonus Transport
160 3000 180 25
170 4000 0 0
|
|
|
|
|
Check the following query...
SELECT T1.empcode,T1.[basic Salary],B.Bonus,T.Transport
FROM Table1 T1
INNER JOIN (SELECT Amount As Bonuus, empcode FROM Table2 WHERE Additions = 'Bonus') B ON B.empcode = T1.empcode
INNER JOIN (SELECT Amount AS Transport, empcode FROM Table2 WHERE Additions = 'Transport') T ON T.empcode = T1.empcode
Adjust the inner join to left join if required...
Thanks
|
|
|
|
|
I am trying to retrieve the Earliest and latest Dates and prices
Using the following code, which works fine. I need to enter the values to a variable
as seen below, which does not work can someone please help
Thanks in advance,
Michael
USE "Sales"
GO
SET NOCOUNT ON;
DECLARE
@dtEarliestDate DATETIME,
@fltEarliestPrice REAL,
@dtLatestDate DATETIME,
@fltLatestPrice REAL
SELECT * FROM
(
SELECT TOP 1 dtDateTime, fltPrice FROM Customers ORDER BY dtDateTime DESC
UNION ALL
SELECT TOP 1 dtDateTime, fltPrice FROM Customers ORDER BY dtDateTime ASC
)
AS B
--SET @dtEarliestDate = B.dtDateTime
--SET @dtLatestDate = B.dtDateTime
--SET @fltLatestPrice = B.fltPrice
--SET @fltEarliestPrice = B.fltPrice
|
|
|
|
|
This SQL works for me ...
begin
declare @max_date datetime
set @max_date = (select max(creation_date) from fsformula)
print @max_date
end
|
|
|
|
|
|
I haven't tested this but try it .
SELECT TOP 1 @dtLatestDate=dtDateTime, @fltLatestPrice =fltPrice FROM Customers ORDER BY dtDateTime DESC
SELECT TOP 1 @dtEarliestDate =dtDateTime, @fltEarliestPrice=fltPrice FROM Customers ORDER BY dtDateTime ASC
|
|
|
|
|
Hi,
I have to join four tables together and am having trouble. My query as it stands is returning data, but it's not the data I need. I do not think I have all four tables joined together correctly. How would I join the tables properly? Thank you.
Justin
I need to join the CXADMIN.RO_FAILURE_DTL RF, CXADMIN.RO_HIST RH, saadmin.sa_repair_part@elgsad rp, and saadmin.sa_code_group_task_dtl @ELGSAD cg tables together.
Here is my query so far:
SELECT distinct RF.REPAIR_ORD, RH.RECV_UNIT, RH.RECV_SERIAL_NBR, rf.created_date, RP.FAULT_CODE, RP.REPAIR_ACTION_CODE, cg.task_code
FROM CXADMIN.RO_FAILURE_DTL RF, CXADMIN.RO_HIST RH, saadmin.sa_repair_part@elgsad rp, saadmin.sa_code_group_task_dtl @ELGSAD cg
WHERE RF.REPAIR_ORD = RH.REPAIR_ORD and Rp.REPAIR_ORD = cg.REPAIR_ORD
AND RF.FAILURE_CODE ='DISK'AND RH.CURR_FACILITY_ID ='23' AND RF.CREATED_DATE >'1-JUN-2010' AND RF.CREATED_DATE < '1-dec-2010'
AND (CG.TASK_CODE ='PHMD' OR CG.TASK_CODE ='PHSN' OR CG.TASK_CODE ='CHMD' OR CG.TASK_CODE ='CHSN')
|
|
|
|
|
For joining tables, SQL offers a very specific keyword, it is documented here[^]. You would need three of those for joining four tables together.
Warning: There are some variations, controlling what to do when rows in one table don't match rows in the other tables. Here is an article that might help you on the subject: Visual Representation of SQL Joins[^]
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Your code is using an implicit equijoin and a cartesianjoin.
WHERE RF.REPAIR_ORD = RH.REPAIR_ORD and Rp.REPAIR_ORD = cg.REPAIR_ORD
For simplicity, let me assume the actual table names are RF, RH, RP and CG. RH and RH are joined and RP and CG are joined but
RF is not joined to RP or CG; and also RH is not joined to RP and CG. So the end result is a cartesian product of two equijoins (RF-RH X RP-CG). I would suggest using an explicit join. From your code, I assume that REPAIR_ORD is common to all the tables. So my code would look like
SELECT .....
FROM
RF INNER JOIN RH
ON RF.REPAIR_ORD=RH.REPAIR_ORD
INNER JOIN RP
ON RH.REPAIR_ORD=RP.REPAIR_ORD
INNER JOIN CG
ON RP.REPAIR_ORD=CG.REPAIRD_ORD
WHERE ....
I am using inner joins but depending on what you need, you may find left joins or right joins more suitable.
|
|
|
|
|
Hi,
I have to query an update for date and time. I know how to update the date alone, but I am having trouble with adding the time to the function. Right now, as it stands, it reads 4/20/2011 1:32:07 PM. I need the dock_date field to read 4/21/2011 7:00:00 AM. When I add 07:00:00 AM to the query I receive a DB error of: ORA-01830: date format picture ends before converting entire input string. Can someone please help me?
Thank you,
Justin
I am joining two tables: cxadmin.ro_hist and cxadmin.ord_dtl and use the in function b/c I have to update multiple rows.
My query so far is:
UPDATE cxadmin.ro_hist
SET DOCK_DATE = '20-APR-2011 7:00:00 AM'
where repair_ord in (
select recv_repair_ord from cxadmin.ord_dtl
where ord_nbr = '602945RR' AND DOCK_DATE > '20-APR-2011' )
|
|
|
|
|
Have you tried to convert on date type?
e.g TO_DATE('20-APR-2011 7:00:00 AM', 'dd-MM-yyyy HH:MI:SS AM')
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
|
No problem
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
