is it a bug?

it is the second time i am writing this problem.
I have a view named x_vw and while ? exec it as
select * from x_vw where field1='a1'
it returns the results which are field1='a1' but when I exec the query as
select * from x_vw
it returns all results but eccept the results which are field1='a1'
select * from X_VW where SiparisNo='a1'
returns data I want but the same data doesnt exists in the resultset of
select * from X_VW
its funny that I find a stupid solution for a stupid problem:))
select * from X_VW where SiparisNo like '%%'
I'm starving for explanation
is it a bug?
if so how can I report a bug to Microsoft?
CREATE view X_VW
as
SELECT sd.Sirketkod AS 'sirket',c.HesapKodu AS 'Sat?c?', c.Unvan,
sd.EvrakNo AS 'SiparisNo',
sd.MalKodu,
s.MalAdi,
SUM(sd.Miktar) AS 'Siparis',
dbo.DMGetIthSipIptalMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
'iptalMiktar',
dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
'Kars?lanan',
SUM(sd.Miktar)-dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu
) as 'Kalan',
dbo.DMGetIthStokGirMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu)as 'StokGiris
',
sh.EkSipNo,sh.aciklama,sh.SiparisTip,
s.kod1,
s.kod2,
s.kod3,
s.kod4,
s.kod5,
gamet2003.dbo.DMGetStokMik(sd.Sirketkod,sd.Malkodu)as 'stokmiktar',
Isnull(f.fiyat,0) as 'Ithalat_fiyati', Isnull(f.dovizkod,'yok') as 'Dovizkod
',
sd.Fiyat as 'SipFiyat', Isnull(sh.dovizkod,'yok') as 'SipDovizkod'
FROM gamet2003..SIP_D sd
inner join gamet2003..SIP_H sh on sh.SirketKod = sd.SirketKod AND sh.EvrakNo
= sd.EvrakNo
inner join gamet2003..CHK c on sd.SirketKod = c.SirketKod AND sd.Chk =
c.HesapKodu
inner join gamet2003..STK s on s.SirketKod = sd.SirketKod AND s.MalKodu =
sd.MalKodu
left join gamet2003..FIYATLIST f on f.sirketkod=sd.sirketkod and
s.malkodu=f.malkodu and f.Fiyatkod='ITHALAT'
WHERE sd.SirketKod='gamet' and sh.EvrakNo like 'IAS%'
and exists(select 1 from
(
select x.sirketkod,x.evrakno
from
(
select th.sirketkod,th.evrakno from gamet2003..ITH_SIP_D th,
gamet2003..SIP_D sp
where sp.sirketkod=th.sirketkod and sp.evrakno=th.evrakno and
sp.oldsirano=th.sirano
and (sp.beklet is null or sp.beklet=0)
group by th.sirketkod,th.evrakno
having abs(sum(sp.kalanmiktar-th.karsilananmiktar))>0
union all
select td.sirketkod,td.oldevrakno as evrakno from gamet2003..ITH_D td
left join gamet2003..STI_H sh on sh.sirketkod=td.sirketkod and
sh.Irsaliyeno=td.Evrakno and (sh.beklet is null or sh.beklet=0)
left join gamet2003..STI_D sd on sd.sirketkod=td.sirketkod and
sh.evrakno=sd.evrakno and (sd.beklet is null or sd.beklet=0)
where (td.beklet is null or td.beklet=0)
group by td.sirketkod,td.oldevrakno
having Isnull(sum(td.miktar),0)>Isnull(sum(sd.miktar),0)
)X
group by x.sirketkod,x.evrakno
)y
where y.sirketkod=sd.sirketkod and y.evrakno=sd.evrakno)and (sd.beklet is
null or sd.beklet=0)
group by sd.SirketKod,c.HesapKodu, c.Unvan, sd.EvrakNo,
sd.MalKodu,s.MalAdi,sh.EkSipNo,sh.aciklama,sh.SiparisTip,s.kod1,s.kod2,s.kod
3,s.kod4,s.kod5,f.fiyat,f.dovizkod,sd.Fiyat,sh.dovizkodHi
Call Microsoft PSS.
http://support.microsoft.com/common/international.aspx
You will be expected to be able to give them sufficient data to reproduce
the problem.
If it is not a bug, you will be charged for the support.
Regards
Mike
"POKEMON" wrote:

> it is the second time i am writing this problem.
> I have a view named x_vw and while ? exec it as
> select * from x_vw where field1='a1'
> it returns the results which are field1='a1' but when I exec the query as
> select * from x_vw
> it returns all results but eccept the results which are field1='a1'
> select * from X_VW where SiparisNo='a1'
> returns data I want but the same data doesnt exists in the resultset of
> select * from X_VW
> its funny that I find a stupid solution for a stupid problem:))
> select * from X_VW where SiparisNo like '%%'
> I'm starving for explanation
> is it a bug?
> if so how can I report a bug to Microsoft?
>
> CREATE view X_VW
> as
> SELECT sd.Sirketkod AS 'sirket',c.HesapKodu AS 'Sat?c?', c.Unvan,
> sd.EvrakNo AS 'SiparisNo',
> sd.MalKodu,
> s.MalAdi,
> SUM(sd.Miktar) AS 'Siparis',
> dbo.DMGetIthSipIptalMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
> 'iptalMiktar',
> dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
> 'Kars?lanan',
> SUM(sd.Miktar)-dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKo
du) as 'Kalan',
> dbo.DMGetIthStokGirMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu)as 'StokGir
is',
> sh.EkSipNo,sh.aciklama,sh.SiparisTip,
> s.kod1,
> s.kod2,
> s.kod3,
> s.kod4,
> s.kod5,
> gamet2003.dbo.DMGetStokMik(sd.Sirketkod,sd.Malkodu)as 'stokmiktar',
> Isnull(f.fiyat,0) as 'Ithalat_fiyati', Isnull(f.dovizkod,'yok') as 'Dovizk
od',
> sd.Fiyat as 'SipFiyat', Isnull(sh.dovizkod,'yok') as 'SipDovizkod'
> FROM gamet2003..SIP_D sd
> inner join gamet2003..SIP_H sh on sh.SirketKod = sd.SirketKod AND sh.Evrak
No
> = sd.EvrakNo
> inner join gamet2003..CHK c on sd.SirketKod = c.SirketKod AND sd.Chk =
> c.HesapKodu
> inner join gamet2003..STK s on s.SirketKod = sd.SirketKod AND s.MalKodu =
> sd.MalKodu
> left join gamet2003..FIYATLIST f on f.sirketkod=sd.sirketkod and
> s.malkodu=f.malkodu and f.Fiyatkod='ITHALAT'
> WHERE sd.SirketKod='gamet' and sh.EvrakNo like 'IAS%'
> and exists(select 1 from
> (
> select x.sirketkod,x.evrakno
> from
> (
> select th.sirketkod,th.evrakno from gamet2003..ITH_SIP_D th,
> gamet2003..SIP_D sp
> where sp.sirketkod=th.sirketkod and sp.evrakno=th.evrakno and
> sp.oldsirano=th.sirano
> and (sp.beklet is null or sp.beklet=0)
> group by th.sirketkod,th.evrakno
> having abs(sum(sp.kalanmiktar-th.karsilananmiktar))>0
> union all
> select td.sirketkod,td.oldevrakno as evrakno from gamet2003..ITH_D td
> left join gamet2003..STI_H sh on sh.sirketkod=td.sirketkod and
> sh.Irsaliyeno=td.Evrakno and (sh.beklet is null or sh.beklet=0)
> left join gamet2003..STI_D sd on sd.sirketkod=td.sirketkod and
> sh.evrakno=sd.evrakno and (sd.beklet is null or sd.beklet=0)
> where (td.beklet is null or td.beklet=0)
> group by td.sirketkod,td.oldevrakno
> having Isnull(sum(td.miktar),0)>Isnull(sum(sd.miktar),0)
> )X
> group by x.sirketkod,x.evrakno
> )y
> where y.sirketkod=sd.sirketkod and y.evrakno=sd.evrakno)and (sd.beklet is
> null or sd.beklet=0)
> group by sd.SirketKod,c.HesapKodu, c.Unvan, sd.EvrakNo,
> sd.MalKodu,s.MalAdi,sh.EkSipNo,sh.aciklama,sh.SiparisTip,s.kod1,s.kod2,s.k
od3,s.kod4,s.kod5,f.fiyat,f.dovizkod,sd.Fiyat,sh.dovizkod
>|||Hi POKEMON,
Your current view is very complex, too complex to analyse over a
newsgroup (and missing DDL and sample data). But still: It sounds like a
bug. The big question is: is this a known / documented bug or a new bug.
There are some known issues relating to parallellism. I would start by
adding the query hint OPTION (MAXDOP 1) when querying the view. So for
example:
select * from X_VW option (maxdop 1)
And of course, make sure you are running the latest SQL-Server service
pack (3a).
If money is not an issue, then you can contact Microsoft Support (as
posted by Mike). Otherwise, it makes sense to isolate the problem. You
can do this by removing all columns, UDFs, joins, etc. that do not
influence the problem.
For example: does the problem still remain if all UDFs are removed? Does
the problem still remain if the entire EXISTS subquery is removed? Does
the problem still remain if all outer joins are removed? Does the
problem still remain if you rewrite "gamet2003..ITH_SIP_D th,
gamet2003..SIP_D sp" as an INNER JOIN? Does the problem still remain if
you remove all OR operators by rewriting all "<something> is null or
<something> = 0" to "<something> is null"?
In the end, Microsoft will need a script to reproduce the problem.
HTH,
Gert-Jan

