Home » SQL & PL/SQL » SQL & PL/SQL » Query Doubt
Query Doubt [message #193193] Fri, 15 September 2006 04:30 Go to next message
ananthaisin
Messages: 16
Registered: November 2005
Location: Chennai
Junior Member

Hi all,

I have a query to take some rows from the base table.the query goes like this

SELECT c1,
c2,
max(c3) (TITLE 'c3 is DATE'),
max(c4) (TITLE 'c4 is DATE')
FROM t1
WHERE (c1 LIKE '9840%' or c1 LIKE '9940%')
group by 1,2
QUALIFY rank() over (partition BY c1 ORDER BY max(c2) desc)=1;
)TEMP(c1,
c2,c3)
GROUP BY 1,2;

the above script is showing some error as
Syntax error, expected something like ';' between the beginning of the request and ')'.

pls clarify me the error and also can somebody pls explain me the script execution.This was givn to me by the BI team.Wht does Qualify and rank over does also why we give Temp().

Rgds
Anantha S
Re: Query Doubt [message #193202 is a reply to message #193193] Fri, 15 September 2006 04:52 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
What SQL flavour is that? It seems that this is not Oracle...

MHE
Re: Query Doubt [message #193205 is a reply to message #193202] Fri, 15 September 2006 04:58 Go to previous messageGo to next message
ananthaisin
Messages: 16
Registered: November 2005
Location: Chennai
Junior Member

Yes its sql and it was given to me by the BI team.it runs fine uptill some records are spooled and then it shows error
Statement 1 - Completed. 1403106 rows processed
Statement 2 - 3707: Syntax error, expected something like ';' between the beginning of the request and ')'.

This is exact error it shows when i run it in Queryman (NCR).I dont know wht lvl of scripting this is pls change topic to the place where it suits.just give the solution and how the script works

Rgds
Anantha S
Re: Query Doubt [message #193228 is a reply to message #193205] Fri, 15 September 2006 06:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ananthaisin wrote on Fri, 15 September 2006 11:58

This is exact error it shows when i run it in Queryman (NCR).
So it's not Oracle after all.

MHE
Re: Query Doubt [message #193232 is a reply to message #193228] Fri, 15 September 2006 06:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that the error might be caused by that spare ';' at the end of the PARTITION by clause.
Re: Query Doubt [message #193239 is a reply to message #193232] Fri, 15 September 2006 07:01 Go to previous messageGo to next message
ananthaisin
Messages: 16
Registered: November 2005
Location: Chennai
Junior Member

hi thnx for replying so fast,

If that ; is not given the query is not running at all.If it is given the query is running and data is spooled.but after the spooling of data when it goes to the second part of the query that is the qualify section error is thrown.

(And for the Kind info Queryman is a front end tool to execute oracle and Sql scripts it is similar to TOAD).ok if not too pls explain me wht does partition does also the role qualify with some example.

Thnx & rgds
Anantha S
Re: Query Doubt [message #193241 is a reply to message #193239] Fri, 15 September 2006 07:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'm sorry but I thought QueryMan was used to query a NCR Teradata data warehouse only. It seems I was wrong:
from the NCR Website

Teradata's Queryman is a Visual Basic application that retrieves data from various ODBC databases and stores it on your desktop. This information discovery/query tool enables you to combine the data retrieved from ODBC-based databases with desktop applications such as Excel to create consolidated roll ups and reports, or to analyze the merged data.
Anyway, the statement you have is specific for QueryMan I'm afraid. In Oracle, a semicolon - ; - is a SQL terminator: it indicates the end of an individual SQL statement. Each statement can only have on SQL terminator. You have two, that explains the reply you got from JRowbottom.

I hoped I never had to face that tool again Very Happy

MHE

[Updated on: Fri, 15 September 2006 07:08]

Report message to a moderator

Re: Query Doubt [message #193252 is a reply to message #193193] Fri, 15 September 2006 07:35 Go to previous message
ananthaisin
Messages: 16
Registered: November 2005
Location: Chennai
Junior Member

Oh thnx will check it out
Previous Topic: REF TABLE
Next Topic: to check the record before inserting
Goto Forum:
  


Current Time: Tue Dec 06 10:21:51 CST 2016

Total time taken to generate the page: 0.07109 seconds