|
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
|
|
|
|
|
For SQL Server you might try:
SET DOC_DATE = CAST('20-APR-2011 7:00:00 AM' AS DATETIME)
or possibly:
SET DOC_DATE = '20-APR-2011 7:00:00' -- NO AM
|
|
|
|
|
I was reading this (( http://technet.microsoft.com/en-us/library/ms181055.aspx[^] )) today, and was shocked (shocked I tell you) by the following statement:
The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:
SELECT * FROM Person;
SELECT * FROM Person.Person;
|
|
|
|
|
Nothing another shower couldn't fix, I hope.
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.
|
|
|
|