Database Discussion Boards

Re: Where is my table created...

jschell12-May-11 9:28

jschell

12-May-11 9:28

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'.

showing column data as header

Ramkumar_S10-May-11 15:23

Ramkumar_S

10-May-11 15:23

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

Re: showing column data as header

PIEBALDconsult10-May-11 15:25

PIEBALDconsult

10-May-11 15:25

Looks like you need a join.

Re: showing column data as header

Ramkumar_S10-May-11 16:31

Ramkumar_S

10-May-11 16:31

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

Re: showing column data as header

Simon_Whale10-May-11 23:44

Simon_Whale

10-May-11 23:44

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.

Re: showing column data as header

J4amieC10-May-11 23:59

J4amieC

10-May-11 23:59

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

Re: showing column data as header

mhwasim11-May-11 7:53

mhwasim

11-May-11 7:53

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

Set variable values from queried data

MAW3010-May-11 9:50

MAW30

10-May-11 9:50

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

Re: Set variable values from queried data

David Mujica10-May-11 10:40

David Mujica

10-May-11 10:40

This SQL works for me ...

begin 

declare @max_date datetime

set @max_date = (select max(creation_date) from fsformula)

print @max_date

end

Re: Set variable values from queried data

Simon_Whale10-May-11 11:39

Simon_Whale

10-May-11 11:39

you need to look at using output parameters, further reading MSDN: Using a Stored Procedure with Output Parameters[^]

Nagy Vilmos wrote:
And eat bacon. Bacon's real important for 'puters.

Re: Set variable values from queried data

SilimSayo11-May-11 3:13

SilimSayo

11-May-11 3:13

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

How do I join for tables together?

Justiin126510-May-11 9:09

Justiin1265

10-May-11 9:09

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')

Re: How do I join for tables together?

Luc Pattyn10-May-11 9:26

Luc Pattyn

10-May-11 9:26

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[^]

Smile | :)

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.

Re: How do I join for tables together?

SilimSayo11-May-11 7:50

SilimSayo

11-May-11 7:50

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.

How do I to mass update date and time?

Justiin12659-May-11 18:06

Justiin1265

9-May-11 18:06

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' )

Re: How do I to mass update date and time?

Blue_Boy9-May-11 20:49

Blue_Boy

9-May-11 20:49

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

Re: How do I to mass update date and time?

Justiin126510-May-11 3:30

Justiin1265

10-May-11 3:30

Thank you. It worked!

Re: How do I to mass update date and time?

Blue_Boy10-May-11 3:59

Blue_Boy

10-May-11 3:59

No problem Smile | :)

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

Re: How do I to mass update date and time?

Corporal Agarn10-May-11 2:21

Corporal Agarn

10-May-11 2:21

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

Execution Plan Caching and Reuse

PIEBALDconsult9-May-11 15:30

PIEBALDconsult

9-May-11 15:30

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;

Re: Execution Plan Caching and Reuse

Luc Pattyn9-May-11 15:33

Luc Pattyn

9-May-11 15:33

Nothing another shower couldn't fix, I hope.

Smile | :)

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.

Re: Execution Plan Caching and Reuse

PIEBALDconsult9-May-11 15:59

PIEBALDconsult

9-May-11 15:59

:: PIEBALD closes his blinds ::

Actually, I've already switched to tequila.

Update All records

ivo758-May-11 1:50

ivo75

8-May-11 1:50

Hi, I use MS Access 2003 database and I want to Update all records in 1 column. How to make this in once? I use VB.NET

Re: Update All records

Luc Pattyn8-May-11 2:42

Luc Pattyn

8-May-11 2:42

I would try a regular update statement without a WHERE clause, as in:

UPDATE table_name SET column_name=value

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.

Re: Update All records

ivo758-May-11 3:11

ivo75

8-May-11 3:11

In this column have numeric data from 1 to 12 and I want to increase them with 1 e.g. 1->2 etc

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.