Insert with Select Inner Join [message #402096] |
Thu, 07 May 2009 10:18  |
rboggess
Messages: 15 Registered: May 2009 Location: Pittsburgh
|
Junior Member |
|
|
Can anyone tell me what's wrong with this SQL statement?
INSERT INTO slblnup
2 ( coilid, fcenum, lnupnum, location, timstp
3 )
4 SELECT ProdData.CoilId,
5 CAST(ProdData.FceNum AS NUMBER),
6 HSMFMC.SLBLNUPSEQ.NEXTVAL,
7 'L',
8 CURRENT_TIMESTAMP
9 FROM System.ProdData
10 INNER JOIN System.tmpslb
11 ON ProdData.CoilId = tmpslb.CoilId;
INSERT INTO slblnup
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
The Select statement, outside of the Insert, generates these values:
SELECT ProdData.CoilId,
2 CAST(ProdData.FceNum AS NUMBER),
3 HSMFMC.SLBLNUPSEQ.NEXTVAL,
4 'L',
5 CURRENT_TIMESTAMP
6 FROM System.ProdData
7 INNER JOIN System.tmpslb
8 ON ProdData.CoilId = tmpslb.CoilId;
COILID CAST(PRODDATA.FCENUMASNUMBER) NEXTVAL '
------- ----------------------------- ---------- -
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
D36806 4 207 L
07-MAY-09 09.58.29.938630 AM -04:00
D36805 4 208 L
07-MAY-09 09.58.29.938630 AM -04:00
D36804 4 209 L
07-MAY-09 09.58.29.938630 AM -04:00
And I can enter all of these values individually, when I'm not counting to ten backwards and threatening this FRED with a 32oz cup of salt-water.
|
|
|
|
|
|
|
|
Re: Insert with Select Inner Join [message #402102 is a reply to message #402098] |
Thu, 07 May 2009 11:00   |
rboggess
Messages: 15 Registered: May 2009 Location: Pittsburgh
|
Junior Member |
|
|
Ai! I'm goin ta hell fer sure now!
Seriously, though, I'm in development/discovery mode right now. In the end, what schema our customer uses on their box is beyond my control. (In fact, what schema the lead uses on our box is beyond my control.) I'm just the guy trying to write stored procedures to wire up the HMI in vb.net.
Got it to work on the single case. It was dieing because timstp is a Date field, and I was trying to insert CURRENT_TIMESTAMP, instead of SYSDATE.
If I can get the more general case to work, great, if not, I can do a followup Update. Thanks for taking the time to look. I really do appreciate it.
|
|
|
Re: Insert with Select Inner Join [message #402103 is a reply to message #402096] |
Thu, 07 May 2009 11:06   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
While you probably don't have much control over the schemas using system remains a very bad idea.
Oracle won't be impressed if you need support off of them and you've been using it.
|
|
|
Re: Insert with Select Inner Join [message #402120 is a reply to message #402103] |
Thu, 07 May 2009 12:06   |
rboggess
Messages: 15 Registered: May 2009 Location: Pittsburgh
|
Junior Member |
|
|
cookiemonster wrote on Thu, 07 May 2009 12:06 | While you probably don't have much control over the schemas using system remains a very bad idea.
Oracle won't be impressed if you need support off of them and you've been using it.
|
I know. It's just that I can't fix that. And neither I nor anyone else is likely to contact oracle for support on this (our internal) server. It's only even powered up to test code against. And it would be next to nothing to wipe out the whole oracle installation and reinstall. (Except that VMS requires a bit more attention during the install than we have short of a dire emergency.)
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
That's the version I'm currently testing against. But our customer is running 8. Or maybe 7. You get the idea. And the problem is, I'm expected to make it work. So, patching our system, doesn't mean I can patch theirs.
And even if I could patch theirs, it doesn't mean I can patch the next customer's. From our perspective, it's better for us to build against the lowest common denominator we can to ensure functionality. (A real buzzkill.)
So, again, thank you one and all for even taking the time to read the post and bother to reply. I do appreciate it, but I think I have my answer -- use SYSDATE or CURRENT_DATE for a Date field.
|
|
|
Re: Insert with Select Inner Join [message #402122 is a reply to message #402120] |
Thu, 07 May 2009 12:16  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
That's the version I'm currently testing against.
|
Which is the buggiest version Oracle ever released.
Quote: | But our customer is running 8. Or maybe 7.
|
So whatever you do you will never validate the sql for your customer. Especially that ansi syntax is not supported in these version.
Quote: | And even if I could patch theirs, it doesn't mean I can patch the next customer's. From our perspective, it's better for us to build against the lowest common denominator we can to ensure functionality. (A real buzzkill.)
|
Go to a supported version and tell your customer to also do it.
Regards
Michel
[Updated on: Thu, 07 May 2009 12:23] Report message to a moderator
|
|
|