SELECT UNION statement works in MSSQL but not in Oracle 1

[mrgulic]

The following query

SELECT column1, column2, column3 FROM [table1] 
UNION ALL SELECT '0',NULL,NULL FROM [table1] HAVING COUNT(*)=0

in MSSQL it will produce the following results (which i will display as comma separated values) when there are no records

0,NULL,NULL

if there are records then only the real records are displayed.

I know that someone has to ask why.
Short answer: To trick the asp.net GridView control into displaying the footer (which I use to add new records).


My problem is that I have to use Oracle on a new project and Oracle returns nothing when I execute a similar query.
Does anyone have any idea how to get the same results in Oracle?

Thank-you, in advance for your time and effort.

 

[r937]

try like this --

SELECT '0',NULL,NULL 
  FROM ( SELECT COUNT(*) daCount
           FROM table1 ) dummy
 WHERE daCount = 0

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[mrgulic]

Thanks for your reply, but there where no results returned.

 

[gmmastros]

Another way of doing this with TSQL is...

If Exists(Select 1 From Table)
  SELECT column1, column2, column3 FROM [table1] 
Else
  SELECT '0' As Column1, NULL as Column2, NULL As Column3

I don't know Oracle very well, but perhaps something like this will work.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mrgulic]

I tried your suggestion but i get an error.

If Exists(Select 1 From Table)
  SELECT TESTGROUP, APPROVER, LEVL FROM [APPROVAL] 
Else
  SELECT '0' As TESTGROUP, NULL as APPROVER, NULL As LEVL ;

Perhaps I have the syntax wrong, but I have never used If Exists, so I am in unknown territory.
Here is my original statement.

SELECT TESTGROUP, APPROVER, LEVL, TYPEREPORT, PRIMALT, DECODE(REOPEN_FLG, '1', 'true', '0', 'false') AS REOPEN_FLG FROM APPROVAL 
WHERE APPROVER = 'test_id' UNION ALL SELECT '0', null, null, null, null, null FROM APPROVAL HAVING COUNT(*)=0;

 

[bborissov]

This works for me:

SELECT FieldInteger, FieldChar, FieldDateTime FROM YourTable
UNION ALL 
SELECT 0 AS Id, CAST(null as char(10)), CAST(NULL as DateTime) AS Tsg

Borislav Borissov
VFP9 SP2, SQL Server

 

[r937]

yeah, but guys, how do you prevent the 2nd SELECT from returning the '0'/NULL row when the table actually does have rows?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

Rudy,

With the EXISTS method, it wouldn't return the 0 row if there is data in the table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredericofonseca]

following will work in oracle

select coalesce (t1.ename, t.ename) as ename
,coalesce (t1.empno, t.empno) as empno
from
(select rownum as row_num, ename, empno
from scott.emp
where empno = 44444) t1
full outer join
(select rownum as row_num, '' as ename, 0 as empno from dual) t
on t.row_num = t1.row_num

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[mrgulic]

Thanks for your reply fredericofonseca but when I was finally able to get your query to work it output thousands of null records when none existed and the same when one did exist.

select coalesce (t1.TESTGROUP, t.TESTGROUP) as TESTGROUP, coalesce (t1.APPROVER, t.APPROVER) as APPROVER
from (select rownum as row_num, TESTGROUP, APPROVER from APPROVAL where APPROVER = 'test_id') t1
full outer join (select rownum as row_num, '' as TESTGROUP, '' as APPROVER from APPROVAL) t on t.row_num = t1.row_num;

I'm not sure that I adapted your query to my table/columns correctly, but at any rate I am done.

I can get my query to work like that too except that when there is a record it will only output the valid record, but the thousands or null records is not acceptable.


Thank-you all for your attempts, but I have determined that this endeavor is going nowhere so I have employed a cheap workaround to avoid depending on oracle for this event.

I styled controls in the GridView's <EmptyDataTemplate> to look like the header and footer so when no record exists, the real header is hidden and a fake one along with the footer is shown.

It's not ideal as it duplicates many controls, but its easy and functional.
I'll figure out the Oracle problem one day, just not this day.

 

[fredericofonseca]

that is because you removed the "from dual" and replaced it with a table name.

there was a good reason for it to be there, and that reason is that the "from dual" returns a single row.


the second select I gave
full outer join
(select rownum as row_num, '' as ename, 0 as empno from dual) t

should always be "from dual" and any column apart from row num should be specified as the same type of the column from the main query that it is to match.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[mrgulic]

Thanks Frederico,
I didn't know what dual was, thought is was one of your tables.
Works great.

 

[mrgulic]

One small issue.............I didn't include my condition statement.....and when i added that on i received null results.

