Reuse derived columns in same SQL Select statement under SQL Server 2005
Our agency is in the process of migrating our Sybase
Adaptive Server Anywhere 7.0 databases to Microsoft SQL
Server 2005. We are currently using PowerBuilder 10.5. We
have several datawindows that reuse SQL computed fields
(derived columns) in the same SQL Select statement. Here is
(CASE WHEN coalesce(s.recommended_verbal,'N') <>
coalesce(s.final_verbal,'N') then 1 else 0 end) as c_verbal,
(CASE WHEN coalesce(s.recomme...General SQL statement?
Once I have a table in SQLAnywhere 5.5.04, is there any way to generate
a SQL statement that would have created it? Someone else needs to
recreate the structure of my table and I'd like to send them a SQL
statement that they could use to do so.
One way to do it is to (for example, you need foo_table TABLE):
1) unload database (can be done from Sybase Central) into foo.sql
2) copy "CREATE TABLE foo_table" command where you need it...
Rick Charnes wrote:
> Once I have a table in SQLAnywhere 5.5.04, is there any way to generate
> a SQL stateme...Inserting BLOB Type column data with other columns data(normal Text)
In below code, SQL Statement is used to Insert BLOB Type data using Serialization. It is required to insert other (normal text) with BLOB Type data as well. Like to get below SQL statement amended so that all the columns(ID,name, blob) can be inserted.
Just now below code working fine Inserting BLOB Type data.
I think it is not stright forward way to insert all the columns(normal text) with BLOB?
1 protected void button2_Click(object sender, EventArgs e)
3 //Connection string
4 string oradb = datasource= mysource;userid="xyz" password="passwo...SQL statement and If statement
I've problem, it's in sqlcommand it's in "where" condition, which if it's exist in sql the "ELSE" not work, and if the condition not in sqlcommand the "ELSE" appear, could someone help
selectcommand.CommandText = "SELECT Weekno FROM Weeks where Weekno='" + Weekno.Text + "'"
if i delete the condition the " ELSE " appear. otherwise it's not appear
whole code Dim myconnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\football.mdf;Integrated S...Parsing columns from a sql statement
Hi, How I could get the name of the columns from a string
variable that has a sql statement?
"select this, that, (to_date('02-02-04','mm-dd-yy')) from
I want to obtain the column names "this", "that" and
I would suggest to use the POS and MID functions.
> Hi, How I could get the name of the columns from a string
> variable that has a sql statement?
> "select this, that, (to_date('02-02-04','mm-dd-yy')) from
>...datawindow not seeing aliased columns in SQL statement
Below is a stripped down version of our SQL statement. The problem is the 2
columns from aliased tables:
"ST_FORGN_CNTRY_CD_TBL_A"."ABBR" and "ST_FORGN_CNTRY_CD_TBL_B"."ABBR". The
SQL works absolutely fine when I execute within Oracle's SQL*Plus, so I know
the SQL isn't the problem. The problem is that the datawindow does not seem
to be distinguishing between the 2 columns (it replaces the table alias with
the table's real name). By doing this, we are not getting the real value
for one of the 2 columns. The columns we can select fr...Help with totalling columns in SQL select statement
Maybe its a friday, but I can't figure out how to total the returned results so I just get a sum of the 'Items' and 'Total'. I thought it was Compute but that did not work!
I'm pulling data from 2 tables, Order and Order_Item and what Im trying to achieve is to total up how many items were sold and the total. Each one of the rows below are for each order.
I must be doing something wrong, can anyone help?
SELECT sum(Order_Item.Quantity) as Items,
sum(Order_Item.Price*Order_Item.Quantity)-[Order].Discount as Total
JOIN Order_Item ON Order_Item.[OrderID]...dynamically adding columns to datagrid from sql statement
hi,i have seen some of the datagrid samples in internet. In most of the cases the datagrid is bind to some table. But if i want to display the output of an sql query(contains select statement with n number of columns) in Datagrid, i can simply use AutogenerateColumns=true and populate the datagrid without having any bound columns. That is fine.If i want to add some more extra columns like a checkbox column and an edit link button column i can do it using property builder. But,the problem is how can i order the columns in datagrid.for example: 1st col: checkboxes &n...SQL Logging SQL Statement
Hi at All!!
I use ASA 7 so
I'd like to know if there is a way to logging the sql
that database server dbsrv7, receive from clients ?
I need this because a client that use odbc connection after
performing an insert into a table, the client receive the
Message: "SQL truncate". I need to know the field on the
table that generate this warning!!
Thanks in advance
The tool that does this is called "request-level logging" and in V7 you
have to enable it from the command line when you start the server, using
the -zr (and optionally -zo) c...If Statement combined with SQL statement.
This is going to sound like quite a strange question. Basically what I am trying to do is use Membership.GetUser().UserName to get the current user who is logged in and I want to see if that user exists in a table on my database before adding some data to the labels. I figured it should go in the page load part. The name of the table is userteams.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If Membership.GetUser().UserName exists in Userteams Then goallie.Text = Select player FROM Userteams Where Username = Membership.getUser().Use...update multiple columns in one sql statements
I am almost sure I can update variables columns in one select/case type
statement, but having problems working out the syntax.
I have a table with transactions - with tran types as the key.
in this example, types = A,B,C ,D.
in this first example I am updating the sum of QTY to value t_A based on
tran types =A.
can I perform sub query/case to update with the same where clause
but for types B,C and D?? I also have to insert for specific lot numbers each sum values.
Create table #t_reconcile(
t_lot_number int not null,
t_A ...Adding an IF Statement to the SQL Statement
I have the following tables and SQL statment which works out a league table for me from the given results.
I am now trying to add a bit more to this:
If the Walkover column = 1 then that row will be a home walkover meaning that the score will be 0(Home Score) - 0(Away Score) but the home team will recieve 3 points, and away team recieves 0 points, win column will be 1 and the played colum will be 1 as well.If the Walkover column = 2 then that row will be a home walkover meaning that the score will be 0(Home Score) - 0(Away Score) but the away team will recieve 3 po...Viewing a SPIDs last SQL statement using sqltext()...though sometimes only partial SQL statements are shown
The dbcc sqltext() command seems to allow you to view the last SQL statement
issued by an active SPID on the server. However I've noticed that sometimes
only part of the SQL statement is returned - does anyone know why this might
be, and if so whether it's due to a server configuration.
Anyway, here's my notes on how to view the last SQL statement processed:
** How to see the last SQL a SPID has processed
-- dbcc traceon(3604) Directs trace output to the client, rather than to the
1> dbcc traceon(3604)
DBCC execution completed. If DBCC pr...how to autogenerate value in a column using Update sql statement.
have value like 0809000001 and would like to autoincrement this field starting with this value.Without reading each individual record. The value should be (last no +1) for each row.The column datatype is varchar.
Seems like a perfect scenario for an int (or bigint for large numbers) identity field instead. You can seed the identity field to start at 809000001.If you really need the 0 at the beginning you could always accomadate this when selecting data by CASTing identity column to a varchar and padding with extra 0's as needed.An alternative would be to use a trigger on the tabl...