Is ISO/IEC 9075 bad?

i've been thrown into a pit with DB2, having to makes things work.
In DB2, sql constructs as
SELECT 'Hello, world!'
are invalid, because i have no from
DECLARE InvoiceTotal money
is invalid because DECLARE is a "command statement" and not a "statement"
UPDATE Invocices SET LastModifiedDate = NULL
is invalid because NULL cannot be implicitly converted to a data type
CREATE PROCEDURE FetchOutstandingOrders AS
SELECT * FROM Orders WHERE Status <> 3
is invalid because i have didn't declare a cursor then leave it open.
CREATE TABLE Users (UserID, int)
INSERT INTO Users (1)
SELECT * FROM Users
DROP TABLE Users
is invalid because i have multiple statements.
Is Microsoft helping it's developers by making a syntax that is, although
non-standard, is easier to use? Or would it be better if Microsoft removed
all these nicities and forced developers to adhere to the standard?
<celkoBait>
A row is a record. A column is a field.
</celkoBait>Post this in comp.databases.ibm-db2 and make their day:)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:eJcpIGuQGHA.4536@.tk2msftngp13.phx.gbl...
> i've been thrown into a pit with DB2, having to makes things work.
> In DB2, sql constructs as
> SELECT 'Hello, world!'
> are invalid, because i have no from
> DECLARE InvoiceTotal money
> is invalid because DECLARE is a "command statement" and not a "statement"
> UPDATE Invocices SET LastModifiedDate = NULL
> is invalid because NULL cannot be implicitly converted to a data type
> CREATE PROCEDURE FetchOutstandingOrders AS
> SELECT * FROM Orders WHERE Status <> 3
> is invalid because i have didn't declare a cursor then leave it open.
> CREATE TABLE Users (UserID, int)
> INSERT INTO Users (1)
> SELECT * FROM Users
> DROP TABLE Users
> is invalid because i have multiple statements.
> Is Microsoft helping it's developers by making a syntax that is, although
> non-standard, is easier to use? Or would it be better if Microsoft removed
> all these nicities and forced developers to adhere to the standard?
> <celkoBait>
> A row is a record. A column is a field.
> </celkoBait>
>|||"05ponyGT" <noname@.overwood.com> wrote in message
news:u8$0WovQGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Post this in comp.databases.ibm-db2 and make their day:)
i've been trying get up to speed with DB2-SQL in the DB2 group. They are
already quite familiar with me :)
i just wanted to know if i'm crazy in thinking that if violating the
standards makes things a hellava lot easier to use, shouldn't you go ahead
and violate it?|||Yes :)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23J7Wi3vQGHA.5296@.tk2msftngp13.phx.gbl...
> "05ponyGT" <noname@.overwood.com> wrote in message
> news:u8$0WovQGHA.2300@.TK2MSFTNGP15.phx.gbl...
> i've been trying get up to speed with DB2-SQL in the DB2 group. They are
> already quite familiar with me :)
> i just wanted to know if i'm crazy in thinking that if violating the
> standards makes things a hellava lot easier to use, shouldn't you go ahead
> and violate it?
>|||>> In DB2, sql constructs as SELECT 'Hello, world!' are invalid, because I
have no from <<
YEP! That is Standard SQL and always has been. If you had learned to
speak SQL instead of a "Hillbilly dialect", you would know this.
This is a SQL/PSM statement, which has to be part of a module. Just
like all the other compiled programming languages. No such data type
in in Real SQL, too.
is invalid because NULL cannot be implicitly converted to a data
[date?] type <<
SET LastModifiedDate = CAST (NULL AS TIMESTAMP)
Again, this is Standard SQL. You can find the same problem in some
CASE expressions which need to be told the type of a column when they
cannot determine it.
SELECT * FROM Orders WHERE Status <> 3
is invalid because i have didn't declare a cursor then leave it open.
<<
SQL has to have a host program. Tiered architecture and all that jazz.
You are not in "Desk top land" any more.
(user_id INTEGER NOT NULL PRIMARY KEY); INSERT INTO Users
(1);
SELECT * FROM Users;
DROP TABLE Users;
is invalid because I have multiple statements. <<
Sorry, but even for sample code, I want to see a key in a table. You
also left off the semi-colons. Where are the BEGIN [ATOMIC] ..END;
that make this code into a valid statement? Did you mind having to do
this bracketing in C, C++, Java, Algol, Pascal, etc. ?
T-SQL got its name from the Sybase days when each statement was
buffered up until you interactively did a transaction with a GO
command. That is closer to BASIC than a compiled language.
Much better. They are not nice or easier to use. MONEY has bad math
results as well as formatting in the database side. The syntax for
SELECT TOP.. ORDER BY.. is awful and the UPDATE.. FROM.. is
unpredictable.
Extensions and failures only serve to confuse people who have to
maintain code, and they can be changed on MS whims, etc.
<celkoBait>
A row is a record. A column is a field.
</celkoBait>
LOL! See? No wonder you cannot write SQL! Seriously, your objections
all seem to come from not knowing how to write a compiled language and
that is far more fuindamental that SQL.|||I think all the short cuts that simplify things without adding functionality
are dangerous. Leaving out the semicolons, not explicitly casting values,
selecting values without a from clause - these are all shortcuts that are
unnecessary and only lead to bad habits. I myself fall into the trap of
using them with SQL Server, this is just a reminder of why I shouldn't.
On the other hand, proprietary code is fine if it allows you to perform
functions that would otherwise require complex coding to accomplish. Basic
SQL rules, however, should remain basic and be enforced. Microsoft (and
other vendors also) encourage bad practices through a lot of these
"niceties".
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23J7Wi3vQGHA.5296@.tk2msftngp13.phx.gbl...
> "05ponyGT" <noname@.overwood.com> wrote in message
> news:u8$0WovQGHA.2300@.TK2MSFTNGP15.phx.gbl...
> i've been trying get up to speed with DB2-SQL in the DB2 group. They are
> already quite familiar with me :)
> i just wanted to know if i'm crazy in thinking that if violating the
> standards makes things a hellava lot easier to use, shouldn't you go ahead
> and violate it?
>|||Boyd,
> Is Microsoft helping it's developers by making a syntax that is, although
> non-standard, is easier to use? Or would it be better if Microsoft removed
> all these nicities and forced developers to adhere to the standard?
Believe it or not there is a school of thought that essentially makes all
your
questions moot.The thinking is that what is perceived to be 'Real sql' is so
fundamentally flawed logically,so obtuse and so poorly implemented that we
would be wise to start over.In such an environment 'standards' become
meaningless.Unfortuneately what most people know of rdbms is what vendors
tell them and what the same vendors make up as standards.I like Joe C. but
his viewpoint need not be the bedrock on which a db rests.It becomes silly
to say one vendor is more real than another.It becomes even sillier to say
that
there are 'shortcuts that are unnecessary and only lead to bad habits'.One
vendor
may be more or less expedient than another.In the end it's like Shakespear
said
't'where ignorance is bliss t'is folly to be wise'.
I'd be happy to post links if your interested:)
$.02 from
www.rac4sql.net|||> Standard SQL ...
> speak SQL
> ...
> Real SQL>
> Again, ... Standard SQL
As long as you don't advocate sticking to a standard for sticking to a
standard's sake. If that is the limit of your argument, then you would have
no problem people improving the standard.

> Did you mind having to do
> this bracketing in C, C++, Java, Algol, Pascal, etc. ?
i do not. C, C++, Java and Pascal all require a semi-colon to separate
statements. VB uses line-ends. That was a technical requirement of the
compiler so it could figure out what you typed.
But they have databases on computers now. Computers have gotten pretty
powerful in the last few months. And it turns out that they can be made
pretty smart, and with SQL at least can figure out exactly what i mean and
not require everything so rigid. Computers these days can be made easier to
use.

> I want to see a key in a table.
Yes, a row is an ordered n-tuple, and a table with a key on it is an
unordered set. Very good. Now we're renaming marriage to civil unions, rows
to records, columns to fields and Montana to France.

> Extensions and failures only serve to confuse people who have to
> maintain code, and they can be changed on MS whims, etc.
Then add them to the standard, and they'll no longer be extensions and
failures.|||>I think all the short cuts that simplify things without adding
>functionality
> are dangerous. Leaving out the semicolons, not explicitly casting values,
> selecting values without a from clause - these are all shortcuts that are
> unnecessary and only lead to bad habits.
Why?

> I myself fall into the trap of
> using them with SQL Server, this is just a reminder of why I shouldn't.
Why?|||"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%235bQgaFRGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Why?
You answered this question yourself when you made the original post:
<begin Quote>
i've been thrown into a pit with DB2, having to makes things work.
In DB2...
are invalid, because i have no from
...
is invalid because DECLARE is a "command statement" and not a "statement"
....
is invalid because NULL cannot be implicitly converted to a data type
...
is invalid because i have didn't declare a cursor then leave it open.
...
is invalid because i have multiple statements.
<end Quote>
You have to unlearn everythign that you learned with microsoft because none
of it was standard, so your knowledge and skills are not portable. This in
itself should be reason enough.
Now, sometimes vendors give us specific code that allows us to do things
that would otherwise require ridiculous amounts of work. There are cases
where deviating from the standards give us huge benefits.
Explicitly converting types, defining the ends of your statements, writing
statements that are complete and require no assumptions to be made by the
computer - These are all good programming practice, no matter what language
you are using. They prevent ambiguity and make it explicitly clear as to
what you want to do so the next programmer can open the code and maintain it
easily. Using shortcuts like these is lazy programming (yes, I am guilty of
it as well) that leads to ambiguous, unmaintainable code, bugs that get into
production when they should prevent the program from even compiling, and
prevents portability.
Writing questionable code because there is no other way to do what you need
to do is acceptable. Writing questionable code because you want to save a
few keystrokes is unequivocally unacceptable and can simply not be
justified.