FAQ Database Discussion Community


Join on table that does not exist

sql,database,join,sybase,sybase-ase
Is it possible to join tables, when one or more tables does not even exist? Consider this use case: You are using some system that has a certain DB scheme out of the box, but allows you to create your own custom tables as well. It is possible to run...

pass a variable to predefined function sybase/sql

sql,sybase,sybase-iq
i'm new in sql language, and i would appreciate your help. I want to pass a variable (i.e. a column) to a predefined function like "convert" or "hextobigint". select hextobigint('14c9d0742dc') returns 1428563641052 meanwhile the below code doesn't gets executed () begin declare @st varchar(16); set @st=dc.table.START_TIME; select hextobigint('dc.table.START_TIME') FROM dc.table;...

SQL Procedure (Sybase Advantage Database Server)

sql,sybase
I have table contain below data: EMPCODE PAYCODE AMOUNT ------------------------ 001 A 100 001 B 200 002 A 120 002 C 80 003 B 50 003 D 20 All PAYCODE in table at the moment are A, B, C, D. However, other EMPCODE with other new PAYCODE such as E...

import sybase data to sql server when any change make in table

sql-server,sybase
I have a Sybase database which i want to migrate to SQL SERVER 2008R2. I have done this, but i got a new requirement. When any data is modified or new insert in table in Sybase that data only migrate from Sybase to Sql Server. The one table data is...

Netezza correlated query

sybase,netezza,correlated
I am migrating from Sybase to Netezza and Netezza does not support this type of query. Can someone show me how to rewrite it? UPDATE table1 t1 SET t1.col1=t2.col1 FROM table2 t2 WHERE t1.col2=t2.col2 AND t2.col3=(SELECT MAX(t3.col3) FROM table2 t3 WHERE t3.col2=t1.col2); ...

hierarchy display Sybase table data

sql,table,sybase
EID PID Name 1 NULL A 2 1 B 3 2 C 4 3 D 5 1 E 6 1 F 7 1 G 8 6 H The above info shows actual data of a table, i want to display the data in that table as below . i.e Hierarchy...

add a getdate() default value while adding a column

sybase,sybase-ase
Useally if I want to add default value , I use this way ALTER TABLE tab1 ADD coll datetime DEFAULT '11-01-15' NOT NULL however I want to add default value as the getdate(). so If use the below I got the below error ALTER TABLE tab1 ADD coll datetime DEFAULT...

sybase wired output after case when

sql,sybase
After this select to a sybase DB SELECT CASE WHEN 'a' = 'b' THEN CONVERT(datetime,getdate(),23) WHEN 'a' <> 'b' THEN '' END the output is always a date (tried in Squirrel and Aqua Data Studio). This is always null SELECT CASE WHEN 'a' = 'b' THEN CONVERT(datetime,getdate(),23) END How can...

Remove duplicate rows between two different columns

sql,sybase
I'm having an issue removing duplicates between two different columns. Here's my current select query: SELECT e.id AS EventId, rt.player_nbr AS PlayerId, rt.partner_nbr AS PartnerId FROM eventTable e INNER JOIN resultsTable rt ON e.id = rt.id Here are the results: EventId | PlayerId | PartnerId 1049 | 20652 | 80458...

Microsoft SQL @@cpu_busy replacement for CPU saturation stat

sql,sql-server,sybase
We have a process that was originally written for Sybase that looks at the @@cpu_busy variables to detect the cpu load on the RDMS. The application will be running against Microsoft SQL. curCPULoadPercent=100*(@@cpu_busy - prevCpuBusy)/((@@cpu_busy - prevCpuBusy)+(@@io_busy - prevIOBusy)+(@@idle - prevIdle)) If any of the above deltas are negative, the...

Get info about foreign key on delete action

sql,foreign-keys,constraints,sybase,sybase-ase
I have a Sybase database with two tables. One table refer to another with a foreign key. I need to know - is this foreign declared with any on cascade option? I have already tried: EXEC sp_helpconstraint <table_with_constraint> but this doesn't return any on cascade information. Is there any way...

What is the locking schema of sybase used for?

database,sybase
Now I am using Sybase, when I create table, I should give a locking schema for it. I know other DB can choose lock by DBMS. Now I have two questions for this: When I give a all page lock for locking schema to a table, but when I delete...

Bind font symbol from database in XAML

wpf,xaml,sybase
I'm having buttons in a WPF project that have Segoe UI Symbols as their content. This works when I set the symbol in XAML directly or when I define the symbol in C# and bind it in the XAML. When I get the symbol from the database and bind it...

