SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

gotly: Oct 28, 2007; Economy-Sized

Having

Now I remember, thanks!

# ? Mar 9, 2009 04:15

Adbot: ADBOT LOVES YOU

# ? May 9, 2024 22:48

fez2: Jan 24, 2002

I'm a little lost. I'm trying to write a stored procedure for DB2 from a stored procedure in SQL Server 2005.

Basically, I have to make the Enterprise Library work with DB2. I was able to convert the tables, but the stored procedures are killing me.

SQL Server 2005:

code:

USE [Logging]
GO
/****** Object:  StoredProcedure [dbo].[InsertCategoryLog]    Script Date: 03/09/2009 13:35:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertCategoryLog]
	@CategoryID INT,
	@LogID INT
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @CatLogID INT
	SELECT @CatLogID FROM CategoryLog WHERE CategoryID=@CategoryID and LogID = @LogID
	IF @CatLogID IS NULL
	BEGIN
		INSERT INTO CategoryLog (CategoryID, LogID) VALUES(@CategoryID, @LogID) 
		RETURN @@IDENTITY
	END
	ELSE RETURN @CatLogID
END

Out of that I created:

code:

CREATE PROCEDURE Aardvark.INSERTCATEGORYLOG ( IN Cat_ID INTEGER, IN Log_ID INTEGER )
	DYNAMIC RESULT SETS 0
	MODIFIES SQL DATA
	NOT DETERMINISTIC
	CALLED ON NULL INPUT
	LANGUAGE SQL

P1: BEGIN ATOMIC
	SET CURRENT SCHEMA = 'Aardvark';
	
	DECLARE CatLogID INT default null;
	
	SELECT CategoryLog.CategoryID into CatLogID FROM Aardvark.CategoryLog WHERE CategoryLog.CategoryID = Cat_ID and CategoryLog.LogID = Log_ID;
	
	IF CatLogID = NULL THEN	
		INSERT INTO Aardvark.CategoryLog (CategoryID, LogID) VALUES( Cat_ID, Log_ID);
		select identity_val_local() into CatLogID from sysibm.sysdummy1;
		return CatLogID;
	END IF;
	
	RETURN CatLogID;
	
END P1

Can anyone tell me what I'm doing wrong? I can't find much on DB2 (probably because it was invented before HTTP

)

Edited for solution: It turns out that DB2 throws the error on the previous line, not the one it hangs on processing.. So it should have been "IF (CatLogID IS NULL) Then".

fez2 fucked around with this message at 18:20 on Mar 16, 2009

# ? Mar 9, 2009 19:02

No Safe Word: Feb 26, 2005

fez2 posted:

I'm a little lost. I'm trying to write a stored procedure for DB2 from a stored procedure in SQL Server 2005.

Basically, I have to make the Enterprise Library work with DB2. I was able to convert the tables, but the stored procedures are killing me.

<snip>

Can anyone tell me what I'm doing wrong? I can't find much on DB2 (probably because it was invented before HTTP )

What errors are you getting? I did a bit of DB2 stuff on my last project (and in fact, we implemented the ASP.NET Membership Provider, which is heavily tied to SQL Server out of the box, in DB2) but honestly we stayed away from DB2 stored procedures in general because of some weird issue with the particular iSeries version we were using and the data provider we had. So, we mostly went with :ughh:

dynamic SQL :ughh:

# ? Mar 9, 2009 19:59

fez2: Jan 24, 2002

No Safe Word posted:

What errors are you getting? I did a bit of DB2 stuff on my last project (and in fact, we implemented the ASP.NET Membership Provider, which is heavily tied to SQL Server out of the box, in DB2) but honestly we stayed away from DB2 stored procedures in general because of some weird issue with the particular iSeries version we were using and the data provider we had. So, we mostly went with dynamic SQL

I'll have to make a custom membership provider down the road too. Luckily that isn't tied too closely to DB2.

In Data Studio, the deploy errors read like so:

code:

Running
AARDVARK.INSERTCATEGORYLOG - Deploy started.
Create stored procedure returns SQLCODE: -204, SQLSTATE: 42704.
AARDVARK.INSERTCATEGORYLOG: 26: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=Aardvark.CATEGORYLOG, DRIVER=3.53.71
DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=Aardvark.CATEGORYLOG, DRIVER=3.53.71
AARDVARK.INSERTCATEGORYLOG - Deploy failed.
AARDVARK.INSERTCATEGORYLOG - Roll back completed successfully.

If I try to load it from the command prompt it just hangs. If I interrupt it then it says it's stuck on line 13. Emphasis on 13 below:

Note that I've changed the procedure some.. I have no idea how to make this monster happy.

quote:

CREATE PROCEDURE INSERTCATEGORYLOG ( IN Cat_ID INTEGER, IN Log_ID INTEGER, OUT CatLogID INTEGER )
DYNAMIC RESULT SETS 1
MODIFIES SQL DATA
NOT DETERMINISTIC
CALLED ON NULL INPUT
LANGUAGE SQL

P1: BEGIN

SET CURRENT SCHEMA = "Aardvark";

SELECT CategoryID into CatLogID FROM "Aardvark".CategoryLog WHERE CategoryID = Cat_ID and LogID = Log_ID;

IF (CatLogID = NULL) THEN
INSERT INTO "Aardvark".CategoryLog (CategoryID, LogID) VALUES( Cat_ID, Log_ID);
select identity_val_local() into CatLogID from sysibm.sysdummy1;

END IF;

END P1
@

I've been reading the IBM Redbook on SQL Server to DB2, but it isn't very helpful.

# ? Mar 9, 2009 20:09

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

Using the GROUP_CONCAT, I'd like to do a big OR statement in my query:

code:

SELECT			GROUP_CONCAT(node.title separator '||| |||')   AS titles
FROM			bc_bests
LEFT JOIN		node
ON			node.nid = bc_bests.ranked_nid
WHERE			node.nid = 	(	SELECT		
						DISTINCT    GROUP_CONCAT(bc_bests.ranked_nid separator ' OR node.nid = ')
						FROM		bc_bests
						WHERE		bc_bests.best_nid = 8286 OR bc_bests.best_nid = 8289
						AND		bc_bests.ranked_rank = 1
					)

So in the sub-statement, the output is something like:

code:

+-----------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(bc_bests.ranked_nid separator ' OR node.nid = ')                                       |
+-----------------------------------------------------------------------------------------------------+
| 7974 OR node.nid = 7926 OR node.nid = 7988 OR node.nid = 7966 OR node.nid = 7929 OR node.nid = 7964 |
+-----------------------------------------------------------------------------------------------------+

So it is in shape for a big OR statement.

How do I make sure the query actually reads this.

The output of it now is something completely wrong. It gives me a row that its not even in the "ranked_nid = 1" category!

8286 and 8289 are temporary numbers that I will give later from outside the SQL.

# ? Mar 10, 2009 05:12

geetee: Feb 2, 2004; >;[

code:

WHERE			node.nid IN 	(	SELECT          bc_bests.ranked_nid
						FROM		bc_bests
						WHERE		(bc_bests.best_nid = 8286 OR bc_bests.best_nid = 8289)
						AND		bc_bests.ranked_rank = 1
					)

# ? Mar 10, 2009 05:16

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

EDIT:

It worked, sorry man. Whoops, Bad copy and paste.

But is there a way to do the "OR = xyz" in a query like that though?

cannibustacap fucked around with this message at 07:02 on Mar 10, 2009

# ? Mar 10, 2009 05:28

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

Okay, so now I have a new but related issue :smith:

When I use the GROUP_CONCAT, it seems to only return 1 single row.

Here is what I got:

code:

SELECT			
DISTINCT		node.title		AS title
FROM			node
LEFT JOIN		bc_bests
ON			node.nid = bc_bests.ranked_nid
WHERE			bc_bests.ranked_nid IN 	(	SELECT      bc_bests.ranked_nid
							FROM	bc_bests
							WHERE	   (bc_bests.best_nid = 8286 OR bc_bests.best_nid = 8289)
							AND			bc_bests.ranked_rank = 1
						);

This returns multiple rows. Good..

code:

+-------------------------------------------+
| title                                     |
+-------------------------------------------+
| Panasonic TH-42PX80U 42" Plasma TV        |
| Panasonic Viera TH-58PZ800U 58" Plasma TV |
| Panasonic TH-50PZ800U 50" Plasma TV       |
| Samsung LN52A650 52" LCD TV               |
+-------------------------------------------+

However, when I add the GROUP_CONCAT, it only returns one row..

code:

SELECT			
DISTINCT		node.title										AS title,
			GROUP_CONCAT(bc_bests.best_nid separator '+')   AS bids
FROM			node
LEFT JOIN		bc_bests
ON			node.nid = bc_bests.ranked_nid
WHERE			bc_bests.ranked_nid IN 	(	SELECT      bc_bests.ranked_nid
							FROM	bc_bests
							WHERE	   (bc_bests.best_nid = 8286 OR bc_bests.best_nid = 8289)
							AND			bc_bests.ranked_rank = 1
						);

and..

code:

+-------------------------------------------+------------------------------------------------------------------------------------------------------------+
| title                                     | bids                                                                                                       |
+-------------------------------------------+------------------------------------------------------------------------------------------------------------+
| Panasonic Viera TH-58PZ800U 58" Plasma TV | 8287+8541+8287+8289+8290+8284+8285+8286+8288+8289+8290+8291+8538+8538+8538+8539+8540+8542+8542+10848+10952 |
+-------------------------------------------+------------------------------------------------------------------------------------------------------------+

So, now it is only returning one row...

My hope is that it can return all 4 rows with the "bids" column intact for all of them.

Really appreciate your help so much guys

EDIT: For easy viewing and not breaking the tables, I reduced the line "WHERE (bc_bests....)" since it is rather long and not needed. But that explains why there is 4 and not 2 rows in the "good" query

EDIT2:
Ohhhhh you know what I notice? The long "bids" cell contains ALL the bids and is only returning the first title.

More updated question: So how do I make sure that the bids are separate for each title?

cannibustacap fucked around with this message at 07:40 on Mar 10, 2009

# ? Mar 10, 2009 07:31

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

code:

SELECT			
node.title										AS title,
			GROUP_CONCAT(bc_bests.best_nid separator '+')   AS bids
FROM			node
LEFT JOIN		bc_bests
ON			node.nid = bc_bests.ranked_nid
WHERE			bc_bests.ranked_nid IN 	(	SELECT      bc_bests.ranked_nid
							FROM	bc_bests
							WHERE	   (bc_bests.best_nid = 8286 OR bc_bests.best_nid = 8289)
							AND			bc_bests.ranked_rank = 1
						)
GROUP BY node.title;

# ? Mar 10, 2009 11:47

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

Jethro posted:

code:

SELECT			
node.title										AS title,
			GROUP_CONCAT(bc_bests.best_nid separator '+')   AS bids
FROM			node
LEFT JOIN		bc_bests
ON			node.nid = bc_bests.ranked_nid
WHERE			bc_bests.ranked_nid IN 	(	SELECT      bc_bests.ranked_nid
							FROM	bc_bests
							WHERE	   (bc_bests.best_nid = 8286 OR bc_bests.best_nid = 8289)
							AND			bc_bests.ranked_rank = 1
						)
GROUP BY node.title;

Sweet it worked! You guys are fuckin rad!

One thing I had to do was add the "bc_Bests.ranked_rank = 1" line outside the subquery also or it repeated data.

Sweeeeeeeeeeeeeeet!

cannibustacap fucked around with this message at 21:27 on Mar 10, 2009

# ? Mar 10, 2009 21:23

MrSaturn: Sep 8, 2004; Go ahead, laugh. They all laugh at first...

I'm working on a project for a small company that keeps their data in an mssql database. They've provided me with a .bak backup copy of their database to use locally on my test machine. However, in order to restore the file to my SQL Server 2005 Express install, I need to specify the username and password they gave me. How exactly can I do that?

The script I'm using is:

code:

RESTORE DATABASE [DBname]
FROM DISK = 'C:\Users\Mike\Desktop\file.BAK'
WITH REPLACE,
MOVE 'DBname_Data' TO 'C:\Program Files\Microsoft SQL Server\data\DBname_Data.MDF',
MOVE 'DBname_Log' TO 'C:\Program Files\Microsoft SQL Server\data\DBname_Log.LDF'

I get this as output when I try that script:

code:

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Users\Mike\Desktop\H2kBom_db_200903082300.BAK'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I know the username and pass I need to use, I just can't figure out how to enter it!

# ? Mar 10, 2009 22:04

Bad Titty Puker: Nov 3, 2007; Soiled Meat

If you're running the script in SSMS, then you enter the username and password when you create the connection to the server.

# ? Mar 10, 2009 22:16

MrSaturn: Sep 8, 2004; Go ahead, laugh. They all laugh at first...

I am using SSMS. It won't let me enter that specific login and pass when I connect to the server. The server I'm using is my localhost, though. Do IU need to create a dummy user or something? How do I do that?

# ? Mar 10, 2009 22:20

SLOSifl: Aug 10, 2002

It looks like the problem is SQL Server can't open the backup file. Set the permissions for YourComputerName\NetworkService or Everyone to allow read or full access for that .bak file.

# ? Mar 10, 2009 22:27

MrSaturn: Sep 8, 2004; Go ahead, laugh. They all laugh at first...

I'm not sure what exactly you mean. Are you talking about windows permissions? The file does have full read and write access in windows, as far as I can tell.

# ? Mar 10, 2009 22:39

SLOSifl: Aug 10, 2002

Apparently it doesn't.

Give SYSTEM full permissions to the file, or Network Service, depending. Neither are covered by "Users".

# ? Mar 10, 2009 22:45

MrSaturn: Sep 8, 2004; Go ahead, laugh. They all laugh at first...

Turns out you were wrong, but you were right. It was trying to tell me that I didn't have permissions to the place where the DB was being restored TO. A win7 Quirk I'm sure.

# ? Mar 10, 2009 22:50

SLOSifl: Aug 10, 2002

Ah, that makes sense since you don't appear to be using the default SQL data folder (which is fine). I just assumed you were restoring to a folder SQL had access to.

# ? Mar 10, 2009 22:54

Civil: Apr 21, 2003; Do you see this? This means "Have a nice day".

I'm having problems showing records based on a character not being present. (mysql) Example:

code:

Company          Email

Smith Co         [email]smith@smithco.net[/email]
Jones Co         NO EMAIL ADDRESS
Dynamite
J & J Bros       [email]jj@gmail.com[/email]
Simtrans         [email]sim@trans.org[/email]

I have a table of data and I want to create a list of both the available email addresses, then a "deficiency list", showing companies that need one. I figure I can use the @ sign as a hook to find addressess. This is easy.

SELECT Company,Email from table where Email like '%@%'

Now, the tough part is getting the list of the omitted records. Is there a way to select contents of an entire table and subtract a search that I do know how to perform? Some records will be NULL, and others might just be a space, while others are just words that wouldn't have an @ sign. Any clues?

# ? Mar 11, 2009 00:10

geetee: Feb 2, 2004; >;[

What's wrong with:

code:

SELECT `Company`, `Email` FROM `table` WHERE `Email` NOT LIKE "%@%"

# ? Mar 11, 2009 00:14

Victor: Jun 18, 2004

If MySQL does ANSI nulls, then that won't return rows where `email` is null.

# ? Mar 11, 2009 01:09

geetee: Feb 2, 2004; >;[

Victor posted:

If MySQL does ANSI nulls, then that won't return rows where `email` is null.

Good catch -- I never knew that. Easy enough to add a test for at least.

# ? Mar 11, 2009 01:53

Just-In-Timeberlake: Aug 18, 2003

Edit: Actually found the answer to my question, this link has a nice writeup on how to handle external procedures in transactions.

Quick question about transactions and stored procedures, say I have a transaction like so (SQL 2005):

code:


BEGIN TRY
     BEGIN TRANSACTION
          /*CALL EXTERNAL STORED PROCEDURE*/
          EXEC EXTERN_PROC @var1, @var2;

     COMMIT TRANSACTION

