Re: getting error in sql

On 19-May-2015 14:28 -0500, Hoteltravelfundotcom wrote:

<<SNIP>> this SQL has error:
SQL0206 - Column or global variable rklib.otord# not found.

Notice the replacement value for the variable name has not been folded to upper-case. Thus as stated in an earlier reply, the double-quote as delimiter will allow specification of mixed-case [or all lower, meaning, the identifier is not folded-to-upper].

I tried without the lib name as well in partition 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)

The qualified-name is incorrect as specified for at least three reasons: 1) the column name is presumably either OTORD# or otord#, but the correlation-name that was chosen to be "AS X" is neither omitted nor specified; given other identifiers used, the most likely is OTORD# 2) RKLIB is the schema-name, and upper-case apparently, because un-delimited in the FROM-clause, so the column qualifier RKLIB is not established anywhere in the subselect; the specified correlation-name is X. 3) the identifiers must be delimited separately; both the column-name as identifier and the correlation-name as identifier can be delimited, but separately. See the following for what would be valid\OK, not necessarily in that query, just generally exhibiting how [un]qualified and delimiting of identifiers function:

--- possible alternative ---- --- why not, or is OK? -------
partition by "rklib"."otord#" see (2)
partition by "x"."otord#" correlation-name wasn't delimited
partition by X."otord#" column unlikely lower-case
partition by "X"."OTORD#" OK, for an SQL code generator
partition by X."OTORD#" OK, but still why delimit?
partition by X.OTORD# OK... seems simple enough
partition by OTORD# OK... correlation optional

Either of the last two are just as well in lower-case, because they are folded to upper-case, for lack of being double-quote delimited; any undelimited identifier could have been specified as lower-case or mixed-case, and the identifier would have been folded to upper-case.