Find if the local temporary table exists in sql anywhere and use it

sql,sybase,sqlanywhere
I want to write a procedure in SQL anywhere which can check if a local temporary table exists and if it does use it. I do not want to drop the table. I have already found a way to drop local temporary table which is: DROP TABLE IF EXISTS t;...

How to remove/rename a duplicate column in SQL (not duplicate rows)

sql,sql-server,sybase
When attempting to do an OPENQUERY from Sybase to Microsoft SQL I run into an error: Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "PatientID" is a duplicate. The query I have built joins 2 tables based on similar admissionID and...

Avoid errors when attempting to convert to datetime

sql,tsql,sybase
Is there a way in SQL to ignore records for which a conversion failed. Setting some default value would also work. > SELECT convert(datetime, foo_str) > FROM foo_tbl ------------------------ Aug 23 2013 00:00:00.000 Aug 17 2013 00:00:00.000 May 06 2015 00:00:00.000 Aug 13 2013 00:00:00.000 Aug 09 2013 00:00:00.000 Sep...

Python-Sybase on Windows 7 Python 2.7 32bit

python,python-2.7,sybase
I'm trying to build python-sybase on windows with very limited success. I have followed the instructions on the sybase module homepage but still experience errors. I have tried both of the following approaches: Download and unzip python-sybase (python-sybase-0.40pre2) No MS Visual Studio Install Microsoft Visual C++ Compiler for Python 2.7...

Datawindow query is not respecting ORDER BY clause

sql,sql-order-by,sybase,powerbuilder,datawindow
I'm developing a form in order to have an order form for procurement purposes. I've a Datawindow as a report object inside my main form. This datawindow (sub-report) has a select to retrieve the orderlines for that order form. Everything is working fine except the ORDER BY clause. I need...

SAP equivalent of IsMSShipped (ie, is a system object)

sybase,sybase-ase
I want to know how to determine if a object (eg, a view) is a system object or a user object. Is this possible? I'm using SAP ASE 16.0. Thanks...

Can an updated with nested select be considered atomic in Sybase?

sql,sybase,atomicity
I am trying something like this: set rowcount 10 //fetch only 10 row Update tableX set [email protected] where id in (select id from tableX where x=0) basically mark 10 record as booked by supplying a batchId. So my question is if this proc is executed in parallel then can I...

Using ResultSetMetaData with same column name in 2 tables [duplicate]

java,sql,database,sybase,resultset
This question already has an answer here: JDBC ResultSet get columns with table alias 6 answers I want to get column names from 2 tables and then their value using ResultSetMetaData and ResultSet. Let's say table myTable has columns x, y and z. I do Select * from table...

How to see Table columns in sorted order in Sybase

sql,database,sybase,sybase-ase
sp_help lists the table columns but they are not sorted as per their names (perhaps as per the actual order in which they are present in table i guess). For many columns in a table it gets difficult to find a column name , so is there any way i...

how to find tables used in a view on sybase

sql,view,sybase,sybase-ase
Am relatively new to sybase ASE and trying to find the tables used in a view kindly help me

SQL Conditional sum and grouping

sql,grouping,sybase,conditional-statements
I have a query that's trying to sum up a patient's length of stay at a hospital. Here is an example of the data | Patient | Admission_ID | Admission_Event_ID | Admission_Event_Type | Start Date | End Date | Duration | Linked_Admission | | P0001 | ADM0001 | AE1 |...

Sybase and jdbc- Proc returns select statement - records not able to get