END TRY

BEGIN CATCH
     /* LOG ERROR, ETC. */
     RAISERROR(@errmsg, @errsev, 1);

END CATCH

Now, in the stored procedure EXTERN_PROC(), do any errors "bubble up" to the calling stored procedure and rollback the transaction, or do I have to implement a TRY/CATCH block in EXTERN_PROC() and have a RAISERROR() in EXTERN_PROC()?

Just-In-Timeberlake fucked around with this message at 21:52 on Mar 11, 2009

# ? Mar 11, 2009 21:35

Vince McMahon: Dec 18, 2003

Nevett posted:

Hey, I need a bit of help with my dumb database project.
I'm little more than an SQL beginner, I'm keeping a database of movies, heres a simple version of what the table looks like:
code:
Movie                 Tag           TagValue
-----------------------------------------------
Wall-E                Genre         Animation
Wall-E                Genre         Adventure
Wall-E                Year          2008
Burn After Reading    Genre         Comedy
Burn After Reading    Genre         Crime
Burn After Reading    Year          2008
In the actual DB, Movie and Tag are foreign keys, TagValue is a Text type column.

Now, its easy enough to find movies that satisfy a single criteria:

select Movie from MovieTagValues where Tag="Year" and TagValue="2008";

But how do I go about finding movies that satisfy multiple criteria? Action movies from 2008 for example.

Can it be done in a single query or do I need to do more processing on the application side?

Also, if you're wondering about the database design itself, it has to handle arbitrary numbers of and content of tags, so all this data can't exist on a single row of a 'Movies' table unfortunately.

seconding the urgh about the table design, but have you tried something like this:

select movie from movietagvalues where movie in (select movie from movietagvalues where tag = 'year' and tagvalue = '2008') and tag = 'genre' and tagvalue='action' group by movie

i'm not great at writing sql without testing it though, so that might not work at all

# ? Mar 11, 2009 23:51

Victor: Jun 18, 2004

golgo13sf posted:

Now, in the stored procedure EXTERN_PROC(), do any errors "bubble up" to the calling stored procedure and rollback the transaction, or do I have to implement a TRY/CATCH block in EXTERN_PROC() and have a RAISERROR() in EXTERN_PROC()?

I'm actually not entirely sure. I've always had the feeling that the error handling/transaction rollback semantics of TSQL were ugly and different depending on who raised the error (whether it was the server or user code). Try/Catch helps some, but I honestly do not know if the transaction will be rolled back if your CATCH block is hit. I advise you to test this stuff. That's how I figure out a lot of things...

# ? Mar 12, 2009 11:57

SLOSifl: Aug 10, 2002

Victor posted:

