SQL Update Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Update Statement

Author

Topic

yaqen
Starting Member

6 Posts

Posted - 2008-10-08 : 01:42:42

Hi guys, ive got a weird problem this time. I work for BI company.
They want me to find the solution.

Example.

Field Dimdate have a few columns.

Now what i have to do is, one of the column i need to key in ( update where ) day data. We got 365 day in one year right,so the problem is, how i want to insert 1 - 365 day in one time. without i need to key in one by one.

example.

Dimtime Table.

Dataval | YearVal | Monthval | DaycountVal.???
10-1.1990 | 1990 | 1 | 1
11-1.1990 | 1990 | 1 | 2
13-1.1990 | 1990 | 1 | 3 --------- until 365 days.

If i just need to key in 1990 its ok.. but the big problem is.. i need to key in until year 2050 huhu..

thanks guys

heading to passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 01:54:12

[code]SELECT y.[Year],v1.number AS Daycountval
FROM (SELECT 1990 AS Year union all
SELECT 1991 union all
SELECT 1992 union all
................
SELECT 2050)y
CROSS JOIN master..spt_values v1
WHERE v1.type='p'
AND v1.number BETWEEN 1 AND 365[/code]

yaqen
Starting Member

6 Posts

Posted - 2008-10-08 : 02:12:33

ok i will try 1st.. thanks for reply.. u make me fall in luv with this forum :)

heading to passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 02:14:36

quote:
Originally posted by yaqen

ok i will try 1st.. thanks for reply.. u make me fall in luv with this forum :)

heading to passion

welcome

try and let me know how you got on

yaqen
Starting Member

6 Posts

Posted - 2008-10-08 : 03:07:08

CROSS JOIN master..spt_values v1

hi visakh, what does it means CJMaster . . ( dot dot )spt_values v1

heading to passion

yaqen
Starting Member

6 Posts

Posted - 2008-10-08 : 03:10:57

Error starting at line 1 in command:
Select y.[DIMTIME],v1.number AS DAYCALYEARYWD
from (select 1990 AS Year union all
select 1991 Union all
select 1992 union all
)y
Cross Join Master..spt_values v1
And v1.number BETWEEN 1 and 365

Error at Command Line:1 Column:9
Error report:
SQL Error: ORA-01747: invalid user.table.column, table.column, or column specification
01747. 00000 - "invalid user.table.column, table.column, or column specification"
*Cause:
*Action:

i think maybe problem with sql statement... i try to identify it.

heading to passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 03:24:37

Ah...so you are using oracle. this is sql server forum. so solutions given will work only in sql server. If you need oracle specific solutions post in some oracle forums. there's one at www.dbforums.com

yaqen
Starting Member

6 Posts

Posted - 2008-10-08 : 03:27:19

haha sory2 :D

heading to passion

yaqen
Starting Member

6 Posts

Posted - 2008-10-08 : 03:32:23

thanks visakh for introducing other dbs forum.

heading to passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 03:56:05