java,stored-procedures,jdbc,sybase
I'm executing a stored procedure from jdbc. The procedure returns the contents of a table 'out_table' which consists of 2 columns key and value, via select query. I'm using String query = "{? = call my_proc}"; try { stmt = conn.prepareCall(query); stmt.registerOutParameter(1, Types.JAVA_OBJECT); boolean results = stmt.execute(); while (results) {...

Where is PHP's sybase_query Implemented?

php,sybase,php-extension
I want to look at the implementation of sybase_query (http://php.net/manual/en/function.sybase-query.php) in PHP. How can I do that? I went here https://github.com/php/php-src, and searched for sybase_query, but nothing came up. Can you help me find the source to the sybase extension?

Data type in sybase function replace() instead of value

sql,function,sybase,netezza
Hi I have a Sybase sql query where function replace() is used like this replace(Table1.Col1,char(10),'') When transfering the query to Netezza There is a problem at the beginning of char(10). I dont understand what was the char(10) for ? From Sybase manuals : REPLACE ( original-string, search-string, replace-string ) there...

Master Device Is Lost and Valid Dump Does Not Exist (SYBASE ASE 15.0)

database,tsql,sybase,sybase-ase
I have the following issue , the physical device of the master db is damaged and a valid dump of master doesn´t exist , I´m using ASE 15.0 version . How can a recover my master db and my users and logins ?...

Updating rows in table with group of data from the same table

sql,aggregate,sybase
I have this table below: Indicator Year Month FirstDayPeriod NrOfThings NmbrOfThingsPastYear 99 2013 1 Jan 1 2013 10858 99 2013 2 Feb 1 2013 8264 99 2013 3 Mar 1 2013 9716 99 2013 4 Apr 1 2013 8549 99 2013 5 May 1 2013 8144 99 2013 6 Jun...

Sybase Sql query not returning error

sql,null,sybase,isnull
While doing a query with multiples tables and multiples variables in a Sybase SQL query, the results weren't what i was expecting. My table is not complete and is not supposed to be, most of it can recieve NULL as a value (I know NULL is not a value but...

How to alter Image column on Sybase to NOT NULL

sybase
On a Sybase ASE 15.7 database I'm trying to modify a column type Image from NULL to NOT NULL (I'm using SQSH so ; is a valid terminator): create table LOB_TEST (XML image NULL); alter table LOB_TEST modify XML image NOT NULL; Error message: Msg 13907, Level 16, State 1...

Sybase SQL anywhere 11 Grant/Revoke on column views

sql,views,sybase,grant,sqlanywhere
My objective is to hide some column values to specific user or group on my views. I managed to give access to views, tables or table column with GRANT SELECT [(MY COLUMN)] ON TABLE/VIEW TO MY_USER for this query I have this error : GRANT of column permission on view...

Calculate working shift giving current datetime in sybase

sql,sybase,sybase-ase
I need a sql script in sybase or at least ansi sql that giving the current datetime, i can get the current working shift. Working shift '1' begins at 08:00 and ends at 15:30 Working shift '2' begins at 15:30 and ends at 23:00 Working shift '3' begins at 23:00...

How to tell if a view has been created WITH CHECK OPTION in Sybase ASE

sql,sybase,sybase-ase
Is it possible to determine whether an existing view in Sybase ASE (v15.7) has the WITH CHECK OPTION clause? I have created 2 views that are identical except for that clause, and the entries in all system tables (e.g. sysobjects, syscomments, sysprotects etc.) appear to be identical for both views....

How to produce an XML output file from a stored procedure in Sybase?

xml,stored-procedures,sybase
I have an event that calls a stored procedure every 10 minutes.I want that SP creates a XML file with the data of a table of my database. Is that possible?If so,how can I create that? Thanks in advance

Sybase server linked in SSMS using Concatenate in openquery

concatenation,sybase,ssms,openquery
I have managed to link a Sybase server to SSMS and am able to query it using openquery but I get an error every time I try to concatenate columns. ie: Select * from openquery(SybaseServer, 'select top 1000 first_name + middle_name + Last_name as "full_name", * from dbname..tablename') I realize...

how to return one result set from procedure

sql,sql-server,sybase
I am trying to make a procedure return randomly selected rows using and old version of Sybase. This is what I have come up with until now. It seems that each row is returned in its own result set. How can I "group" all selected rows in a single result...

How to execute SQL queries from text files

sql,database,sybase,aquafold
I am using Aqua Data Studio 7.0.39 for my Database Stuff. I have a 20 SQL files(all contains sql statements, obviously). I want to execute all rather than copy-paste contains for each. Is there any way in Aqua to do such things. Note: I am using Sybase Thank you !!...

how to get sybase table column name and its datatype and order by

database,sybase
There are multiple table in my sybase database i want to know the column name and datatype of a given table like (myOrder table). How can i do this. Below script i found on stackoverflow From a Sybase Database, how I can get table description ( field names and types)?...

Selecting Data based on a specific condition ,which is the optimal solution using sql statement or stored procedure using cursor in sybase

sql,sql-server,sybase,sybase-ase
SELECT distinct(a.acct_num) FROM customer_acct a, customer_acct_history b LIKE "%000%" WHERE a.acct_num *= b.acct_num AND acct_type='C' This Query would return a list of acct numbers from the tables. Based on this output . I am planning to run another Query and select various data from other tables . The second query...