The following query was tried to returns a result set that shows the employees whose salary is one standard deviation greater than the average salary of their department.
So the below OVER was used for partitioning based on department value. For some reason , its not working in sybase ASE and throwing error .
please guide if this can be tried in different way or am i missing anything in above query . Thanks in advance.
...
The following query was tried to returns a result set that shows the employees whose salary is one standard deviation greater than the average salary of their department.
So the below OVER was used for partitioning based on department value. For some reason , its not working in sybase ASE and throwing error .
please guide if this can be tried in different way or am i missing anything in above query . ....
The syntax consisting of "over ... partition by ..." etc. belongs to a special class of database functions called "window functions" or "analytic functions".
These were defined in the ANSI/ISO SQL 2003 version and implemented by a few databases like Oracle, SQL Server, PostgreSQL, DB2 etc.
Sybase ASE 15.7 is not one of those databases; it does not support window functions, hence the error message.
My guess is that you got that syntax from a place dedicated to a database other than Sybase ASE 15.7
Of course, it can be done without window functions but it will be inefficient.
The first thing you need to keep in mind is that each aggregate function has its own window function counterpart.
So:
=> the "avg" aggregate function returns the average for each distinct set of group-by columns for all the rows satisfied by the filter criteria. The number of rows returned is typically less than that satisfied by the filter criteria.
=> the "avg" window function returns the average for the set of partition-by columns for all the rows satisfied by the filter criteria. The number of rows returned is always equal to that satisfied by the filter criteria.
Here's an example using Oracle 11g; I don't have Sybase ASE at the moment.
If the difference mentioned above is clear, then the alternative query should be clear as well.
You simply find out the average and std dev for each department id in a subquery and join it to the emp table on the department id.
That is shown below:
Window functions were introduced to remove the inefficiency in queries like the one above.
You are scanning the table once to determine the average and std dev per department in subquery x.
And then you are scanning the same table (emp) again in order to join the data in subquery x back to the table emp (with alias "e").
Imagine the amount of repetitive work if there are a huge number of subqueries like "x" above.
The same output using window functions is below.
Here, the table emp is scanned only once and the calculated average and std dev are provided for all the rows returned.
Hope that helps.
Converting the syntax back to Sybase ASE should not be a big problem - Oracle does not require the "cast" function and my columns were different than yours.
I leave it to you as an exercise.
Last edited by durden_tyler; 03-02-2017 at 11:42 AM..
Dear Team
Please provide suggestion on below query which is used in Sybase Adaptive Server Enterprise/15.7 (ASE).
Query takes more time > 30 Mins to 1 Hr
All required indexes are built
Can we have any efficient approach to get the data retrieval faster for below query.Any help... (0 Replies)
Dear Team
I am using Sybase Adaptive Server Enterprise/15.7 (ASE). Trying my luck on SOUNDEX function to get unique records
Though the command works for characters and provides unique code for similar outputs.
But when i try with Starting numbers ( Followed with Street address ) , soundex... (1 Reply)
Hi Team ,
I am new to Sybase Adaptive Server Enterprise/15.7 (ASE) and need some guidance to find the different values in serial format column.
SELECT DISTINCT SERIAL_FORMAT FROM PRODUCTS
It has values with below format which contains 12 digits hexadecimal characters with... (2 Replies)
HI Team
I am using Sybase ASE15.7 version.
Below is the sample column values .
http://abc.lifeline.airs.com/support/
https://xyzbre.lifeline.airs.com/video/
Would like to know how to
Trim http:// and https:// from above list of example
Remove characters after first /
Include only the... (3 Replies)
Helo Experts,
I have an issue in connecting to sybase from UNIX.
PFB, my code :
#!/bin/ksh
###############################################################################
#
# Filename: docflo_split.sh
#
# Description:docflo_split.sh WrapperScript splits the temporary file... (1 Reply)
Hi Gurus,
I am very new in Unix,
I have 1 script, in which I am truncating the table , then BCP the data in Sybase table, and then loading the data from sybase table to sybase table.
every thing is working fine, but the problem is with Error.
I made some hanges in my insert statement so... (3 Replies)
Hi Gurus,
I am very new in Unix,
I have 1 script, in which I am truncating the table , then BCP the data in Sybase table, and then loading the data from sybase table to sybase table.
every thing is working fine, but the problem is with Error.
I made some hanges in my insert statement so... (1 Reply)
i'm looking for an installer of Sybase ASE 11.9.2.6 for HP-UX. tried buying from the local distributor but he can only give me the latest version. can anyone point me to where i could possibly get it? (0 Replies)
To begin:
I use Linux
The Problem:
I need bcp functionality for scripts. Perl modules, such as Sybase:xfer, require ctlib which comes with Sybase Open Client. Talking with Sybase sales
reps is an exercise in futility and hate. They know absolutely nothing about their own products and will... (0 Replies)