Reuse derived columns in same SQL Select statement under SQL Server 2005

Hello,

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
an example:
select
  institution_code,
  incident_number,
  sequence,
  final_review_date,
  ts_status_code,
  idoc_number,
  (CASE WHEN coalesce(s.recommended_verbal,'N') <>
coalesce(s.final_verbal,'N') then 1 else 0 end) as c_verbal,
  (CASE WHEN coalesce(s.recommended_grade,' ') <>
coalesce(s.final_grade,' ') then 1 else 0 end) as c_grade,
  (CASE WHEN coalesce(s.recommended_grade_days,0) <>
coalesce(s.final_grade_days,0) then 1 else 0 end) as
c_gradedays,
  (CASE WHEN coalesce(s.recommended_segregation_days,0) <>
coalesce(s.final_segregation_days,0) then 1 else 0 end) as
c_seg,
  (CASE WHEN coalesce(s.recommended_gcc_loss,0) <>
coalesce(s.final_gcc_loss,0) then 1 else 0 end) as c_gcc,
  (CASE WHEN coalesce(s.recommended_job_transfer,'N') <>
coalesce(s.final_job_transfer,'N') then 1 else 0 end) as
c_job,
  (CASE WHEN coalesce(s.recommended_housing_change,'N') <>
coalesce(s.final_housing_change,'N') then 1 else 0 end) as
c_house,
  (CASE WHEN coalesce(s.recommended_restitution_amount,0) <>
coalesce(s.final_restitution_amount,0) then 1 else 0 end) as
c_rest,
  (CASE WHEN coalesce(s.recommended_restitution_paid_to,' ')
<> coalesce(s.final_restitution_paid_to,' ') then 1 else 0
end) as c_restpaye,
  (CASE WHEN coalesce(s.recommended_transfer_reason_id,' ')
<> coalesce(s.final_transfer_reason_id,' ') then 1 else 0
end) as c_trans,
  (CASE WHEN coalesce(s.recommended_other,' ') <>
coalesce(s.final_other,' ') then 1 else 0 end) as c_other,
  (CASE WHEN c_verbal =3d 0 and c_grade =3d 0 and
c_gradedays =3d 0 and c_seg =3d 0 and c_seg =3d 0 and c_gcc
=3d 0 and c_job =3d 0 and c_house =3d 0 and c_rest =3d 0 and
c_restpaye =3d 0 and c_trans =3d 0 and c_other =3d 0 then 0
else 1 end) as c_overall
from dbo.summary as s
where institution_code =3d 'WIL'
and final_review_date >=3d 2004-04-16 and final_review_date
<=3d 2004-05-12;

In the above example c_verbal, c_grade, c_gradedays, c_seq,
etc. are referred to later in the SQL Select statement. 
This worked under Sybase Adaptive Server Anywhere but does
not in MS SQL Server 2005.  An error is given for each
computed variable that is reused.  For instance, =91Msg 207,
Level 16, State 1, Line 1   Invalid column name 'c_verbal'.

Does someone have an idea of an easy work-around solution
for this?  In the above example, we know we can replace the
variable names with the same expression that we used to
originally set its value.

Thanks.
Linda Butler
0
L
10/22/2008 3:12:45 PM
sybase.powerbuilder.database 9855 articles. 1 followers. Follow

1 Replies
899 Views

Similar Articles

[PageSpeed] 3

Hi Linda;

 MS's SS TransAct-SQL was originally a port of the Sybase one (Sybase 
actually did the work initially for MS). However, in later releases MS has 
decided to enhance their TransAct-SQL the MS way vs ANSI standards. So I 
would suspect that some of your SQL will have to be rewritten to adhere to 
SS's variations.

FWIW: I had to do that for another project last year that wanted to migrate 
to SS2005. I am also looking at the same problem this year with SS2008. Even 
changing MS DB Drivers can affect SS interaction for the same SQL statement. 
:-(

-- 
Regards ... Chris
ISUG - NA RUG Director
http://chrispollach.pbdjmagazine.com


<L. Butler> wrote in message news:48ff42ed.73a2.1681692777@sybase.com...
Hello,

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
an example:
select
  institution_code,
  incident_number,
  sequence,
  final_review_date,
  ts_status_code,
  idoc_number,
  (CASE WHEN coalesce(s.recommended_verbal,'N') <>
coalesce(s.final_verbal,'N') then 1 else 0 end) as c_verbal,
  (CASE WHEN coalesce(s.recommended_grade,' ') <>
coalesce(s.final_grade,' ') then 1 else 0 end) as c_grade,
  (CASE WHEN coalesce(s.recommended_grade_days,0) <>
coalesce(s.final_grade_days,0) then 1 else 0 end) as
c_gradedays,
  (CASE WHEN coalesce(s.recommended_segregation_days,0) <>
coalesce(s.final_segregation_days,0) then 1 else 0 end) as
c_seg,
  (CASE WHEN coalesce(s.recommended_gcc_loss,0) <>
coalesce(s.final_gcc_loss,0) then 1 else 0 end) as c_gcc,
  (CASE WHEN coalesce(s.recommended_job_transfer,'N') <>
coalesce(s.final_job_transfer,'N') then 1 else 0 end) as
c_job,
  (CASE WHEN coalesce(s.recommended_housing_change,'N') <>
coalesce(s.final_housing_change,'N') then 1 else 0 end) as
c_house,
  (CASE WHEN coalesce(s.recommended_restitution_amount,0) <>
coalesce(s.final_restitution_amount,0) then 1 else 0 end) as
c_rest,
  (CASE WHEN coalesce(s.recommended_restitution_paid_to,' ')
<> coalesce(s.final_restitution_paid_to,' ') then 1 else 0
end) as c_restpaye,
  (CASE WHEN coalesce(s.recommended_transfer_reason_id,' ')
<> coalesce(s.final_transfer_reason_id,' ') then 1 else 0
end) as c_trans,
  (CASE WHEN coalesce(s.recommended_other,' ') <>
coalesce(s.final_other,' ') then 1 else 0 end) as c_other,
  (CASE WHEN c_verbal = 0 and c_grade = 0 and
c_gradedays = 0 and c_seg = 0 and c_seg = 0 and c_gcc
= 0 and c_job = 0 and c_house = 0 and c_rest = 0 and
c_restpaye = 0 and c_trans = 0 and c_other = 0 then 0
else 1 end) as c_overall
from dbo.summary as s
where institution_code = 'WIL'
and final_review_date >= 2004-04-16 and final_review_date
<= 2004-05-12;

In the above example c_verbal, c_grade, c_gradedays, c_seq,
etc. are referred to later in the SQL Select statement.
This worked under Sybase Adaptive Server Anywhere but does
not in MS SQL Server 2005.  An error is given for each
computed variable that is reused.  For instance, 'Msg 207,
Level 16, State 1, Line 1   Invalid column name 'c_verbal'.

Does someone have an idea of an easy work-around solution
for this?  In the above example, we know we can replace the
variable names with the same expression that we used to
originally set its value.

Thanks.
Linda Butler 


0
Chris
10/23/2008 2:27:29 PM
Reply:

Similar Artilces:

Migrating sql server 2005 express database to sql server 2005 database
Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail. Regards, Sandyhttp://www.thequinn.infohttp://www.sksdataservices.comhttp://www.infobasket.info Hi Sandy, To move a database from SQL Express to SQL 2005, you can use the following steps. 1. If your database file is attached to the server instance, you will need to detach it first using SQL management studio. If it is under App_Data folde...

moving an sql server 2005 database to a sql server 2000 database
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?.....   this article explains it. http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html hth,mcm pizzamaker74: I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?..... If both are in the same network or box just register the 2005 with the 2000 cr...

Moving SQL Server 2005 Database to SQL Server 2005 Express
Hi, I have SQL Server 2005 in my Development machine but the hosting server has only SQL server 2005 Express. I was wondering if I develop the DB in SQL server 2005 would be able to move/deploy it to SQL Server Express in the production server? If so how? Can I just generate the script and then run it on the productions server? Or there is a better/easier way? Thanks in advanceShuaib----------------------- I don't anticipate any problems creating database in SQL Server 2005 and moving it to Express. If you need to generate the script to create an empty shell (no d...

Restoring a SQL Server Express 2005 Database to SQL Server Standard 2005
Hello,If I backup and restore an express database to sql server 2005 standard, will there still be limitations in regards to the database size, cpu...etc.? Thanks,Jon  I suppose that no. Limitations are connected to SQL engine not to database itself. But probably if your database will grow up connected to SQL Standard and you will try to move it back to SQL express you will be in trouble.  ThanksJPazgier...

Migrate Database from SQL Server 2005 beta 3 to Sql Server 2005 Release
Do you now, How can I move database with datas to release version of sql server? Procedores backup/restore can't help becouse of unsuported version database. Any ideas? What about generate a schema script?  Then run an install of your schema in a new query.  You could at least get the db schema but not the data.Eric RamseurRainbow Portal 2.0 AdminDownload Rainbow 2.0!!Rainbow Code Rainbow Portal CommunityC# 2005 Group but I have many datas...

Merge two sql statements with derived table Sql Server 2005
I have two select statements that I need to merge into one record. I have a personcontact table that contains contact information and insurance information. (bad db design). below is what I have created so far based on what I have learned from Google searches but I get errors and I am not sure why   ThanksMsg 156, Level 15, State 1, Line 18 Incorrect syntax near the keyword 'Left'. Msg 156, Level 15, State 1, Line 29 Incorrect syntax near the keyword 'as'. select Top 1 pc.firstname + ' ' + pc.lastname as ContactName , A.Address1, A.Address2, A.cit...

Can microsoft SQL Server 2005 database upgrade to microsoft SQL Server 2008 database?
I have a microsoft SQL Server 2005 database, I don't know how to upgrade the 2005 database to 2008 database, I try to use SQL Server Management Studio Express 2008 to open the 2005 database, it's OK, does it mean that the 2005 database have been upgraded to 2008 database? BTW, is SQL Server 2005 database compatible with SQL Server 2008 database completely?SuperCool Multiple ZIP - A utility to unzip multiple files and work with multiple zip filesSuperCool Random Number Generator Yes. It is compatible. Anyhow go through this url for more informationhttp://msdn.microsoft.com/en-us/li...

How to convert a SQL Server 2005 Database to SQL Server Express
Hi, I have a SQL Server 2005 Database that I would like to export to SQL Server Express. How do I go about this? I've tried backing it up, creating a new (blank) SQL Server Express Database and trying to restore it, but get the following message: TITLE: Microsoft SQL Server Management Studio------------------------------ Restore failed for Server 'PRODSOL-LAPTOP1\SQLEXPRESS'.  (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOp...

Restoring a SQL Server 2005 database into SQL Server 2000
Hey guys  We currenlty have SQL Server 2000 installed inhouse for testing.  A client has given us a backup of their 2005 database and i'm wondering if the restore will work into 2000, or any issues i should be aware of. Should i just install msde locally on my machine and restore the database?  thanks Hi,Ask your client to backup database as 2000 format. There is an option for this in Microsoft SQL Server Managment Studio. Then download MSSMSE and install for yourself and try to restore database from it.If database doesn't use any 2005 features (rowcount for exampl...

If I upgrade sql server 2005 express to sql server 2008 express, will my user instances in the 2005 database be transfered automaticly?
If I upgrade sql server 2005 express to sql server 2008 express, will my user instances in the 2005 database be transfered automaticly? Thanks! Hi asdpai, I am not sure if I catch your meaning.But if you want to upgrade the SQL Server 2005 Express to SQL Server 2008 Express, you may refer to the following link: Version and Edition Upgradeshttp://msdn.microsoft.com/en-us/library/ms143393.aspxHow to: Upgrade to SQL Server 2008 (Setup)http://msdn.microsoft.com/en-us/library/ms144267.aspxJian KangMicrosoft Online Community SupportPlease remember to mark the replies as answers if t...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!),   I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine.  But I've had nothing but frustration trying to get it up and running on a webhosting site.  I picked hostmysite.com which uses SQL Server 2005.  I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database.  I tried using SQL Server Management Studio Express.  I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf file...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi,Basically i built a webpage with Login and it's working well from my local machine.Right now, i'm using Database Publishing Wizard. the problem is how do i convert.MDF files to .SQL 2005 to get my database publish in the internet? thanks  Place your .MDF file in the App_Data directory, change the connection string accordingly and then publish your web site. ----------------------------------------------------------Please click Mark As Answer if this helped in solving your problem.  Please click "Mark As Answer" if this hepled in solving your problem. ...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!),   I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine.  But I've had nothing but frustration trying to get it up and running on a webhosting site.  I picked hostmysite.com which uses SQL Server 2005.  I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database.  I tried using SQL Server Management Studio Express.  I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf files ...

How migrate SQL server 2000 database to SQL Server 2005 Express
I  have a  SQL server 2000 database.I have to migrate it to SQL Server 2005 Express.But I don't know how to do. Is any one can help me ? Thanks You can detach the database using sp_detach_db and then re-attach it to your SSE instance using sp_attach_db.  This should work seamlessly, but back up first, just in case.Adam MachanicSQL Server MVP...

Web resources about - Reuse derived columns in same SQL Select statement under SQL Server 2005 - sybase.powerbuilder.database

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from July 2011 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

Jo-Ann Miller sacking: Queensland Premier's statement in full
Annastacia Palaszczuk issues a statement in wake of ethics committee report.

Jo-Ann Miller sacking: Queensland Premier's statement in full
Annastacia Palaszczuk issues a statement in wake of ethics committee report.

Malcolm Turnbull's statement to UN climate conference in Paris
Malcolm Turnbull joined other world leaders in Paris for the United Nations climate talks on Monday. This is his statement.

Freddie Gray case: Officer William G. Porter's recorded statement played in court - Baltimore Sun
Officer William G. Porter told investigators in a recorded interview played for jurors Friday that he did not believe that Freddie Gray was truly ...

Obama releases emotional statement on Planned Parenthood shooting - Business Insider Deutschland
... Parenthood shooting in Colorado Springs, Colorado. REUTERS/Jonathan Ernst President Barack Obama. President Barack Obama released a statement ...

Jury Sees Officer’s Video Statement In Freddie Gray Trial
More key testimony as the prosecution continues to call witnesses in the trial of city police officer William Porter.

Today’s Top Election 2016 News Is Trump’s Vindication Over Muslim Statement
Today’s election 2016 news looks at how one of Donald Trump’s most controversial claims was actually correct, although exaggerated. Here’s how ...

The Latest: Islamic State group airs statement praising California mass shootings
The latest on the mass shooting in San Bernardino, California (all times local): 2:40 a.m.

Resources last updated: 12/6/2015 7:05:11 PM