Slightly Complex Data Ordering
Jul 18, 2006
I'm a SQL noob still and need some help figuring out a weird problem. I have directory information I am trying to pump out in the correct order, no big deal except some entries need to be categorized based on a simple hierachy. So for example, there is an entry for a company division, and then there are all the subdivisions, and then a few offices. So it goes like this:
Divsion=Computing
Subdivision=Networking
Subdivision=Development
Subdivision=Support
Office=Desktop
Office=Application
In other words, Desktop is an office in Support which is a part of Computing.
Now what I want is to basically pump all that data out via SQL in that exact order. So I have 3 fields that I am using and then trying to arrange it using Order By - except it doesn't come out correctly.
Code:
SELECT Division, Subdivision, Office
FROM directory
ORDER BY Division, Subdivision, Office
What happens with this is that I end up with all my Divisions in order only. So for example, the main entry for Computing has "Computing" as it's Division and Office, but I want it to appear first in the list so I set Subdivision to be "A" - Instead of it appearing first it appears second below an entry for a different Division that also has it's Subdivision set as "A". I end up with Divisions mixed into the wrong cluster of subdivisions and offices.
Anyone have any ideas? It seems like this should be fairly simple and yet somehow it isn't. Thanks in advance for any help!
View 7 Replies
ADVERTISEMENT
Jul 20, 2005
HOW TO ORDER THIS DATA.MONTH-YEAR10-200310-202011-20033 -20034 -20035 -20036 -20037 -20038 -20039 -2003THANX IN ADV.Join Bytes!
View 2 Replies
View Related
Oct 5, 2006
How I can ordering data in SQL EE by VB 2005 EE.
Thanks.
View 8 Replies
View Related
Jul 28, 2004
Hi frnds,
i need a help from all of u.
i will tell whats it ------ i have a webpage creating using ASP.NET,C# using SQL server 2000.
i have a table in the database which has attribute named "Priority" which has values like "High" "Low" "Medium". all i wana to make is that i enter any of this value tru a dropdown list into the database and i have used Datalist in Webpage so that i will retrieve back the info from the database.
when any request is made to retrieve back this value . i want to see all the priority field in the datalist which has priority as "High" in bold and in order. is this possible ,if it is plz let me know
thank u in advance
sunilk
View 2 Replies
View Related
Nov 1, 2006
It is one of the covenants of DBMS that data rows are retrieved in no particular order, and I have learned not to rely on that. However, I'd like to know under what circumstances could rows be returned, e.g., as a result of a simple query on SQL Server, not in the order they were stored. I have never seen this happen so far. I can't even test my sorting algorithms (on the data after it gets retrieved from the server) since it always comes already sorted. Naturally, I can always simulate this scenario (and I have) but this is kind of a "conflict of interest", I'd like to see "the real thing". Any ideas?
Kamen
View 4 Replies
View Related
Apr 16, 2008
In my search results I need to order my data by a particular entry rather than a particular column and then add all the other entries onto the end of the result set. Specifically I'm trying to order my records by a particular location in a city so those entries in that location appear first in the result set and then on the end I want to include all the other locations in that city.
I'm assuming I'll need to do these as separate queries and then use a UNION statement but I'm wondering if there is a better or easier way. Is there, for example, another type of ORDER BY clause that lets me order by a particular entry rather than a column?
Thanks
View 12 Replies
View Related
Nov 13, 2007
I want to select the below info from a sql 2005 db, to use it in a dynamic datadriven menu. Only problem, I don't know how to formulate my select statement. So here's the data
ID, TEXT HREF DESCR PARENTID
1
hMenu1
link1.htm
desc text
0
2
hMenu2
link2.html
desc text
0
3
sMenuM1
link3.htm
desc text
1
4
sMenuTSMenu3
link4.htm
desc text
3
I want the data structured so that below the row with id 1, would come it's subitems, subitems for the subitems and so on. So the recordset I'd like to return would in this case be:hMenu1
sMenuM1
sMenuTSMenu3
hMenu2
I've indented the data just to make things clearer. And finally, I want the substructure to be infinite at least in theory.I also do not want to use recursive logic to do this, if possible. I can change the structure of my table if neeed.Cheers!/Eskil
View 7 Replies
View Related
Jan 6, 2007
Hello,
I am new to SSIS.
I am trying to write a simple package to export data from some SQL 2005 tables and into a flat file.
In my data flow, I am using the OLE-DB data source and then the flat file destination.
This all works fine except that I cant get the package to write the columns out in the order I want. Even when I drive the OLE-DB source by a query, they columns are getting written to the flat file in a different order than I want.
How is SSIS determining what order to write the columns in and, more importantly, how can I change it to do it in the order I want?
Please help if you can. As mentioned I am new to SSIS so please give clear+simple answers.
Thanks
Mgale1
View 5 Replies
View Related
Sep 8, 2007
I was curious if using RAISERROR in the catch block of a stored procedure does actually causes some hit on performance? I think it would, as compared to simply returning an error code in this sp's output parameter.
View 1 Replies
View Related
Mar 23, 2008
In Connect To Server dialog, I have a dropdownlist of recently used server names. I am curious to know where these names are stored so that I can remove an invalid entry.
Thanks.
View 1 Replies
View Related
Feb 9, 2015
I have a query with a nested query that is used as an in line view. When I run the whole query I keep getting slightly different results each time (10000, 10002, 10001, 9999 etc.), nothing is being changed, no jobs are running on the DB to affect the tables etc.
When I run the inline view query with the nested query, or nested query on its own, it returns same number each time.
What could potentially be the cause of this?
Using SQL Server 2008 R2 Express Edition on local W7 PC
View 1 Replies
View Related
Jul 20, 2005
Hello All,I'm trying to find out exactly what JOIN doeseg.SELECT A.NameFROM Author A JOIN Publisher PON A.SomeID = P.SomeIDWHERE P.Country = 'X'I know what inner, outer, right and left joins do, but what does justJOIN on its own do? (Can't find it in help either)Thanks,K Finegan
View 3 Replies
View Related
Mar 13, 2007
Why, when I have SQL 2005 SP1 on Windows Server 2003 do I get, when I set up a new database WITH CONTENT IN IT, a polite message on the report builder informing me that:
"The selected datasource does not have any content associated with it."??!??!??!?!
When it quite clearly does because I can see it.
This appears to be an extension of the previous problem (that I am still encountering) wherein I can only see some but not all of the content of a database via ReportServer; but more - when I copy the content that I could see into the new database, I can NO LONGER SEE IT.
Please will somebody give some suggestions because this is starting to get ANNOYING!!!!!!!!!
View 2 Replies
View Related
Nov 12, 2003
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
View 7 Replies
View Related
Jan 4, 2007
I am creating an application which uses logic similar to J.D.Edwards (for those of you are familiar with its wildcarding data structure).
Basically, a customer purchases a particular house, with a particular elevation, in a particular community. This data is stored in a Customer table. From the customer’s selection (community, plan, elevation) criteria, I need to create a unique list of options from data stored in an optionmaster table.
A customer’s data looks similar to this in the customer table:
CustomerID
CommunityID
Community Name
PlanID
Plan Name
ElevationID
ElevationName
1234567
7
Hickory Hills
25
Allen
3
C
The Optionmaster table is structured like this (there are actually about 1000 records):
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
0
+
0
+
0
+
100
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optional Dormer
0
+
25
Allen
2
B
50
9125
Optional Tub
8
Smithville
0
+
0
+
800
9125
Optional Kitchen
0
+
0
Lori
0
+
2500
First. Based on the customer’s table I need to first select all options = to their community, as well as those available to communities everywhere (+). In some cases the same option is maintained for both at both levels. In that case the community specific option must be selected.
To do this I think I need an if else statement to select those records:
In this case, if CommID =7, select record, else select 0. This eliminates the other Smithville community and pulls the correct option # 1234.
The result would look like this:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optinal Dormer
0
+
25
Allen
2
B
50
9125
Optinal Kitchen
0
+
0
Lori
0
+
2500
Second, from this dataset, I need to select all options equal to the plan ID, and those which apply to all plans (+). So, the Lori Plan data goes away.
Something like, If the plan ID = 25, select record, else 0. the result would be:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optinal Dormer
0
+
25
Allen
2
B
50
Third and finally, based on that dataset, I would have to select any elevation specific options for that plan or options for all elevations of that plan (+).
Something like this: If the Elevation ID = 2, select record, else. 0. Note the elevation B data goes away.
The final result would be for this customer is:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
Any assistance anyone can provide in coming up with a sql statement to do this would be appreciated.
View 5 Replies
View Related
May 12, 2007
I want to export data to CSV that is nested.
eg.Poll 1,val1,val2
Question1,Q1val1,Q1val2
AnswerQ1A1,Q1A1val1,Q1A1val2, TallyQ1A1
AnswerQ1A2,Q1A2val1,Q1A2val2, TallyQ1A2
Question2,Q2val1,Q2val2
AnswerQ2A1,Q2A1val1,Q2A1val2, TallyQ2A1
AnswerQ2A2,Q2A2val1,Q2A2val2, TallyQ2A2
AnswerQ2A3,Q2A3val1,Q2A3val2, TallyQ2A3
Best practices-wise, what should my data end up like?
Since it's nested surely it can't be simply a list of identically-formatted rows, one for each answer:
Poll1,val1,val2,Question1,Q1val1,Q1val2, AnswerQ1A1,Q1A1val1,Q1A1val2, TallyQ1A1,Poll1,val1,val2,Question1,Q1val1,Q1val2, AnswerQ1A2,Q1A2val1,Q1A2val2, TallyQ1A2,Poll1,val1,val2,Question2,Q2val1,Q2val2, AnswerQ2A1,Q2A1val1,Q2A1val2, TallyQ2A1,Poll1,val1,val2,Question2,Q2val1,Q2val2, AnswerQ2A2,Q2A2val1,Q2A2val2, TallyQ2A2
Do I end up with three separate tables?
Poll1,val1,val2
Question1,Q1val1,Q1val2, Question2,Q2val1,Q2val2
AnswerQ1A1,Q1A1val1,Q1A1val2, TallyQ1A1,AnswerQ1A2,Q1A2val1,Q1A2val2, TallyQ1A2,AnswerQ2A1,Q2A1val1,Q2A1val2, TallyQ2A1,AnswerQ2A2,Q2A2val1,Q2A2val2, TallyQ2A2
If that's so, do I simply concatenate them in the final file? Or do I have three separate files? And do I label them uniquely such as Poll1Main, Poll1Questions and Poll1Answers so that I can export several polls side-by-side without overwriting each other?
View 1 Replies
View Related
Aug 14, 2004
I could really use some suggestions on the best way to get the results of a pretty complex aspx form. There are a whole bunch of search criteria. Most of the criteria are ranges of numbers such as a starting number and ending number but other criteria need to search text fields for keywords. Most fields are optional which means that many values will not be submitted. Not that there is a right and wrong way to do it, but what typically works out better for these things, an aspx procedure that puts together a highly complicated select statement or writing a highly complicated stored procedure? Either way I see a ton of if statements, or am I making this a lot harder then it needs it needs to be?
Thanks.
View 2 Replies
View Related
Oct 5, 2001
A point for general discussion maybe.
I've just read Jeff's query regarding passing an array to a stored procedure, and it seems to be a question that hits the message board quite frequently. There often seems to be the case where you have some block of data in one location, and want to process it somewhere else, and there just isn't an easy way to do it (or is there?). Passing a cursor is possible, but getting data back out of a cursor is painful.
The ways I have used in the past are:
Using a cursor
Using a global temporary table
Using a "permanent" table, but declared with a unique name (using a timestamp) and passing the tablename as a parameter.
Has anybody any suggestions for a future SQL release which we could suggest to Microsoft?
Suggestions from myself:
-Having a single TSQL command to copy a cursor back into a table, to make cursor parameters less cumbersome
-Being able to pass a pointer to a temporary table as an SP parameter
-Allowing variables to be declared with more complex structures (such as arrays, or classes)
Regards,
ChrisH
View 1 Replies
View Related
Feb 16, 2006
hey
is sql server able to handle complex data compared to oracle ,mysql or access
View 10 Replies
View Related
Sep 1, 2015
I have three tables and all three are linked. Looking for query so that I can get the desired result. Notes for Table_A and Table_B:
ROW Data are given in the variables to insert the Row data. If these Row data are already exist with the exactly same sequence in the row of table then don't need to INSERT data again.If these variable date doen't exist then need to add this row.
Notes for Table_C:
Seq_id_Table_A is a Seq_id of #table_A. Seq_id_Table_B is a Seq_id of #table_B.
--Table_A----------------------Variables for Table_A--------------------
Declare @table_A_Y char(4)='TRC'
Declare @table_A_Y1 char(2)='1'
[code]...
View 6 Replies
View Related
Feb 8, 2007
I am building a report that takes a web service as data source. This web service does not take primitive types as parameters. The signature is something like this:
<soap:Body>
<GetXml xmlns="myreports.ws">
<root >
<TP Name="string">
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
</TP>
<TP Name="string">
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
<B Name="string" Surname="string">
<SA xsi:nil="true" />
</B>
</TP>
</root>
</GetXml>
</soap:Body>
My question is: how do I pass the xml to the web service from reporting services.
I have seen examples with simple types. They do it with this query:
<Query>
<SoapAction>myreports.ws/GetXml</SoapAction>
<Method Namespace="myreports.ws" Name="GetXml"></Method>
<Parameters>
<Parameter Name="Parameter1">
<DefaultValue>some value</DefaultValue>
</Parameter>
</Parameters>
<ElementPath IgnoreNamespaces="True">
GetXmlResponse{}/GetXmlResult{}/diffgram{}/NewDataSet/Element{@Attribute1,@Attribute2}
</ElementPath>
</Query>
But there are no example on using xml directly instead of parameters. Can anyone help me? Where do I put my xml?
View 3 Replies
View Related
May 30, 2007
Hello All,
How do I get columns to output when I have a data reader source? My connection is an ODBC and does complex sql. I am connection to a Netezza database and I would like to execute a very complex query, but in essence does
Create newtable as
(select day, sessionId)
from source
// lots of other joins and unions
select day, sessionId from newtable
drop newtable
I have an ODBC connection and I have a Datareader source, I cannot connect this source to my SQL Server destination because no output columns are available. What am I missing here?
Are there any good examples of this, taking data from a ODBC source into SQL server?
Thanks in advance.
View 5 Replies
View Related
Oct 9, 2007
Hi,
Need a guidance on consuming the WCF service with complex type in the SSIS package.
I have a WCF service with complex type inside the complex type (Nested complex type) in the web method as an argument. When I try to use this WCF service in the SSIS web service task, I get an error "The web method has unsupported arguments".
I am able to consume the WCF service with the web method having Complex type and simple/prmitive type in side that as argument.
For example:
The web method in WCF service which accepts the argument as comlex type say "Employee" whose structure is:
Employee
{
FName String;
LName String;
Age int
}
It is possible to consume this WCF service and pass the arguments.
But when the Employee complex type is changed to have one more complex type in side it it give the above mentioned error. The Employee type is modified as:
Employee
{
FName String;
LName String;
Age String
Type EmployeeType;
}
EmployeeType
{
type string;
}
Now I get the error Web Method has unsupported arguments.
Can this be done in SSIS?
Virendra
View 3 Replies
View Related
Oct 30, 2007
Hi,
I'm trying to replicate a SQL join across two seperate data sources in SSIS. If I were to write SQL to do this, it would be as follows:
SELECT Costs.CostRateEntryId,
Costs.UserId,
Costs.HourlyRate * 8 AS DailyCostRate,
Dates.DateKey,
Dates.ActualDate,
FROM Costs
INNER JOIN Dates ON Dates.ActualDate >= Costs.EffectiveDate AND Dates.ActualDate <= Costs.EndDate
Unfortunately, as the tables 'Dates' and 'Costs' are in two seperate SQL2005 systems, I can't really do this. I was hoping that it could be achieved in SSIS, but I cant seem to find any way that I can do a join that's <= or >=.
Can anyone help?
Thanks
Jeremy
View 7 Replies
View Related
Mar 3, 1999
Hi there!
We are experiencing a problem using order clause in the statement below:
SELECT
person.pkey
, first_name
FROM
person
, private_person
WHERE
fkey_person = person.pkey
AND first_name = 'A'
ORDER BY
first_name
, person.pkey
Where person table contains the following columns:
pkey, name etc
and private_person
pkey, fkey_person (indexed), first_name (indexed)
The output is:
STEP 1
The type of query is SELECT
FROM TABLE
private_person
Nested iteration
Index : i$private_person$first_name
FROM TABLE
person
Nested iteration
Using Clustered Index
pkey first_name
----------- ---------------------
2000512 A
10994 A
2299 A
1097 A
1218 A
5133 A
1329 A
1387 A
1465 A
7532 A
5513 A
1884 A
512 A
591 A
(14 row(s) affected)
STEP 1
The type of query is SETOFF
Why SQL server does not order by pkey? Is there any information about it somewhere? Is it a bug or what?
If we are ordering by fkey_person everything is Ok. Can anybody help?
Thanks a lot!
Kind Regards,
Vladimir
View 4 Replies
View Related
Jun 18, 2007
I have a table named as C1_Messages with fields Id, Messages, Dates.
My Id filed looks like this:
1000
1001
1008
1009
1084
1093
1098 etc.
But here I need to make these Ids in a order, means it sould be in a consecutive order. Like
1000
1001
1002
1003
1004
1005 etc.
So is there anyway to do this method in SQL?
Regards
Shaji
View 5 Replies
View Related
Jun 27, 2006
Hello,
I have made sql table called costreallocation consists of two columsn:
RuleID varchar(10)
Type varchar(20)
I run the following three queries:
insert into costreallocation(RuleId,Type)values('aa','a')
insert into costreallocation(RuleId,Type)values('dd','d')
insert into costreallocation(RuleId,Type)values('cc','a')
then when i notice that these three records are not inserted as they are run:
In the table thn there are three records :
aa a
cc d
dd d
but i need to be filled in the table as they run as:
aa a
dd d
cc d
your help is highly appreciated
Best regards
View 2 Replies
View Related
Jan 26, 2007
I have an int field - that will hold number for an ordering system. It's a databound field to a textbox in a grid, I want the default to be empty, however not all fields are required so i may have 1-6 as bound values, and the rest should fall in line behind them - the only thing i can think of to do is default the values to 9999 so they come after 1,2,3,4,5,6 etc... I don't like seeing 9999 in my bound fields.
thanks for any suggestions you may have.
Jeff
View 1 Replies
View Related
Mar 5, 2001
Hello.
I want to order a resultset from a table by two columns in another table. Here's how it looks:
[Books]
ID
Title
[linkBookAuthor]
BookID (Books.ID)
AuthorID (Authors.ID)
[Authors]
ID
Firstname
Lastname
AuthorOrder
As you can see it's a many to many relationship. Is there a way to order the books by Authors.Lastname (the first if there are several authors) with one query ? If not, what is the fastest way ? I don't need the names in the resultset so I only want to order the books by Authors.Lastname, not include Authors.Lastname.
Thanks for any help.
View 1 Replies
View Related
Mar 20, 2001
Hiya!
I've got a little stored procedure:
CREATE PROCEDURE rICDCode @param1 char(15) = 'Description'
AS
SELECT DiagCode, ICD9ID, Description
FROM ICDCodes
ORDER BY @param1
and SQL is returning error 1008 - "The SELECT item identified by the ORDER BY number %d contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name."
I want to be able to use the same stored procedure for several different functions which all need the same rowset sorted differently. Any way to do this?
Thanks,
Sarah
View 2 Replies
View Related
Jul 11, 2005
Hi guys.
I want to create a top 20 product list from a few thousand products. I want the rest of the products to be grouped into 'others'...
I also want the products to be ordered by the facts in the cube. Thus the product dimension would dynamically change depending on the Time dimension thats being selected.
is this plausible ?
Thanks
Tom
View 1 Replies
View Related
May 16, 2008
Is it possible without using CASE statements, to order a result set by one field (if the second field return value is null) or another (if the second field return value is not null)
This would be on datetime fields.
Let's say I have 5 records with 2 date fields
sched_dt and arriv_dt.
sched_dt will always have a value, arriv_dt may or may not have a value.
In a single result set I would like to have the records with an arriv_dt sorted by arriv_dt and the ones without an arriv_dt sorted by sched_dt.
*Hope that makes sense*
Thanks
View 1 Replies
View Related
Apr 7, 2005
I have a table that has 4 dates for each record (birthday, anniversary, policy_start & policy_end). What I want to do is cycle through each record and then do some calculations using the gaps between the dates. So it
might be Calc1 using (birthday->anniversary), Calc2(anniverary->policy_start), Calc3(policy_start->policy_end), etc. BUT the dates might be in different orders (ie the birthday could come first - or the anniverary could, etc).
In VBA in Excel, I can use the SMALL function within my calcs and this returns the minimum date, the next smallest, the next & then the biggest. But how can I do this in SQL. I guess I can push the 4 dates out to a temp table, order it and then suck them back in - but I have no idea how to do this for all the records in my primary table
I've also tried using various WHERE statements (ie do Calc1 using Birthday & Anniversary WHERE Birthday < Anniversary and ........ - but the code gets awfully long.
Is it possible to write my own SMALL function which uses a bubble sort on the 4 dates & then returns the smallest, next smallest, etc
Any thoughts ?
View 2 Replies
View Related
