|
Home > Archive > ASE Database forum > April 2005 > Dual
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| Anyone around here knows if there is an equivalent or at
least similar way of implementing a dummy table called DUAL
from Oracle DB?
| |
| Stefan Karlsson 2005-04-05, 8:06 pm |
| <teric> wrote in message news:4252be37.5a8b.1681692777@sybase.com...
> Anyone around here knows if there is an equivalent or at
> least similar way of implementing a dummy table called DUAL
> from Oracle DB?
Why ?
DUAL is commonly used as a dummy table in SELECT statement to comply with
that DBMS' syntactical requirements. It's not required in ASE, e.g.
SELECT getdate()
HTH,
/Stefan
| |
| Carl Kayser 2005-04-05, 8:06 pm |
|
I'm guessing that you want to "get" a computed value or values. In which
case eliminate the "from" clause, e.g.,
select "System name" = suser_name (), "DB name" = user_name ()
<teric> wrote in message news:4252be37.5a8b.1681692777@sybase.com...
> Anyone around here knows if there is an equivalent or at
> least similar way of implementing a dummy table called DUAL
> from Oracle DB?
| |
|
| here is why. I am trying to get values that are returned by
3 sql statements because I need it in 3 column form.
Heres is my SQL stmt
SELECT(SELECT Convert(char(3), Count(*)) + ' for 8 AM'
FROM nxstar_e LEFT OUTER JOIN nxscuste ON
nxstar_e.cust_code = nxscuste.cust_code
WHERE nxstar_e.assigned_to in ('.EMAIL', '.INTERNA',
'CUSTSUPT') and
nxstar_e.tar_status in ('NEW','OPEN') and
(Convert(time, nxstar_e.entered_date) >= '1:00:00'
AND
Convert(time, nxstar_e.entered_date) <=
'8:00:00')) EIGHTAM,
(SELECT Convert(char(3), Count(*)) + ' for 5 PM'
FROM nxstar_e LEFT OUTER JOIN nxscuste ON
nxstar_e.cust_code = nxscuste.cust_code
WHERE nxstar_e.assigned_to in ('.EMAIL', '.INTERNA',
'CUSTSUPT') and
nxstar_e.tar_status in ('NEW','OPEN') and
(Convert(time, nxstar_e.entered_date) >= '8:00:00'
AND
Convert(time, nxstar_e.entered_date) <=
'17:00:00')) FIVEPM,
(SELECT Convert(char(3), Count(*)) + ' for 1 AM'
FROM nxstar_e LEFT OUTER JOIN nxscuste ON
nxstar_e.cust_code = nxscuste.cust_code
WHERE nxstar_e.assigned_to in ('.EMAIL', '.INTERNA',
'CUSTSUPT') and
nxstar_e.tar_status in ('NEW','OPEN') and
(Convert(time, nxstar_e.entered_date) >=
'17:00:00' or
Convert(time, nxstar_e.entered_date) <=
'1:00:00')) ONEAM
FROM DUAL;
> <teric> wrote in message
> from Oracle DB?
>
> Why ?
>
> DUAL is commonly used as a dummy table in SELECT statement
> to comply with that DBMS' syntactical requirements. It's
> not required in ASE, e.g. SELECT getdate()
>
> HTH,
>
> /Stefan
>
>
| |
|
| That's almost what I want, except that values are going to
come from 3 SQL stmt. I'll try this suggestion. Thanks!
> I'm guessing that you want to "get" a computed value or
> values. In which case eliminate the "from" clause, e.g.,
>
> select "System name" = suser_name (), "DB name" =
> user_name ()
>
> <teric> wrote in message
> from Oracle DB?
>
>
>
| |
|
| This one works for me. Thanks!
> I'm guessing that you want to "get" a computed value or
> values. In which case eliminate the "from" clause, e.g.,
>
> select "System name" = suser_name (), "DB name" =
> user_name ()
>
> <teric> wrote in message
> from Oracle DB?
>
>
>
| |
| Stefan Karlsson 2005-04-05, 8:06 pm |
| <teric> wrote in message news:4252ce3c.54b5.1681692777@sybase.com...[color=darkred]
> here is why. I am trying to get values that are returned by
> 3 sql statements because I need it in 3 column form.
>
> Heres is my SQL stmt
>
> SELECT(SELECT Convert(char(3), Count(*)) + ' for 8 AM'
> FROM nxstar_e LEFT OUTER JOIN nxscuste ON
> nxstar_e.cust_code = nxscuste.cust_code
> WHERE nxstar_e.assigned_to in ('.EMAIL', '.INTERNA',
> 'CUSTSUPT') and
> nxstar_e.tar_status in ('NEW','OPEN') and
> (Convert(time, nxstar_e.entered_date) >= '1:00:00'
> AND
> Convert(time, nxstar_e.entered_date) <=
> '8:00:00')) EIGHTAM,
> (SELECT Convert(char(3), Count(*)) + ' for 5 PM'
> FROM nxstar_e LEFT OUTER JOIN nxscuste ON
> nxstar_e.cust_code = nxscuste.cust_code
> WHERE nxstar_e.assigned_to in ('.EMAIL', '.INTERNA',
> 'CUSTSUPT') and
> nxstar_e.tar_status in ('NEW','OPEN') and
> (Convert(time, nxstar_e.entered_date) >= '8:00:00'
> AND
> Convert(time, nxstar_e.entered_date) <=
> '17:00:00')) FIVEPM,
> (SELECT Convert(char(3), Count(*)) + ' for 1 AM'
> FROM nxstar_e LEFT OUTER JOIN nxscuste ON
> nxstar_e.cust_code = nxscuste.cust_code
> WHERE nxstar_e.assigned_to in ('.EMAIL', '.INTERNA',
> 'CUSTSUPT') and
> nxstar_e.tar_status in ('NEW','OPEN') and
> (Convert(time, nxstar_e.entered_date) >=
> '17:00:00' or
> Convert(time, nxstar_e.entered_date) <=
> '1:00:00')) ONEAM
> FROM DUAL;
>
I tested the statement without the "FROM DUAL" clause and it works fine
(created empty nxstar_e and nxscuste tables). What exactly is the issue or
the error message you receive ?
/Stefan
| |
|
| I already got it. Thanks. Never thought Select without a
From will work for Sybase. That's not the case for Oracle
that is why we use DUAL. Thanks again.
> <teric> wrote in message
> statements because I need it in 3 column form. >
>
> I tested the statement without the "FROM DUAL" clause and
> it works fine (created empty nxstar_e and nxscuste
> tables). What exactly is the issue or the error message
> you receive ?
>
> /Stefan
>
>
|
|
|
|
|