Crosstab Query doesn't work

Crosstab Query doesn't work

Post by BP Margoli » Wed, 27 Nov 2002 14:36:55



K

If you know in advance the number of columns in the result set, try:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;175574.

If you have a variable number of columns, try:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Using Transform and Pivot clause, query works fine in
> Access-2000 but doesn't work with MSDE or SQl2000.
> Can anybody help me
> Thanks
> K.K.B.

 
 
 

Crosstab Query doesn't work

Post by Greg Obleshchu » Wed, 27 Nov 2002 18:59:39


Hi
Transform is Access syntax.  SQL doesn't support these syntax.  Try the
links BP has listed and also try my crosstab extended stored procedure.
http://www.ag-software.com/xp_ags_crosstab.asp

--
I hope this helps
regards
Greg O MCSD
Document any SQL server database
AGS SQL Scribe http://www.ag-software.com/ags_scribe_index.asp


Quote:> Using Transform and Pivot clause, query works fine in
> Access-2000 but doesn't work with MSDE or SQl2000.
> Can anybody help me
> Thanks
> K.K.B.


 
 
 

1. ORACLE DOESN'T COMPLY TO BASIC STANDARD SQL: SIMPLE QUERIES DOESN'T WORK

ORACLE DOESN'T COMPLY TO BASIC STANDARD SQL: SIMPLE QUERIES DOESN'T WORK

BUG on all Oracle versions including 8.0.5.0.0 Oracle Server on Unix

I found it in July 1994, yes 1994 ! tested on V7 Netware, V6 SUN, V6 Dos
standalone,
it was supposed to be corrected about 2 years after on a 7.3.x version.
But now, there is still a problem on a 8.0.5.0.0 Oracle unix server.
I also tried on a 7.3.2.3.0 Oracle Unix Server (but this version was not
supposed to be fixed)

Bug on SubQueries with NOT IN operator when Full Table Scan on
SubQueries
it evaluates return value when it doesn't match to the Where Clause
and generates an error when some values are not Allowed
(you can also have such a bug if you use to_number function applied to a
column where
there are not numbers in other rows than the selected one)

Why is it so long to repair such standard SQL bugs ???

Drop table Numbers;
Create Table Numbers(nmbr number(20,4));

Drop Table IntNumbers;
Create Table IntNumbers(
        Kind char(20),
        Value Number(10));

Insert into Numbers (nmbr) values(0.1);
Insert into Numbers  (nmbr) values(0.5);
Insert into Numbers (nmbr) values(-0.125);

insert into IntNumbers (Kind,Value) values ('NEGATIVE INT', -8);
insert into IntNumbers (Kind,Value) values ('POSITIVE INT', 10);
insert into IntNumbers (Kind,Value) values ('ZERO', 0);

commit;

select 1/value from IntNumbers Where Kind='POSITIVE INT' or
Kind='NEGATIVE INT';
REM Return No Error and two lines

select nmbr from Numbers where nmbr in
        (select 1/value from IntNumbers Where Kind='POSITIVE INT' or
Kind='NEGATIVE INT') ;
REM Return No Error and two lines

select nmbr from Numbers where nmbr not in
        (select 1/value from IntNumbers Where Kind='POSITIVE INT' or
Kind='NEGATIVE INT') ;
REM return Error 1476 divisor is equal to zero and return no lines
REM should return one line and no error ???

REM     ORACLE DOESN'T COMPLY TO BASIC STANDARD SQL: SIMPLE QUERIES
DOESN'T WORK

--

http://www.pele.org
Online currency converter on http://195583.com

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

2. FTP Task in DTS (2000)?

3. Query works in SQL Query Analyzer but doesn't work in DTS

4. auto increment field

5. Scheduling DTS doesn't fail but doesn't work

6. Why this error after reboot?

7. Query built in Access doesn't work in SQL Server

8. help connecting to Access2000 with VB6 Please

9. ActiveX Query - Why doesn't this work?

10. Query doesn't work after migrating from Access to SQL Server 7

11. Cancel doesn't work on async queries

12. RDO Query on system tables doesn't work with sql 7

13. sp querying a linked server doesn't work although ansi_warnings and ansi_nulls are correctly set