Re: getting error in sql

On 5/19/2015 3:28 PM, Hoteltravelfundotcom wrote:

OK thanks we got a bit further, this sql has error: SQL0206 - Column or
global variable rklib.otord# not found. ( I tried without the lib name as
well in parttion by

Select * from
(
Select x.*,
row_number() over (partition by "rklib.otord#"
order by case ottrnc when '001' then 1 else 2 end
, ottrnd, ottrt
)
as RowN
from rklib.clspaytp x
) a
where a.RowN in (1,2)

OK so still reading Chuck's reply, here's what he said: ''[' and ']'
characters are not supported as delimiters' So it was the right thing
to drop the square brackets.

And then he said:

'If the column name is mixed-case, then the proper delimiters are the
'"' [i.e. the double-quote character].'

Remember, Chuck is very precise. Double quotes are only needed IF the
column name is mixed-case. Here is an example of a table with a mixed
case column name (via DSPFFD):

Field Level Information
Data Field Buffer Buffer Field Column
Field Type Length Length Position Usage Heading
OTORD00001 CHAR 10 10 1 Both "Otord#"
Alternative name . . . . . . . . . . . . :
"Otord#"
Allows the null value
Coded Character Set Identifier . . . . . : 37


Here are some SQL statements for you to examine:
create table buck.something ("Otord#" char(10))

select otord# from something
Column or global variable OTORD# not found.

select "otord#" from something
Column or global variable otord# not found.

select "Otord#" from something
SELECT statement run complete.

select something."Otord#" from something
SELECT statement run complete.

select "something.Otord#" from something
Column or global variable something.Otord# not found.

--
--buck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.