SELECT COALESCE (t1.TESTGROUP, t2.TESTGROUP) AS TESTGROUP, COALESCE (t1.APPROVER, t2.APPROVER) AS APPROVER, COALESCE (t1.LEVL, t2.LEVL) AS LEVL, 
COALESCE (t1.TYPEREPORT, t2.TYPEREPORT) AS TYPEREPORT, COALESCE (t1.PRIMALT, t2.PRIMALT) AS PRIMALT, 
COALESCE (DECODE(t1.REOPEN_FLG, '1', 'true', '0', 'false'), DECODE(t2.REOPEN_FLG, '1', 'true', '0', 'false')) AS REOPEN_FLG
FROM (SELECT ROWNUM AS row_num, TESTGROUP, APPROVER, LEVL, TYPEREPORT, PRIMALT, REOPEN_FLG FROM APPROVAL WHERE APPROVER = 'userid') t1 
FULL OUTER JOIN (SELECT ROWNUM AS row_num, '' AS TESTGROUP, '' AS APPROVER, '' AS LEVL, '' AS TYPEREPORT, '' AS PRIMALT, '' AS REOPEN_FLG FROM DUAL) t2 ON t2.row_num = t1.row_num
[b]WHERE LOWER(t1.APPROVER) = LOWER(:APPROVER) ORDER BY t1.TESTGROUP[/b];

 

[fredericofonseca]

try this

SELECT COALESCE (t1.TESTGROUP, t2.TESTGROUP) AS TESTGROUP
      ,COALESCE (t1.APPROVER, t2.APPROVER) AS APPROVER
      ,COALESCE (t1.LEVL, t2.LEVL) AS LEVL 
      ,COALESCE (t1.TYPEREPORT, t2.TYPEREPORT) AS TYPEREPORT
      ,COALESCE (t1.PRIMALT, t2.PRIMALT) AS PRIMALT, 
      ,[COLOR=#CC0000]DECODE(COALESCE(t1.REOPEN_FLG,T2.REOPEN_FLG), '1', 'true', '0', 'false') AS REOPEN_FLG[/color]
FROM (SELECT ROWNUM AS row_num
            ,TESTGROUP
            ,APPROVER
            ,LEVL
            ,TYPEREPORT
            ,PRIMALT
            ,REOPEN_FLG
       FROM APPROVAL
      [COLOR=#CC0000]WHERE APPROVER = LOWER(:APPROVER)[/color]
     ) t1 
      FULL OUTER JOIN (SELECT ROWNUM AS row_num
                             ,'' AS TESTGROUP
                             ,'' AS APPROVER
                             ,'' AS LEVL
                             ,'' AS TYPEREPORT
                             ,'' AS PRIMALT
                             ,'0' AS REOPEN_FLG FROM DUAL
                      ) t2
      ON t2.row_num = t1.row_num
WHERE LOWER(t1.APPROVER) = LOWER(:APPROVER)
[COLOR=#CC0000]or t1.approver is null[/color]
ORDER BY t1.TESTGROUP;

The order by should not be excluding records - but if the above does not give anything remove the order by just to see


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[mrgulic]

I had to make the following change

from:

,DECODE(COALESCE(t1.REOPEN_FLG,T2.REOPEN_FLG), '1', 'true', '0', 'false') AS REOPEN_FLG

to:

,COALESCE (DECODE(t1.REOPEN_FLG, '1', 'true', '0', 'false'), DECODE(t2.REOPEN_FLG, '1', 'true', '0', 'false')) AS REOPEN_FLG

as I was getting the following error............after that change it works great.

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

I can't express how much I appreciate this.
Thank-you so very much.

 

[r937]

not to be pedantic or anything, but the next time you have an oracle problem, could you please post it in one of the oracle forums?

this forum is for microsoft sql server

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[mrgulic]

I would have posted to an Oracle forum except that out of the following I didn't see one that was the equivalent of or close to "Microsoft SQL Server: Programming Forum".

[ul]
[li]Oracle: Oracle release - 9i and earlier[/li]
[li]Oracle: Oracle release 10g[/li]
[li]Oracle: Oracle release 11g[/li]
[li]Oracle certification and testing[/li]
[li]Oracle: WebDB[/li]
[li]Oracle: Reports[/li]
[li]Oracle: Web App Server[/li]
[li]Oracle: Enterprise Developer Suite[/li]
[li]Oracle ERP Solutions[/li]
[li]Oracle: JDeveloper[/li]
[li]Oracle ContactCenter@nywhere[/li]
[li]Oracle (BEA): WebLogic[/li]
[li]Oracle (BEA): Tuxedo[/li]
[li]Oracle: Oracle9iAS Application Server[/li]
[/ul]

I would have had to guess and therefore run the risk of being wrong and thus be informed by someone that I had posted to the wrong Oracle forum. It was a safe bet that someone watching this forum would be skilled in both.

For future reference, which Oracle forum would be the equivalent of this one?

 

[r937]

For future reference, which Oracle forum would be the equivalent of this one?

i have no idea, because i don't do oracle

but i would guess Oracle release 11g or whatever one corresponds to the version you're using

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon