Board index » delphi » From Jet Access to SQL Server SQL Languages - Please some help from the experts

From Jet Access to SQL Server SQL Languages - Please some help from the experts

Dear Sirs,

Im new in SQL Server 7.0 trying to transform my 85 well shaped Access Jet
SQL queries, but its not easy.

So Im trying to get some help.

Lets consider this query:

SELECT
IIF( ISNULL( a ), b , a*b ) as Quantity,
IIF( ISNULL( x ) , Quantity/y , Quantity/z ) as Paper,
Paper*w as Printings , *
FROM MyTable
I started replacing my IIFs with CASEs in an SQL Server Stored Procedure
but what I cant figure out is how to reuse the calculation of one column
 Quantity for example ), in the next calculations. SQL Server is telling
Quantity is not a valid Column Name.

I know I can repeat all the Quantity ( and Paper ) code or use subqueries
but I ask you ?

Isnt such a powerfull way to write this Access SQL code in SQL Server in
just one single Stored Proc ?

Thanks in advance.

Milka

 

Re:From Jet Access to SQL Server SQL Languages - Please some help from the experts


Try to replace IIF(ISNULL( with ISNULL or COALESCE. The syntax and use is
slightly different:
IIF( ISNULL( a ), b , a*b ) as Quantity == ISNULL(a*b,b) as Quantity
meaning that if "a" is null, then "a*b" will also be null and it will return
second expression.

MSSQL does not support reuse of calculated (aliased) columns by name, so you
have to repeat the expression. Yes, it makes the query look ugly and
unreadable. One of limitations and reasons to hate MSSQL.

--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: [email protected]

No questions via email, unless explicitly invited.

Quote
Milka wrote in message <[email protected]>...
>Dear Sirs,

>Im new in SQL Server 7.0 trying to transform my 85 well shaped Access Jet
>SQL queries, but its not easy.

>So Im trying to get some help.

>Lets consider this query:

>SELECT
>IIF( ISNULL( a ), b , a*b ) as Quantity,
>IIF( ISNULL( x ) , Quantity/y , Quantity/z ) as Paper,
>Paper*w as Printings , *
>FROM MyTable

>I started replacing my IIFs with CASEs in an SQL Server Stored Procedure
>but what I cant figure out is how to reuse the calculation of one column
> Quantity for example ), in the next calculations. SQL Server is telling
>Quantity is not a valid Column Name.

[snip]

Re:From Jet Access to SQL Server SQL Languages - Please some help from the experts


Well Robert,

Im just trying MSSQL and I found it in MSOffice2000 in MSDE for free.
Can you tell another RDBMS with good cost/benefit relationship like MSSQL
which bring the power Im asking for ?

"You know MSSQL, specially when bundled
in BackOffice Small Business, which is my case because I dont expect
to have more then 50 users in the next years, is very cheap"

Best Regards

Milka

Robert Cerny <[email protected]> escreveu nas notcias de
mensagem:[email protected]

Quote
> Try to replace IIF(ISNULL( with ISNULL or COALESCE. The syntax and use is
> slightly different:
> IIF( ISNULL( a ), b , a*b ) as Quantity == ISNULL(a*b,b) as Quantity
> meaning that if "a" is null, then "a*b" will also be null and it will
return
> second expression.

> MSSQL does not support reuse of calculated (aliased) columns by name, so
you
> have to repeat the expression. Yes, it makes the query look ugly and
> unreadable. One of limitations and reasons to hate MSSQL.

> --
> ----------------------
> Regards
> Robert Cerny
> Remove both qwe when replying
> email: [email protected]

> No questions via email, unless explicitly invited.
> Milka wrote in message <[email protected]>...
> >Dear Sirs,

> >Im new in SQL Server 7.0 trying to transform my 85 well shaped Access
Jet
> >SQL queries, but its not easy.

> >So Im trying to get some help.

> >Lets consider this query:

> >SELECT
> >IIF( ISNULL( a ), b , a*b ) as Quantity,
> >IIF( ISNULL( x ) , Quantity/y , Quantity/z ) as Paper,
> >Paper*w as Printings , *
> >FROM MyTable

> >I started replacing my IIFs with CASEs in an SQL Server Stored
Procedure
> >but what I cant figure out is how to reuse the calculation of one column
> > Quantity for example ), in the next calculations. SQL Server is telling
> >Quantity is not a valid Column Name.
> [snip]

Other Threads