March 13, 2009 at 11:18 pm
foxjazz (3/13/2009)
Why couldn't they write t-sql more like C# or have a flavored compiler for t-sql so that we could work it like c# or vb depending on how you like it. (or the t-sql language which really is the worst language MS has ever put on the block). I really don't understand why the sql server group didn't do a better job of it, or is it because they were taking thier queue from ORACLE.Really stupid paradigm if you ask me, and I think it needs to be improved upon.
Heh... because the people that wrote it understood that most procedural programers shouldn't go anywhere near a database. 😉
--Jeff Moden
March 14, 2009 at 5:47 am
foxjazz (3/13/2009)
Why couldn't they write t-sql more like C# or have a flavored compiler for t-sql so that we could work it like c# or vb depending on how you like it. (or the t-sql language which really is the worst language MS has ever put on the block). I really don't understand why the sql server group didn't do a better job of it, or is it because they were taking thier queue from ORACLE.Really stupid paradigm if you ask me, and I think it needs to be improved upon.
We have a saying here in the UK: "A poor workman always moans about his tools".
If you don't like the tools, don't use them.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 14, 2009 at 11:08 am
Ok set this baby... Real world.
I have a table catagory fields are description, catID, typeID. parentID
another tables queryed with (make, model, yr, engine).
in the category table makes are typeid = 1, models typeid = 2, years , typeid = 3, engines, typeid = 4.
for each model, the parentid points to the make of the model. Yes it's hiearchical.
This is real world folks, do this in T-Sql without using a cursor.
And it doesn't matter whether identity insert is set to on or off, can be either way on the primary key (CATID).
If there is anything I left out, let me know.
March 14, 2009 at 11:17 am
Actually we are forced to use t-sql when doing such things as storedprocedures. Unless of course we write clr functions.
Not having the ability to use non-cursored rowbased loops is a big downfall. And I think it should be different in the future.
And without someone speaking up about the issues, nothing will get done. You all that use t-sql and don't code probably never know any difference. I know my dba wife doesn't.
And of course she is glad not to be a coder, and I don't blame her a bit.
I think one of the reasons they built SSIS, was so that we could do stuff a different way, and not worry about cursors.
So why don't they just build datatables into t-sql, it would do away with the cusor paradigm all together.
It's mainly because MS is built on separate camps, and they don't talk to each other in ways that could have made sql server easier to use and merging some of the languages.
March 14, 2009 at 11:27 am
foxjazz (3/14/2009)
Ok set this baby... Real world.I have a table catagory fields are description, catID, typeID. parentID
another tables queryed with (make, model, yr, engine).
in the category table makes are typeid = 1, models typeid = 2, years , typeid = 3, engines, typeid = 4.
for each model, the parentid points to the make of the model. Yes it's hiearchical.
This is real world folks, do this in T-Sql without using a cursor.
And it doesn't matter whether identity insert is set to on or off, can be either way on the primary key (CATID).
If there is anything I left out, let me know.
You didn't actually ask us to do anything. What do you want us to try and do with this?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 14, 2009 at 12:01 pm
You all that use t-sql and don't code probably never know any difference. I know my dba wife doesn't.
And of course she is glad not to be a coder, and I don't blame her a bit
I am a woman who wrote JAVA and now C# and also write SQL(T-SQL and PL/SQL) so your wife being a DBA that does not write code is based on her ability and not related to her sex or T-SQL or the DBA profession. I also moderate C# and most of the .NET forums on MSDN.
MCPD Web C#, MCITP BI and DBA and MCTS TFS
:Whistling:
I think one of the reasons they built SSIS, was so that we could do stuff a different way, and not worry about cursors. So why don't they just build datatables into t-sql, it would do away with the cusor paradigm all together.
SSIS was created to compete with Informatica ETL just as SSRS was created to compete with Crystal Report though Crystal Decision the original creator of Crystal Report was Microsoft partner.
It's mainly because MS is built on separate camps, and they don't talk to each other in ways that could have made sql server easier to use and merging some of the languages.
That is not relevant I write both C# and SQL at advanced level I never take any of the two for granted I buy books from experts as needed and spend time with both products. The teams work together that is the reason there is Visual Studio for Database projects only today. What you know about a language depends on your effort and resources. What is in SQL Server relational engine is not Microsoft's to change as needed because Microsoft is just another vendor. Take look at this connect request about MERGE statement the user did not know it is not Microsoft's to change, just as Microsoft cannot persist XML CDATA sections in XML column which in some cases contain six tables shopping cart.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=291266
Kind regards,
Gift Peddie
March 14, 2009 at 12:03 pm
Also be prepared for the possibility that the database design will be critiqued. If it turns out that the table structure is a poor design, the fault doesn't lie with T-SQL.
By the way, nobody here is your "baby." Language like that makes you sound like a whiny punk, even to those of us who tried to give you the benefit of the doubt. So far you've managed to impress no one with anything substantive. Whatever impression you've made is nothing to be proud of. Nobody really cares whether you like SQL or not. The only thing that's keeping this going is the professionalism of the volunteers who are still quite willing to show you how to do it right, despite your lame attitude.
Now, assuming you would actually like an answer to the question you neglected to type in, here is what I was able to put together from your sketchy description. Having worked on bill of materials processors in the past, I don't think what I'm seeing is correct. But I'm going to post it here to make it easy for you to correct and post back. Once we have a clear picture, then rational discussion can take place. Your choice.
/*
I have a table catagory fields are description, catID, typeID. parentID
another tables queryed with (make, model, yr, engine).
in the category table makes are typeid = 1, models typeid = 2, years , typeid = 3, engines, typeid = 4.
for each model, the parentid points to the make of the model. Yes it's hiearchical.
This is real world folks, do this in T-Sql without using a cursor.
And it doesn't matter whether identity insert is set to on or off, can be either way on the primary key (CATID).
*/
declare @types table (typeID int, typeDesc varchar(20))
insert into @types
select 1,'Makes' union all
select 2,'Models' union all
select 3,'Years' union all
select 4,'Engines'
declare @categories table (catDescription varchar(50), catID varchar(10) primary Key, typeID int, parentID int)
insert into @categories
select 'Pontiac', 1001, 1, 1 union all
select 'GTO', 1002, 2, 1 union all
select '1969', 1003, 3, 1 union all
select '400 V8 RamAir',1004,4,1
select * from @types
select * from @categories
produces:
[font="Courier New"]
typeID typeDesc
1 Makes
2 Models
3 Years
4 Engines
desc catID typeId parentID
Pontiac 1001 1 1
GTO 1002 2 1
1969 1003 3 1
400 V8 RamAir 1004 4 1
[/font]
[edited to include sample setup and additional comments]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 14, 2009 at 12:23 pm
foxjazz (3/14/2009)
Ok set this baby... Real world.I have a table catagory fields are description, catID, typeID. parentID
another tables queryed with (make, model, yr, engine).
in the category table makes are typeid = 1, models typeid = 2, years , typeid = 3, engines, typeid = 4.
for each model, the parentid points to the make of the model. Yes it's hiearchical.
This is real world folks, do this in T-Sql without using a cursor.
And it doesn't matter whether identity insert is set to on or off, can be either way on the primary key (CATID).
If there is anything I left out, let me know.
See the link in my signature below and post the table creation and data in that fashion, and we'll give it a try... without a cursor and without recursion.
--Jeff Moden
March 14, 2009 at 12:29 pm
foxjazz (3/14/2009)
Not having the ability to use non-cursored rowbased loops is a big downfall.
There is a way to do non-cursored row-based loops in T-SQL... the technique is called "pseudo-cursors" and it's nasty fast. It doesn't help with hierarchies, but it'll blow the doors off just about everything else for the likes of running totals, special ranking, Median calculations, and the creation of things that require numbered rows to function.
--Jeff Moden
March 14, 2009 at 12:48 pm
foxjazz (3/13/2009)
select name from mynametable where changedate > @yesterdayfetch from @sel into @name
while (@@fetch_status = 0)
begin
insert into nameother (name,changeddate) values (@name,getdate())
end
Do this without a cursor!
Heh... rant complete... regard all further alarms. 😉
--Jeff Moden
March 14, 2009 at 12:57 pm
Jeff Moden (3/14/2009)
foxjazz (3/13/2009)
select name from mynametable where changedate > @yesterdayfetch from @sel into @name
while (@@fetch_status = 0)
begin
insert into nameother (name,changeddate) values (@name,getdate())
end
Do this without a cursor!
Heh... rant complete... regard all further alarms. 😉
You know, the irony here is that I did a presentation about this last month and just submitted the first in a series of articles on it. In fact, the first example in in my presentation is logically identical to this "impossible" problem. The solution is there too. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 14, 2009 at 2:22 pm
BWAA-HAA!!! The real irony you speak of is that it obviously wasn't read by those that needed it the most! 😀
--Jeff Moden
March 14, 2009 at 4:18 pm
Ok, yes I did it that way before. Except you have to do it in 4 distinct query sets, and link the first results to the other set, and so on.
Ok, another problem:
Lets say you have a "product table with a descriptionID that links to a description table.
In another database, there is a product and description table.
This product table has lots of field in it,ProductID, productlineID, partnumber, searchnumber, upc etc... most of which we don't care about.
Partnumber and productlineid are unique. productid is the uniqueKey.
The only productlineid we are worried about in this exercise is where it is 1 all the others can just be there for other reasons.
in the first table we say select partnumber, description and get a set of rows.
38101, belt
7404, 'drive align tensioner'
These descriptoins may change over time and lets say they do change but te changes are small (like instead of 'drive align tensionser' its now just 'tensioner'.
so the first goal is to find all the changed items from the first table, which is simple using a outer join, where product is null.
So we have a few partnumbers that are listed (which are already in the product table, so no inserts are necessary).
select partnumber, description from FIRSTTABLE would be the first query
now for each partnumber you receive, you have to update the descriptionID using the productID so the structure looks like this:
table = productdescription
descriptionID int,
description nvarchar(50)
table = product
productid int, primarykey
partnumber nvarchar(50),
productlineid int,
descriptionid int,
other fields of other uses...
so this would be a valid query.
select @productid = productid, description from product inner join productdescription on product.descriptionid = productdescription.descriptioinid
where partnumber = '38101'
and this would be legal.
Update product set descriptionid = @descriptionid where productid = @productid
however you have to check the descriptiontable to see if there is a valid description, if not you have to insert one.
select @cnt = count(*), @descriptionid = descriptionid from productdescription where description = 'tensioner'
if @cnt = 0
begin
insert productdescription(description) values ('tensioner')
update product set descriptionid = @@identity where productid = @productid
end
else
begin
update product set descriptionid = @descriptionid where productid = @productid
end
This is a real world problem that can't be done with set statements, you have to run a cursor because you can't make more than one primarykey update to the table to make it have the correct description with one update.
March 14, 2009 at 4:22 pm
wrong...
typeID typeDesc
1 Makes
2 Models
3 Years
4 Engines
desc catID typeId parentID
Pontiac 1001 1 1
GTO 1002 2 1
1969 1003 3 1
400 V8 RamAir 1004 4 1
the results should look like this:
Pontiac catid 1001, typeid 1, parentid 1
GTO 1002, 2, 1001
1969 1003, 3, 1002
400 V RamAir 1004,4,1003
The parentID has to be the parent, not 1.
and yes you can do inserts to accommodate this table by linking the results of the first insert in your second query, the results of the second to the third and third to fourth.
Which is really an easy problem.
March 14, 2009 at 4:43 pm
I am still waiting for you to actually ask a question. These incoherent ramblings do not constitute a question. You don't even bother to indicate which previous post you are replying to.
If you find that you are having too much difficulty organizing your thoughts for a straigh-forward question, then please do as Jeff asks and read his article and submit a question in that form.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 46 through 60 (of 465 total)
You must be logged in to reply to this topic. Login to reply