Try/Catch helps some, but I honestly do not know if the transaction will be rolled back if your CATCH block is hit. I advise you to test this stuff. That's how I figure out a lot of things...

It won't be rolled back.

edit: Unless you rollback the tran in there. Here's a safer pattern:

code:

BEGIN TRAN
BEGIN TRY
    -- Query code
    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    PRINT ERROR_MESSAGE()
END CATCH

SLOSifl fucked around with this message at 16:45 on Mar 12, 2009

# ? Mar 12, 2009 16:40

antitroll: Jun 1, 2000; I hate the Midway

I need to do a find and replace on XML data stored as a image column in a vendor supplied table in a SQL Server 2005 database.

This appears to be the old SQL Server 2000 method of storing XML data, but I'm having problems finding information about it.

This appears to be close to what I need:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6014

# ? Mar 12, 2009 17:39

Just-In-Timeberlake: Aug 18, 2003

Victor posted:

I'm actually not entirely sure. I've always had the feeling that the error handling/transaction rollback semantics of TSQL were ugly and different depending on who raised the error (whether it was the server or user code). Try/Catch helps some, but I honestly do not know if the transaction will be rolled back if your CATCH block is hit. I advise you to test this stuff. That's how I figure out a lot of things...

If I'm understanding the info in the link I posted, you have to put a RAISERROR() in the CATCH for the calling procedure to know that an error occurred.

Basically (again from what I got from the article, haven't fully tested it yet) your external procedure might be called by itself instead of from another procedure, and conceivably might need to be transactional as well. So you have the external procedure check to see if @@TRANCOUNT > 0, if it is, then the external procedure has no business managing the transaction, just raise an error and let the calling procedure manage the transaction. If @@TRANCOUNT = 0, then start a transaction, since the external procedure was called by itself.

Again, haven't tested the code yet.

# ? Mar 13, 2009 01:43

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

I have a database which is fairly bad in terms of organization and need to clean it up. It basically contains information about Puerto Rico restaurants. As of right now all the information is kept in a single table, so there's a ton of data duplication.

I propose the following example, a restaurant called "La Ensalada" which has 6 locations appears 12 times on our restaurant table. 6 for each location and then 2 for each translation. There's some information that pertains to the restaurant chain itself (the kind of food it serves and the price, for example). Most of the information is part of the location, and some of this info needs to be translated since our site is bilingual.

My idea is splitting it into 3 tables:
Restaurant
RestaurantLocation
RestaurantLocationTranslation

The issue comes because I have some fields that need to be both in the Location and each LocationTranslation (think of it as a default and then a translated override) like the street address (I need a default in order to do geocoding). Leading to some, but not much, data duplication.

Keeping in mind that some of the translated columns need to be full text indexable, but not the ones that are duplicated (ie. Description doesn't need a default in RestaurantLocation, needs to be full-text but street address doesn't need full-text but needs a default).

The question is, am I doing this right?

Or should I maybe drop the RestaurantLocationTranslation table and instead use XML or something. This is in SQL Server 2005 (could switch to 2008).

# ? Mar 16, 2009 16:38

fez2: Jan 24, 2002

deimos posted:

The question is, am I doing this right?

Or should I maybe drop the RestaurantLocationTranslation table and instead use XML or something. This is in SQL Server 2005 (could switch to 2008).

I would go one step farther and make a "Language" table. (assuming geocode always works for English..)
LanguageID, LanguageText, GeoCode_bit

Restaurant can have universal stuff like the phone number, an id, the manager's name, relative price ($$$/$$$$).

Restaurant_Location_Language would then have
RestaurantID, LanguageID, Address, Description, MenuXML

That would clear out your duplication and you could give users a drop down pretty easily for each restaurant to see available languages.

# ? Mar 16, 2009 18:28

KarmaticStylee: Apr 21, 2007; Aaaaaughibbrgubugbugrguburgle!

"INSERT INTO allPosts (postURL) SELECT postURL FROM shortURLS"

trying to add postURL fields from the shortURLS table to the postURL fields in the allPosts table

postURL is a unique field in the allPosts table

I'm getting a:
"Duplicate entry 'http://www.exampleurl.com' for key 2"

any ideas?

# ? Mar 17, 2009 00:01

SLOSifl: Aug 10, 2002

Are you trying to update existing rows or is allPosts empty?

If you're just dumping a single column from a table into allPosts (which is empty), then you need DISTINCT after the SELECT.

# ? Mar 17, 2009 03:31

Wonderbread 2000: Oct 27, 2008

I'm trying to write a trigger (on SQL Server 2005) that should be stupidly simple, but I've never written a trigger before and can't figure it out.

What I want to do, is every time an INSERT or UPDATE statement is executed on a particular table, is to insert a copy of the row(s) affected into another table. Thing is, reading through MSDN I can't figure out any way to reference the affected rows or the statement that fired the trigger.

# ? Mar 17, 2009 16:09

SLOSifl: Aug 10, 2002

INSERT INTO WhateverTable ( Column1, Column2, Column3 )
SELECT [Column1], [Column2], [Column3] FROM INSERTED

# ? Mar 17, 2009 16:18

Wonderbread 2000: Oct 27, 2008

There's no UPDATED table, though. And will that work with a batch operation?

# ? Mar 17, 2009 16:21

SLOSifl: Aug 10, 2002

No, there's no UPDATED pseudo-table. It's always called INSERTED.

Yes, it'll work on a batch operation.

# ? Mar 17, 2009 16:24

Wonderbread 2000: Oct 27, 2008

Thanks, I didn't realize updates also used the INSERTED table.

# ? Mar 17, 2009 16:35

Subotai: Jan 24, 2004

I am using sql server 2008 and storing the datetime in one of my tables using the GETUTCDATE() function. Is there anyway to pull the time out of the database so that is not a literal string value but instead an integer value, like the number of seconds from the epoch, etc? After looking at all the date time functions on MSDN, I don't see a way to do this, but it seems like it would be something a lot of people would use.

# ? Mar 18, 2009 19:33

var1ety: Jul 26, 2004

Subotai posted:

I am using sql server 2008 and storing the datetime in one of my tables using the GETUTCDATE() function. Is there anyway to pull the time out of the database so that is not a literal string value but instead an integer value, like the number of seconds from the epoch, etc? After looking at all the date time functions on MSDN, I don't see a way to do this, but it seems like it would be something a lot of people would use.

You might need to compute it. As far as I know that is your only option in Oracle.

code:

with t as (SELECT localtimestamp - CAST('01-JAN-1970' AS TIMESTAMP WITH LOCAL TIME ZONE) AS delta from dual)
select extract(day from delta) * 24 * 60 * 60 +
       extract(hour from delta) * 60 * 60 + extract(minute from delta) * 60 +
       extract(second from delta) AS epoch_seconds
  from t

# ? Mar 18, 2009 19:52

Adbot: ADBOT LOVES YOU

# ? May 9, 2024 22:48

Aredna: Mar 17, 2007; Nap Ghost

Subotai posted:

I am using sql server 2008 and storing the datetime in one of my tables using the GETUTCDATE() function. Is there anyway to pull the time out of the database so that is not a literal string value but instead an integer value, like the number of seconds from the epoch, etc? After looking at all the date time functions on MSDN, I don't see a way to do this, but it seems like it would be something a lot of people would use.

You can just use the datediff function:

code:

select datediff(s,'1970-01-01 00:00:00','2009-11-25 12:34:56')

gives you 1259152496, which you can convert back with the dateadd function:

code:

select dateadd(s,1259152496,'1970-01-01')

You can change the start date to be any date you wish to treat as your epoch time if you don't want the unix standard, and change the end date to your field or function.

# ? Mar 18, 2009 20:43

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »