What am I missing

What am I missing

Post by Paul » Sat, 15 Feb 2003 22:44:28



Hi,

Can anyone help with this one ? I'm doing a select
statement using CASE and have given the rsult an ALIAS. I
need to do a further calculation with DATEDIFF using the
result from the case calculation yet I get invalid column
name on the column with the alias. i.e.

SELECT InvoicedTo, DateOut, (select CASE WHEN DateOut
< '03/31/2003' THEN DateOut ELSE '03/31/2003' end) as
InvoiceToDate,  DATEDIFF(d, InvoicedTo, InvoiceToDate) + 1
AS ChargeDays FROM tblArchive

Can anybody tell me where I've gone wrong ?

 
 
 

What am I missing

Post by Michael MacGrego » Sat, 15 Feb 2003 22:49:27


Well it helps if the query is formatted so it's more readable, though I'm
not happy with putting all of the case expression on one line, I've done so
below for the DATEDIFF bit, mainly as the wrapping that can occur after
submitting will make a mess of it anyway but hopefully you'll get the
picture. And what was that odd SELECT before the CASE about?

SELECT InvoicedTo,
     DateOut,
     CASE
         WHEN DateOut < '03/31/2003' THEN DateOut
          ELSE '03/31/2003'
      END AS InvoiceToDate,
      DATEDIFF(d, InvoicedTo, CASE WHEN DateOut < '03/31/2003' THEN DateOut
ELSE '03/31/2003' END) + 1 AS ChargeDays
  FROM tblArchive

Michael MacGregor
Database Architect
SalesDriver

 
 
 

What am I missing

Post by Phil » Sat, 15 Feb 2003 23:01:41


Paul.

A calculated field cannot be used within the same select.  
A way round this would be to create a sub-select then use
the calculated field in the outer select, something like
this:

SELECT    DaysToCharge.InvoiceedTo,
          DaysToCharge.DateOut,
          DATEDIFF(d, DaysToCharge.InvoicedTo,
DaysToCharge.InvoiceToDate) + 1 AS ChargeDays
FROM      (
              SELECT    InvoicedTo,
                        DateOut,
                        (select CASE WHEN DateOut
< '03/31/2003' THEN DateOut ELSE '03/31/2003' end) as
InvoiceToDate  
              FROM      tblArchive
           ) AS DaysToCharge

Hope this helps.

Quote:>-----Original Message-----
>Hi,

>Can anyone help with this one ? I'm doing a select
>statement using CASE and have given the rsult an ALIAS. I
>need to do a further calculation with DATEDIFF using the
>result from the case calculation yet I get invalid column
>name on the column with the alias. i.e.

>SELECT InvoicedTo, DateOut, (select CASE WHEN DateOut
>< '03/31/2003' THEN DateOut ELSE '03/31/2003' end) as
>InvoiceToDate,  DATEDIFF(d, InvoicedTo, InvoiceToDate) +
1
>AS ChargeDays FROM tblArchive

>Can anybody tell me where I've gone wrong ?
>.

 
 
 

1. INITIALIZE DEVICE- -What am I missing???

On SQL 6.5, the DATABASE BACKUP/RESTORE dialogue has
the INITIALIZE DEVICE checkbox. HELP states that
checking the box will append, and clearing the box
will overwrite...!!!???  That seems just the opposite
of what it should be.  What am I missing???

*** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ***

2. Connect to Oracle8i using C on Solaris 2.6

3. What am I missing....?

4. pdoxwin form/button question

5. Integrated Security: What am I missing here?

6. Selecting MAX(date) problems

7. HELP: Am I missing the point or something ?

8. Microsoft New Charges for Technical Support, Do Something

9. Help with SQL Query - what am I missing?

10. Am I missing something????

11. Transaction Snapshots - What am I Missing?...

12. Simple set variable, what am I missing

13. What am I missing???