Basic Query Help - Revisited
Jul 14, 2001
I'm not getting it. The query below "e.g." is exactly as I think it should be except for the <Join Clause> as there is only the match on site name. I tried joing on the site name, but only got the sites in the log. I want all the site names even if they don't have a log item for a given date range.
I'd post my real query if it would help, it's just that I'm chopping up some strings and converting some time stamps to dates as varchar for readability for the final report, which is an MSExcel pivot table.
My original post lies below.
TIA
jEfFp...
Reply...
Date: Basic Query Help (reply)
RickD (Rick.Davis@Schroders.com)
7/11/01 6:41:26 AM
Make log an outer join to site.
e.g
select s.name, l.ondate from
site s
RIGHT OUTER JOIN log l ON <Join Clause> where
substring(id,3,6) in(select name from site)
and ondate between getdate()-8 and getdate()-1
You didn't seem to have any logical join between the tables, but i'm sure you have, just put this in the <Join Clause>.
Oh, and read BoL, it helps no end.....
------------
Original Post...
Jeff Proctor at 7/10/01 11:18:31 PM
my 7 day query....
select s.name, l.ondate from
site s, log l where
substring(id,3,6) in(select name from site)
and ondate between getdate()-8 and getdate()-1
This returns those sites that exist in the current log, however what I want is a result set that has a row for each name regardless if they are in the log.
I have 2 tables...
table1 sites
name
smithj
anandt
burtot
proctj
etc....
table2 log
id ondate
02anandt 07/01/01
02anandt 07/03/01
02anandt 07/03/01
01smithj 07/03/01
03burtot 07/05/01
01smithj 07/05/01
03burtot 07/05/01
etc.....
View 1 Replies
ADVERTISEMENT
May 8, 2007
I have searched the archive of this forum, and based on some previous answers have read the followin FAQ
http://www.gotreportviewer.com/
However, even so, I still have a questoin about when to use RDLC vs RDL from the point of view of licensing and load sharing betwee DB and report server.
1. As we all know that SSRS will take a license of SQL Server if the web service is deployed on any other machine other than SQL Server (which is normally the case because no serious DBA will allow IIS apps to be installed on the DB Server). Also, if the SSRS is deployed on NLB cluster then each node of the cluster would need a "SQL Enterprise" license. (which can be very expensive).
2. Co deploying the SSRS with SQL Server is a bad idea because it puts excessive load on the DB+Report Server.
3. Since there is no "load balancing" in SQL all the rendering has to be done by the SSRS service which is running on the DB Server.
Now compare this to RDLC
1. RDLC gives me the same engine as RDL
2. However it has no licensing tags to it.
3. I can deploy ASP.NET report viewer on my web farm and scale it out as much as I like.
4. Extract data from the DB using stored procedures.
So it seems that RDLC solution is cheaper, gives better scale out capabilities and also moves the rendering of reports on the web farm rather than the Report Server.
Why should me or anyone else choose RDL at all?
There seems to be one disadvantage that the data has to fetched programmatically and manually binded (like an ASP.NET app) as RDLC does not provide any feature to connect to data sources. But this is not much when you see the cost and scalability benefits.
regards,
Abhishek.
View 6 Replies
View Related
Mar 16, 2004
I am new to this but here goes.
I have a sqlserver with asp.net.I have a stored procedure in the database
named Try_Login .I need to know how and what is the best way to accomplish my task.I need the data in the stored procedure after it runs.Do I use the ExecuteReader() Method or how do I go about retrieving these values?????
CREATE procedure dbo.Try_Login
(
@LoginName nvarchar(15),
@Password NvarChar(15)
)
as
select
UserName,
UserPassword,
UserClinic,
UserTester
from
Clinic_users
where
UserName = @LoginName
and
UserPassword = @Password
What I need to know is how to get my code to correspond with it.I.E. I have a login in form...
txtUsername and txtPassword need to be passed to this Stored procedure.
I assume you use .value to obtain thier values.Here is my code so far......
Public Sub LoginDB()
Dim conLogin As SqlConnection
Dim cmdLogin As SqlCommand
Dim dtrLogin As SqlDataReader
conLogin = New SqlConnection ("Server=myserver;database=APPOINTMENTS;uid=XXXX;pwd=XXXX")
cmdLogin = New SqlCommand("Try_Login ", conLogin)
cmdLogin.CommandType = CommandType.StoredProcedure
cmdLogin.Parameters.Add("@LoginName",txtUsername.Text)
cmdLogin.Parameters.Add("@Password",txtPassword.Text)
dtrLogin = cmdLogin.ExecuteReader()
While dtrLogin.Read()
End While
End Sub
I have no clue why it does not work..........
View 1 Replies
View Related
Feb 16, 2000
I keep getting this error when tring to log in to a SQL 7 db from a client application written in VB6.
The text reads:
Login failed for user 'rmuser'. Reason: Not associated with a trusted SQL Server connection.
I have the account setup in SQL security and I have the account listed in the database and I'm using named pipes on a common domain. I'm running out of options. The network is a mix of Novell and NT. Please help
View 1 Replies
View Related
Nov 20, 2000
hi,
I guess my brain isn't functioning on monday morning. I am trying to do a convert with little success.
what I want to do is insert a character string into a smallint column of a table. i.e.
DECLARE @a char(6)
select @a = 'string'
I have tried the following and variations on it, but get a syntax error
insert table testtable (1,convert(smallint,@a))
The columns in testtable are both smallint datatypes.
Thanks in advance
-- I got this response, but seem to get a convert error even
when using the below example
Try this :
DECLARE @a char(6),
@b smallint
select @a = 'string'
select @b = convert(smallint, @a)
insert testtable values (1, @b)
any suggestions?
View 2 Replies
View Related
Mar 11, 2007
I have a database with a date of birth. From this, I need to select records that include a large number of variables including records where an individual is older than a selected age and/or younger than a selected age, and even worse, of a given astrological sign if one is selected. I'm currently using a strongly typed dataset. Is this something that's do-able with a dataset, or should I build the select string on the form programmatically? I have an ASP site that I'm re-writing in ASP.NET 2.0. In the ASP site the select string includes the code below. I just don't see how I can do this in a dataset.if (strLowerAge <> 18) or (strUpperAge <> 99) then If (strLowerAge <> "") Then strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ")" End If If (strUpperAge <> "") Then If (strSQL <> "") Then strSQL = strSQL & " AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")" Else strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")" End If end if End If If (strAstSign <> "") Then Select Case strAstSign Case "Aries 3/21 - 4/19" If (strSQL <> "") Then strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) < 20 ))" Else strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m, [DATE OF BIRTH]) = 3 AND DATEPART(d, [DATE OF BIRTH]) > 20 ) OR (DATEPART(m, [DATE OF BIRTH]) = 4 AND DATEPART(d, [DATE OF BIRTH]) < 20 )" End If Case "Taurus 4/20 - 5/20" If (strSQL <> "") Then strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))" Else strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 4 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) < 21 )" End If Case "Gemini 5/21 - 6/20" If (strSQL <> "") Then strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))" Else strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 5 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) < 21 )" End If Case "Cancer 6/21 - 7/22" If (strSQL <> "") Then strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))" Else strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 6 AND DATEPART(d,[DATE OF BIRTH]) > 20 ) OR (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) < 23 )" End If Case "Leo 7/23 - 8/22" If (strSQL <> "") Then strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))" Else strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 7 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) < 23 )" End If Case "Virgo 8/23 - 9/22" If (strSQL <> "") Then strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))" Else strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 8 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) < 23 )" End If Case "Libra 9/23 - 10/22" If (strSQL <> "") Then strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) < 23 ))" Else strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 9 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) < 23 )" End If Case "Scorpio 10/23 - 11/21" If (strSQL <> "") Then strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) < 22 ))" Else strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 10 AND DATEPART(d,[DATE OF BIRTH]) > 22 ) OR (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) < 22 )" End If Case "Sagittarius 11/22 - 12/21" If (strSQL <> "") Then strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) < 22 ))" Else strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 11 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) < 22 )" End If Case "Capricorn 12/22 - 1/19" If (strSQL <> "") Then strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) < 20 ))" Else strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 12 AND DATEPART(d,[DATE OF BIRTH]) > 21 ) OR (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) < 20 )" End If Case "Aquarius 1/20 - 2/18" If (strSQL <> "") Then strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) < 19 ))" Else strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 1 AND DATEPART(d,[DATE OF BIRTH]) > 19 ) OR (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) < 19 )" End If Case "Pisces 2/19 - 3/20" If (strSQL <> "") Then strSQL = strSQL & " AND ((DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) > 18 ) OR (DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) < 21 ))" Else strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DATEPART(m,[DATE OF BIRTH]) = 2 AND DATEPART(d,[DATE OF BIRTH]) > 18 ) OR (DATEPART(m,[DATE OF BIRTH]) = 3 AND DATEPART(d,[DATE OF BIRTH]) < 21 )" End If End Select END IFDiane
View 3 Replies
View Related
Nov 10, 2003
Greetings. ASP.NET neophyte seeking a bit of advice. I am looking for the specific syntax that allows me to pass a local variable into an open query within SQL 2000 for a linked server, such as:
SELECT [ticketno] from OpenQuery(JWS_GR, 'select ticketno from tksmisc where itemno = 1 AND ticketno = @scaleticketno')
I included the variable @scaleticketno here where I was using an actual value. This doesn't work, returning the eqivalent of a database 'DUH?' message because the linked server database engine (Pervasive SQL 2000i) has no idea how to treat @scaleticketno. (Nor do I.) Any thoughts, suggestions or recommendations would be much appreciated and regarded with eternal gratitude.
View 1 Replies
View Related
Oct 2, 2007
Building on the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2205669&SiteID=1 which Jessica Moss posted the code below:
Code Block
select u.UserName, c.Path, r.RoleName
from ReportServer.dbo.PolicyUserRole pur
left join ReportServer.dbo.Users u on pur.userID=u.userID
left join ReportServer.dbo.Roles r on pur.roleID=r.roleID
left join ReportServer.dbo.Catalog c on pur.policyID=c.policyID
order by u.UserName, c.Path, r.RoleName
I tried to take it one step further and add a parameter where I could just type in the users Active Directory login id, thus limiting the result set. I also wanted to avoid having to type in the domainnameuserid for each parameter and only wanted to type the userid. So I changed the code (see below) and added a multi-value parameter called users.
Code Block
select u.UserName, c.Path, r.RoleName
from ReportServer$SQL2005DSS.dbo.PolicyUserRole pur
left join ReportServer$SQL2005DSS.dbo.Users u on pur.userID=u.userID
left join ReportServer$SQL2005DSS.dbo.Roles r on pur.roleID=r.roleID
left join ReportServer$SQL2005DSS.dbo.Catalog c on pur.policyID=c.policyID
where u.Username IN('DOMAINNAME' + @Users)
order by u.UserName, c.Path, r.RoleName
Unfortunately, if I type:
Domainnamejblack
Domainnamejdoe
Domainnameefranks
I only get a return for the first userid entered. Any ideas?
View 4 Replies
View Related
Oct 19, 2006
Hi all,I am using the following stored procedure in SQL 7 to return a range of records.CREATE PROCEDURE spRetMyTable@maxRows int, @lastRecord int, @SortPhrase varchar(122)ASbeginDECLARE @stSql VARCHAR(255) SET ROWCOUNT @maxRowsSET @stSql = 'SELECT * FROM (SELECT TOP 10 colPrimary, col1, col2, col3 FROM (SELECT TOP ' + CONVERT(VARCHAR, @lastRecord) + 'colPrimary, col1, col2, col3 FROM [MyTable] ORDER BY [colPrimary] ASC) AS tbl1 ORDER BY [colPrimary] DESC) AS tbl2 ORDER by [colPrimary] ASC'exec(@stSql)set rowcount 0endTo execute the above procedure I am issuing the command:execute spRetMyTable @maxRows 10, @lastRecord 1500The column [colPrimary] is the primary field in the table.I will be using the selected records in a gridView with paging (3 records per page). So if the next page is selected I would require to take out record 1491 to record 1494 and Add record 1501 to record 1503 in the datatable subject to the availability of records in the table MyTable.How do I provide sorting by col1, col2 or col3? It could be single column or multiple column sorting as chosen by the user during runtime.Is there any performance overhead in the above method in the first place?Could it be improved by any alternative process?I need YOUR ASSISTANCE to cater to the above issues in a feasible way.Thanks in advance.
View 2 Replies
View Related
Jul 14, 2007
Suppose we get two following tables:
Table A: [USER_ID] [varchar] (11) NOT NULL , [COURSE_ID] [varchar] (11) NOT NULL ,
Table B:
[COURSE_ID] [varchar] (11) NOT NULL , [COURSE_NAME] [varchar] (50) NOT NULL , [COURSE_NO] [varchar] (15) NULL , [BEGIN_DATE] [datetime] NULL , [END_DATE] [datetime] NULL , [CREATER] [varchar] (11) NOT NULL ,
and during the execution of my program, I can get the current use's id (USER_ID), say U0001.
How can I retrieve the result set containing [COURSE_NAME], [COURSE_ID], but the current user's id (U0001) have Not been assigned in Table A.
Thanks in advance.
Ricky.
View 3 Replies
View Related
Aug 7, 2013
I am trouble getting the count of applications. In the below query MerApp.ApplicationID represents the applications. I'm trying to get a results of a count of applications for each sales team member.Before adding the COUNT() function. My query results show 3811 because it shows the sales team member numerous times because he has many applications with different IDs. For example sales team member A shows up in 75 rows because he has 75 different applications assigned to him.
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
WHERE MerApp.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
I want sales team member A to show up once with the count of applications to be 75 because that's how many are assigned to him.
This didn't work for me:
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, COUNT (MerApp.ApplicationID) AS [Applications], stm.UserID, MerApp.ActiveStatus
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
WHERE MerApp.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
It didn't count the number of applications for each person.I have about 14 different sales team members. So I'm trying to have a query that produces 14 rows not 3811 rows
View 5 Replies
View Related
Nov 23, 2005
ARRRRRRRGGGGGHHHHH!!Please can you help, I'm going round the bend with this.I have a simple and small table called STOCKCATS, which I need to query toget back a dataset in a particular order, but although it looks simple Ican't get it to work. My table schema plus sample data to see the problemis as follows:DROP TABLE IF EXISTS `STOCKCATS`;CREATE TABLE `STOCKCATS` (`CATID` varchar(30) NOT NULL default '',`LEVEL` varchar(30) default NULL,PRIMARY KEY (`CATID`),KEY `indxCATEGORYID` (`CATID`));INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('A001',''),('A002','A001'),('A003','A001'),('A004','A001'),('A005','A001'),('PCHW01',''),('MHW01',''),('FD01',''),('ELEC01',''),('MHW02','MHW01');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('MHW03','MHW01'),('MHW04','MHW01'),('MHW05','MHW01'),('PCHW02','PCHW01'),('PCHW03','PCHW01'),('PCHW04','PCHW01'),('PCHW05','PCHW01'),('PCSW01',''),('MSW01',''),('C001',''),('C002','C001'),('C003','C001'),('MV',''),('SUZ','MV'),('ALF','MV'),('PLASMA','ELEC01'),('T01','ELEC01'),('HEATING',''),('RAD','HEATING'),('P01',''),('B01','P01'),('BB','HEATING'),('FS','HEATING'),('WM','HEATING'),('AEROSOL',''),('SOLVENTS','AEROSOL'),('DGC','');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('DGXWINDOWS','DGC'),('DGXEXTRA','DGC'),('DGXCON','DGC');As you can see from the table structure, this table consists of 2 fieldvalues. The 1st is the category code and the 2nd is the level is at. If acatid has a level of nothing, eg '', then it means that it is a root levelcategory. If a catid has a another cat's catid in it's level, eg B01 hasP01, then it is a sub-category of this category, eg B01 is a sub-cat of P01.All I want to do is query this table and bring back the data so thatalphabetically it goes root level cat A1, then all the sub-cats for thisroot level, then root level A2, then all sub-cats for this root level and soon. An example using the above would be as follows:^ ^ A to G of root level cats plus their sub-cats....HEATING << root levelBB << sub-cat of heatingFS << sub-cat of heatingWM << sub-cat of heating/ / I to Z of root level cats plus their sub-cats....A few posters kindly gave me a solution of ORDER BY COALESCE(CATID,LEVEL),CATID and I thought this had done it, but I was looking at the ('A001',''),('A002','A001'), ('A003','A001'), ('A004','A001'), ('A005','A001')entries as these naturally fell into place. If you use this order commandon the above you will see that ('P01','') and it's associated ('B01','P01')sub-cat just don't come together.Does any body have any ideas?ThanksLaphan
View 6 Replies
View Related
Jul 20, 2005
Ok, I'm a beginner so forgive me for my ignorance. Could someone helpme with this?tbl_x has two fields xid and xlistxlist being a list of numbers.....1, 5, 6, 8i want to create a new table from tbl_x that converts each number inthat xlist into a row in tbl_newso for exampletbl_x has 3 rowsxid xlist1 1, 4, 52 2, 3, 73 2, 1, 7i need a query or sql script that will convert that table with a listinto the following....tbl_newid xid xlid1 1 12 1 43 1 54 2 25 2 36 2 77 3 28 3 19 3 7
View 2 Replies
View Related
Nov 20, 2007
Hi I am having two very basic problems which are very frustrating.
I have read http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx and changed my DB to "Copy if newer" however when I use the connection string "|DataDirectory|MyDB.sdf" MyDB remains unchanged. (If I put the full path to MyDB in the connection string I will see the changes.) I have tried copying the DB file in the bindebug directory and opening the copy separately, and the changes still do not appear.
This leads me to my second issue. While trying to verify if the insert worked, I am trying to query the DB for the row I just inserted. I have found very little documentation describing how to do this; am I missing something/someplace obvious? Here is my code:
string strCommand = "SELECT * FROM Log WHERE Severity = @Sev AND Message = @Msg";
SqlCeResultSet resultSet = null;
SqlCeCommand sqlCommand = new SqlCeCommand(strCommand, pConn);
sqlCommand.Parameters.Add(SevParam);
sqlCommand.Parameters.Add(MsgParam);
try
{
pConn.Open();
resultSet = sqlCommand.ExecuteResultSet(ResultSetOptions.None);
}
finally
{
pConn.Close();
}
return !(resultSet == null);
resultSet always returns non-null, so I am clearly doing something wrong. Do I need to "read through" the resultSet to make sure there is something there? And if so, how? Should I be using a DataReader instead? (and if so how?) Am I going in the wrong direction? Just to be clear, I want to insert a row into my DB and then check that that row has been inserted by querying the DB.
Thanks!
=Spencer Whitman
View 1 Replies
View Related
Apr 28, 2006
Ok, I have a table that contains a number of columns, one of these columns contains a 'unitref' e.g.AC02/001D.
I import a new set of records, approx 7,000 per week in a DTS package from CSV Flat File into the table.
What I need to achieve at either the point of import of new data weekly, or once the new data is sitting in its final resting home, is a copy of the first two 2 Chars of the UnitRef, in the example above, this would make it 'AC' and then place that in a column named 'site_ref'.
Having posted the question on this forum relating to grabbing the first two chars of a value and placing them in a temporary table by utilising the Left(field,2) command in SQL (Kindly answered by CryptoKnight), I was wondering how I can do this possibly by using the inesrt into type command. I have many columns that get imported this is only a tiny step of many things that ideally would need to happen on an import,
Regards
View 1 Replies
View Related
Jan 28, 2005
How would I convert this query to ANSI 92 standard?
select p.InternalID,
n.LastName
from tblPatient p,
tblName n
where p.PatientID = n.EntID and
n.EffDt = (
select max(n2.EffDt) from tblName n2 where n.EntID = n2.EntID and n2.EffDt <= getdate()
)
View 1 Replies
View Related
Apr 29, 2008
hi
i am learning SQL basic commands ( like , where, having, group by, order by , join ,and simple sub-query) and I got one exersice I cannot solve
table RECIPE has 3 columns , ( pizza ,ingredient,amount)
each pizza could use 1 / many ingredient, each ingredient could be used by 0 / 1/ many pizza, amount means the quantity of that ingredient used in certain pizza
question:
(List the ingredients, and for each ingredient, also list the pizza that contains the largest amount of this ingredient.)
============================
Raw data :
recipe
======
pizza ingredient amount
--------------------------------
margarita spice 5
margarita cheese 120
ham ham 150
ham spice 5
napolitana anchovies 100
napolitana olives 75
napolitana spice 10
hawaiian ham 100
hawaiian pineapple 100
hawaiian spice 5
cabanossi cabanossi 150
cabanossi spice 10
siciliano onion 50
siciliano capsicum 75
siciliano olives 50
siciliano anchovies 50
siciliano spice 15
americano salami 120
americano pepperoni 75
americano spice 10
mexicano onion 75
mexicano capsicum 75
mexicano mushroom 50
mexicano chilli 25
mexicano spice 20
seafood seafood 200
seafood spice 5
garlic garlic 25
garlic spice 10
vegetarian onion 50
vegetarian capsicum 50
vegetarian mushroom 50
vegetarian peas 50
vegetarian tomato 50
vegetarian spice 5
mushroom mushroom 100
mushroom spice 5
special cheese 25
special tomato 25
special ham 25
special anchovies 25
special olives 25
special mushroom 25
special bacon 25
special egg 25
special pineapple 25
special cabanossi 25
special salami 25
special capsicum 25
special onion 25
special peas 25
special seafood 25
special spice 10
stagiony ham 75
stagiony mushroom 50
stagiony olives 50
stagiony anchovies 25
stagiony spice 10
====================================================
Query Results needed:
ingredient pizza amount
------------------------------
anchovies napolitana 100
bacon special 25
cabanossi cabanossi 150
capsicum mexicano 75
capsicum siciliano 75
cheese margarita 120
chilli mexicano 25
egg special 25
garlic garlic 25
ham ham 150
mushroom mushroom 100
olives napolitana 75
onion mexicano 75
peas vegetarian 50
pepperoni americano 75
pineapple hawaiian 100
salami americano 120
seafood seafood 200
spice mexicano 20
tomato vegetarian 50
=============================================
Thanks
View 10 Replies
View Related
Aug 19, 2005
Hi,I feel stupid for posting this question but I cannot find out how to create an SQL statement to return records that have been added today.My database table has a field called calldate which hold the date and time of the record added to the database i.e. "02/08/2005 16:55:41".Please can someone let me know how I can search to find all records added today. I thought it would be something like it, but I assume I need to format the calldate so it's just 02/08/2005.Select *From TableWhere CallDate = GetDate()ThanksBrett
View 9 Replies
View Related
Apr 19, 2004
I am having some difficulty writing a relatively basic query. The objective is to retrieve the new stories (headlines) for the past 3 days from the database. Since each headline can be assigned multiple categories (topics) the query returns a row for every headline assignment. I can't use the 'Group By' expression because one of the columns is nText.
So basically if there is an article written yesterday, "I Love Cats" that gets assigned both topics 'CATS' and 'PETS' I only it returned with the first topic assigned... 'CATS'. Here is a little image of the three tables being called:
http://64.225.154.232/temp_dbDiagram.gif
I don't think that this query is too difficult, but I'm just getting my feet wet with writing queries that are more than select * from whatever. Any insight or recommendations are greatly appreciated.
SELECT headline.HEADLINE_ID, headline.HEADLINE_TITLE, headline.HEADLINE_DATE, headline.HEADLINE_THUMBNAIL,
topic.TOPIC_NAME, topic.TOPIC_URL
FROM tbl_CCF_Headlines headline INNER JOIN
tbl_CCF_Headlines_Topics ON headline.HEADLINE_ID = tbl_CCF_Headlines_Topics.HEADLINE_ID INNER JOIN
tbl_CCF_Topics topic ON tbl_CCF_Headlines_Topics.TOPIC_ID = topic.TOPIC_ID
WHERE (headline.HEADLINE_DATE IN
(SELECT TOP 3 HEADLINE_DATE
FROM tbl_CCF_HEADLINES
GROUP BY HEADLINE_DATE
ORDER BY HEADLINE_DATE DESC))
ORDER BY headline.HEADLINE_DATE DESC
View 2 Replies
View Related
Jun 13, 2006
In the tutorial Creating a Basic Package Using a Wizard > Lesson 1: Creating the Basic Package >
says to use the following sql statement on the query page:
SELECT * FROM [Customers$] WHERE NumberCarsOwned > 0
When I paste the query in I get the message :
This SQL statement is not a query.
Does anyone have any suggestions? The input and output are set up correctly and I have the sample excel file Customers.xls.
I am new to all this, is there some setting I need to change for the tutorial to work or..?
FYI I have installed Sql 2005, Sp1.
View 4 Replies
View Related
Mar 18, 2005
This is probably a very silly question.I started learning ASP.net by following ASP.NET Unleashed. I am stuck where he wants me to open a connection to SQL Server database. I have just downloaded
MSDE. But I dont know where to type this code and how to run it..so as to connect to the database.
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load
Dim conPubs As SqlConnection
conPubs = New SqlConnection( "server=localhost;uid=webuser;pwd=secret;database=pubs" )
conPubs.Open()
End Sub
</Script>
Connection Opened!
Now do i have to change the uid to SA ? (i had to assign one when i downloaded and installed MSDE?
Thanks for the help.
View 1 Replies
View Related
Nov 16, 2005
Hi all,
am not very experienced in using DTS and really need your help. I have a dts package that i have scheduled to run every day. Here's what i want the package to do:
1. Check whether a value for a certain column in a certain row of a table in my database is 0 or 1. If it is 1, then
2. Run the dts task (which i have created and is working)
In other words, when the package is started, i want to execute a stored procedure or sql task or whatever, and if that returns 1 then i want to continue, if it returns 0 i want to finish the package without running the dts task. I'm sure there's a simple way to do this, but i could use your help...!
Thanks,
Elisabet
View 1 Replies
View Related
Jul 11, 2007
Hi All,
Can this be done and if so can you give a bullet list of the steps need to accomplish this.
I need to load a bunch of files into a stagging table. Need to loop through the files and load them.
Thanks,
Michael
View 3 Replies
View Related
Mar 12, 2008
Hi,
what this statement do?
does it add all the values or combine all the values.
REPLACE combine WITH lc_tran + lc_exp + lc_war + ll_boc
Regards
kk
View 1 Replies
View Related
Feb 19, 2007
I downloaded SQLExpress and Visual Studio Express to my home computer.
I built a simple database, adding data through theSQLexpress admin tool.
I built a web page using MS Studio. I connected to the database and used the webpage for a few days. Then I restarted the computer. Now the web page won't open, and MS Studio won't open the MDF file in the App_Data folder.
I can still see and work in the database through SQL server Express.
The web page and the MSStudio attempt to connect to the mdf file both fail with this message:
Cannot open user default database. Login failed.Login failed for user 'KAAAK/Administrator'.
So it seems to be trying to connect as the Windows user.
When I try to modify the connection to connect through a user/password I created in SQL manager, I get a message that the user is not a trusted SQL user.
from web.config:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|info.mdf;Integrated Security=True;User Instance=True;User ID=Admin;Password=12345" providerName="System.Data.SqlClient"/>
</connectionStrings>
That was changed from the original string created automatically by MS Studio
<connectionStrings>
<add name="stocksConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|stocks.mdf;Integrated Security=True;User Instance=True;" providerName="System.Data.SqlClient"/>
</connectionStrings>
I am sure this is some simple problem, but why would the system refuse to access an mdf file it had already been accessing.?
Thanks, Michael
View 1 Replies
View Related
Mar 5, 2007
Hi all, having trouble with my first sql communication. I've got hosted service with an SQL database i've populated with a row.
When it gets to the third line the page crashes with an error.
SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;");// +"Integrated Security=True"); SqlCommand cmd = new SqlCommand("SELECT UserName FROM Users",connection); SqlDataReader reader = cmd.ExecuteReader();
is there somewhere i need to put in my username or password? or is this code just wrong
Many thanks burnside.
-- Edited by longhorn2005
View 7 Replies
View Related
Jun 8, 2007
not sure why I am having trouble here but I am using the following WHERE clause expecting to find all rows where any one of the the three keywords are present.
....WHERE Company.L_Keywords LIKE '%metal%' AND Company.L_Keywords LIKE '%tile%' AND Company.L_Keywords LIKE '%ceramic%'
however it appears to finding only the rows where all three words are present in the L_Keywords field
View 2 Replies
View Related
Jul 25, 2007
This is a very simple question. How would a select satement be formated in the following example.
SELECT Grade, Student_ID, First_Name, Last_Name FROM Scores WHERE (This is where I'm stuck and I know this is not the right formatting although I wish it were because it would make my life a little bit easier.) Student_ID = 115485, 115856, 568547, 965864, etc...
I may have up to 100 specific student ids to put in this one statement. I know I can use the "WHERE Student_ID = 115485 OR Student_ID = 115856, OR Student_ID = 568547" but that would be alot of waste. Seems like there should be an easier way than using the "OR Student_ID =" for every entry.
Can someone explain another way I can do this. Thanks in advance.
View 3 Replies
View Related
Jan 28, 2008
Hey, I have a pretty simple question.My query is throwing an error saying "Invalid column name 'subject'."The problem is that subject is a custom column I've made, well just look at the sql:SELECT a.ArticleID, subject=ISNULL((select subject from subjects),'') where subject='some subject'
View 8 Replies
View Related
May 15, 2001
I have already created package which loads a text file to database using the dts wizard in Enterprise Manager.How do I execute that package using visual basic?Please provide the Code!!!Thanks
View 2 Replies
View Related
Apr 25, 2000
Hello,
I've just migrated my access database from access2000 to sql7.0. The wizard told me there was no problem. But a simple question:
How do i open my database? Where can i see tables, fields...?
Is there no interface like the one in access2000?
Thanks in advance!
View 2 Replies
View Related
Dec 6, 2004
Hi there.
I am trying to use MDX code to create a measure in ProClarity. Please help!!
A store can be one of a number of ‘Brands’. The MDX segment below gives me the Sales Value of a selected item, but for Store Brand 'Brand1' only. This works fine - but how do I add a brand? That is, how do I see the combined Sales Value for the selected item for 'Brand1' and 'Brand2'?
([Store Brand].[Brand1],[Measures].[Sales Value],[Item].CurrentMember)
B
View 8 Replies
View Related
Sep 15, 2005
Hoping someone could me with an ongoing indexing question that I have.
On my site, we have over the past 5 years developed what is emerging as a fairly complicated dbase structure, as features have been added to my site and relations have increased between different database tables, there has been a need to index fields in different ways, and in some instances field indexing has overlapped. For example we may have a table that has 5 fields (field1,field2,field3,field4,field5). A need to index field1 is requried because of a query that reads:
SELECT * From Table1 where field1=XXXXX
Additionally there may be a need to for another query that reads:
SELECT * From Table1 where field2=XXXXX
In this instance an index is placed on field2....
But, for example when there is the following query:
SELECT * From Table1 where field1=XXXXX and field2 = XXXXX
Is it necessary to set a new index on: field1,field2 ???
We have made the choice that yes, in fact there is...but now over time some of our tables have instances of single fields being indexed along with combinations of two single fields that have already been indexed, being indexed together. As tables have grown to over 1,000,000 records and having up to 15 or so indexes, we realize that the number of indexes maybe degrading performance. Also, indexes vary in type, e.g INT,BIGINT,Varchar fields... In the above instance, can we eliminate the multi-indexes and improve performance over all...?
On a second related question:
In the event that two tables are joined on a common field.
e.g. Select * from Table1,Table2 where Table1.field1=Table2.field1
Is it necessary to index both of these fields in tables: Table1 and Table2 ?
Hope someone can help, as we are looking to improve the efficiency of our tables as they continue to grow.
View 3 Replies
View Related
