Home » SQL & PL/SQL » SQL & PL/SQL » SELECT INTO error (merged 3)
SELECT INTO error (merged 3) [message #434681] Thu, 10 December 2009 04:22 Go to next message
chris-uk-lad
Messages: 11
Registered: November 2009
Junior Member
Currently have a series of select into statements which work fine for example:

SELECT *
INTO R_Tab1
FROM TABLE1 WHERE ID = USER_ID AND Grouping = USER_Grouping AND Ref = 'NewLine';


This is placing the whole line of the record into a variable. Now the issue im having is a select into which contains an embedded select statement (finding the max date in a column). It seems to want 'FROM' to be used after the embedded statement, but that wouldnt work with the statement.

SELECT * INTO R_Tab1, (SELECT ID,MAX(DDate) AS MAXDATE 
FROM Table1 WHERE Grouping= USER_Grouping AND ID = USER_ID AND Table1.Ref = 'NewLine' GROUP BY ID) MAXRESULTS
WHERE Table1.ID = MAXRESULTS.ID
AND Table1.DDate = MAXRESULTS.MAXDATE
AND Table1.Ref= 'NewLine'


Much Appreciated
Re: SELECT INTO error (probably small overlook) [message #434686 is a reply to message #434681] Thu, 10 December 2009 04:29 Go to previous messageGo to next message
chris-uk-lad
Messages: 11
Registered: November 2009
Junior Member
apologies for treble post, looking for method to delete XD
Re: SELECT INTO error (merged 3) [message #434687 is a reply to message #434681] Thu, 10 December 2009 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I've tidied that up for you.

What you've got there is an attempt at an inline view.
And yes the outer query really does need a from clause. All Selects in a Select need a corresponding From.

Why would adding a From not work?
Re: SELECT INTO error (merged 3) [message #434693 is a reply to message #434681] Thu, 10 December 2009 04:45 Go to previous messageGo to next message
chris-uk-lad
Messages: 11
Registered: November 2009
Junior Member
thanks for the deletions Smile

ive tried inserting from but i keep getting an issue over maxresults correctly being recognised as not beng a table. If i place it anywhere else it says that its not found where expected (between the close of bracket and MAXRESULTS.

It seems th be thrown off by the SELECT INTO.

[Updated on: Thu, 10 December 2009 04:58]

Report message to a moderator

Re: SELECT INTO error (merged 3) [message #434699 is a reply to message #434681] Thu, 10 December 2009 05:03 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
this might be what you want:
SELECT * INTO R_Tab1
FROM Table1,
    (SELECT ID,MAX(DDate) AS MAXDATE 
     FROM Table1 
     WHERE Grouping= USER_Grouping 
     AND ID = USER_ID 
     AND Table1.Ref = 'NewLine' 
     GROUP BY ID) MAXRESULTS
WHERE Table1.ID = MAXRESULTS.ID
AND Table1.DDate = MAXRESULTS.MAXDATE
AND Table1.Ref= 'NewLine'
Re: SELECT INTO error (merged 3) [message #434701 is a reply to message #434699] Thu, 10 December 2009 05:11 Go to previous messageGo to next message
chris-uk-lad
Messages: 11
Registered: November 2009
Junior Member
cookiemonster wrote on Thu, 10 December 2009 05:03
this might be what you want:
SELECT * INTO R_Tab1
FROM Table1,
    (SELECT ID,MAX(DDate) AS MAXDATE 
     FROM Table1 
     WHERE Grouping= USER_Grouping 
     AND ID = USER_ID 
     AND Table1.Ref = 'NewLine' 
     GROUP BY ID) MAXRESULTS
WHERE Table1.ID = MAXRESULTS.ID
AND Table1.DDate = MAXRESULTS.MAXDATE
AND Table1.Ref= 'NewLine'


I get 'not enough values' in SELECT * INTO R_Tab1 FROM though wouldn't expect i need include all column names within the second select to balance it out.

[Updated on: Thu, 10 December 2009 05:14]

Report message to a moderator

Re: SELECT INTO error (merged 3) [message #434704 is a reply to message #434681] Thu, 10 December 2009 05:17 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm guessing r_tab1 is typed to table1, in which case yes you'll get that error as you are now selecting two more columns than exist in the table. Change the * to a full list of columns apart from the two that got from the inline view.
Re: SELECT INTO error (merged 3) [message #434707 is a reply to message #434704] Thu, 10 December 2009 05:25 Go to previous messageGo to next message
chris-uk-lad
Messages: 11
Registered: November 2009
Junior Member
cookiemonster wrote on Thu, 10 December 2009 05:17
I'm guessing r_tab1 is typed to table1, in which case yes you'll get that error as you are now selecting two more columns than exist in the table. Change the * to a full list of columns apart from the two that got from the inline view.


replace * with all columns, gave a 'too many values error'
replace * with just 1 column, 'too many values'
replace 1 column with * 'not enough values'

doesnt make sense :s
Re: SELECT INTO error (merged 3) [message #434710 is a reply to message #434707] Thu, 10 December 2009 05:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Rather than just changing thngs and hoping it will work, try selecting the list of columns that you need:
SELECT table1.* INTO R_Tab1
FROM Table1,
    (SELECT ID,MAX(DDate) AS MAXDATE 
     FROM Table1 
     WHERE Grouping= USER_Grouping 
     AND ID = USER_ID 
     AND Table1.Ref = 'NewLine' 
     GROUP BY ID) MAXRESULTS
WHERE Table1.ID = MAXRESULTS.ID
AND Table1.DDate = MAXRESULTS.MAXDATE
AND Table1.Ref= 'NewLine'


[typo]

[Updated on: Thu, 10 December 2009 05:28]

Report message to a moderator

Re: SELECT INTO error (merged 3) [message #434713 is a reply to message #434710] Thu, 10 December 2009 05:37 Go to previous message
chris-uk-lad
Messages: 11
Registered: November 2009
Junior Member
That appears to have cracked it! thank you both very much for your time.
Previous Topic: Bulk Collect
Next Topic: Using WITH Clause
Goto Forum:
  


Current Time: Sat Sep 24 19:51:54 CDT 2016

Total time taken to generate the page: 0.07395 seconds