May 13, 2008 at 6:44 am
When I copy my query from QA
and paste it in VIEW SQL pane
it changes it. Why is that? Does it mean the query
was not constructed properly?
SELECT COUNT(s.boxid)
FROM USFRET..scan s INNER JOIN USFRET..policy p ON s.client = p.clientcode
WHERE
(s.boxid = 'RET0018438')
AND (s.client IN ('GSK','MCP','JNJ','PCP','PCR','HOS'))
AND (p.deduct_ship = 'N' OR s.reason = 'RCL')
to:
SELECT COUNT(s.boxid) AS Expr1
FROM dbo.scan AS s INNER JOIN
dbo.policy AS p ON s.client = p.clientcode
WHERE (s.boxid = 'RET0018438') AND (s.client IN ('GSK', 'MCP', 'JNJ', 'PCP', 'PCR', 'HOS')) AND (p.deduct_ship = 'N')
OR
(s.boxid = 'RET0018438') AND (s.client IN ('GSK', 'MCP', 'JNJ', 'PCP', 'PCR', 'HOS')) AND (s.reason = 'RCL')
May 13, 2008 at 7:08 am
That's what the view / query designer in Enterprise Manager and Management Studio does. If it bothers you then don't use the designer. The designer has more than a few other limitations too. Many of us get on just fine without it.
May 13, 2008 at 1:33 pm
The graphical query designer in QA/Enterprise Manager has its own layout. A really, really annoying one that nobody has ever yet managed to find readable. Code you put in there or open in there will be reformatted (most of the time), and will end up being unreadable. I think it's a "feature".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2008 at 2:05 pm
SELECT COUNT(s.boxid) AS Expr1
In a view each column needs to have a name.
The querybuilder gui just puts Exprn if you don't provide one.
Regarding the layout, as already mentioned, it messes it up.
You're better off using aliasses and perform the layout of it yourself.
(altough it doesn't matter for sqlserver, but it is handy for us humans 😉 )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 13, 2008 at 2:11 pm
Keep in mind, the Query Designer needs to keep the textual and graphical representations of the query consistent. It then becomes logical that it will keep as specific a textual structure as possible.
May 13, 2008 at 2:33 pm
I've actually seen the graphical query designer break a working query by doing this kind of auto"fixing".
The query included a self-join on a table, with 7 iterations of the self-join.
When I wrote it, it worked just fine.
Then another developer opened it in the query designer in Enterprise Manager, and it rewrote it from a chain of seven self-joins, to seven separate self-joins.
From:
From Table1 t1_1
inner join Table1 t1_2
on t1_1.ID = t1_2.ParentID
inner join Table1 t1_3
on t1_2.ID = t1_3.ParentID
inner join Table1 t1_4
on t1_3.ID = t1_4.ParentID
... 7 tables in a row
To:
From Table1 t1_1
inner join Table1 t1_2
on t1_1.ID = t1_2.ParentID
inner join Table1 t1_3
on t1_1.ID = t1_3.ParentID
inner join Table1 t1_4
on t1_1.ID = t1_4.ParentID
...
Note that the left side of all the joins was changed to the first instance of the table, instead of the preceding instance of the table.
I tested it out, and it wasn't something the developer had done himself. All that had to happen was open the query in Enterprise Manager, and it re-wrote it. Found that MS Access would do the same thing to it if opened in the designer in there (probably they had the same code).
If I fixed the query in EM, and saved it, it showed on the screen correctly, and compiled back into junk (all joins to first instance), without telling me it was doing so.
That's why I never use those tools.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 13, 2008 at 2:56 pm
Unfortunately, I have seen it "break" queries as well. In addition, I have seen many instances where it cannot display the query correctly due to some usage of functions and variables that it can't sort out.
Because of this, I think I have used it more in the last day and a half in random fiddling for another post than I have in the last 5 years. 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply