Hi,
I apologize if this is not the section where this should be posted.
Below is a link to a post I made in the Visual Basic forum. Now that I think about it, it may be more of a SQL question than a VB one.
I'm trying to run a query on the result of a query. Thank in advance for any help.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2486926&SiteID=1
I have this table record:
PHP Code:
ID | NUMBER | DSTART | DEND | ADDRESS |
----------------------------------------------------------------
1 | 9524123 | 12 Dec 95 | 24 Dec 95 | London |
2 | 9524123 | 06 Jan 96 | 15 Jan 96 | Paris |
3 | 084521 | 12 Mar 96 | 15 May 96 | New York |
4 | 084521 | 22 Aug 96 | 25 Aug 96 | Sidney |
---------------------------------------------------------------
Now, I need to build a query to show only the latest DTSTART date
for each NUMBER. The result would be something like this:
PHP Code:
ID | NUMBER | DSTART | DEND | ADDRESS |
----------------------------------------------------------------
2 | 9524123 | 06 Jan 96 | 15 Jan 96 | Paris |
4 | 084521 | 22 Aug 96 | 25 Aug 96 | Sidney |
---------------------------------------------------------------
Can you guys help me to build the queries? Thanks in advance.
Hi,
I have been trying to write a nested query, however i am facing errors while executing that.
The code is as below.
<%
Dim MyConnection = New SqlConnection()
MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString
MyConnection.Open()
Dim cmd As New SqlCommand
Dim rs As SqlDataReader
Dim rs2 As SqlDataReader
Dim query = "select count(bank_id) from bank_master"
Dim query2 As String
cmd.CommandType = CommandType.Text
cmd.Connection = MyConnection
cmd.CommandText = query
rs = cmd.ExecuteReader
If rs.Read() Then
Dim rec = rs(0)
If (rec > 0) Then
Response.Write(rec & " banks registered in the system")
rs.Close()
query2 = "select * from bank_master order by bank_id"
rs2 = cmd.ExecuteReader(query2)
If rs2.Read() Then
Response.Write(rs2(0))
Else
Response.Write("No banks yet registered in the system")
End If
Else
Response.Write("No banks yet registered in the system")
End If
End If
%>
The error message that i am getting at runtime:
Server Error in '/Accounts' Application.
Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.FormatException: Input string was not in a correct format.Source Error:
Line 117:
Line 118: query2 = "select * from bank_master order by bank_id"
Line 119: rs2 = cmd.ExecuteReader(query2)
Line 120: If rs2.Read() Then
Line 121: Response.Write(rs2(0))
Source File: E:WEBHOMEAccountsank.aspx Line: 119
Could you please help?
Best Regards,
Debashis
I have a the following fields in a table TblToDo
[ToDoID] [int] IDENTITY(1,1)
[ToDoReferralID] [int]
[ToDoDate] [datetime]
[ToDoCompleteBy] [datetime]
[ToDoCompleted] [datetime]
and want to produce a query to show the with following
All Grouped by [ToDoReferralID]
Count [ToDoID] AS [ToDoToTal]
Count [ToDoID]WHERE [ToDoCompleted] IS NULL AS [ToDoIncomplete]
Count [ToDoID]WHERE [ToDoCompleted] IS NULL AND [ToDoCompleteBy] <= GETDATE() AS [IncompleteUTarget]
Count [ToDoID]WHERE [ToDoCompleted] IS NULL AND [ToDoCompleteBy] > GETDATE()AS [IncompleteOTarget]
I can manage a single aggregate but where to being trying to incorporate all into one query.
Hi all,
I need to rewrite the following query without a nested query (stupid mysql 4.0.25. Can anyone lend a hand?
SELECT name from `list` where name not in (
SELECT DISTINCT b.name from ` armor ` a,` list` b where b.name = a.name
);
Though I am a novice to MS SQL server (2000 I believe), I can do almost!everything I need. Maybe not efficiently, but usefully. However, I havea problem -- a complex query problem...I can create a parameter query in a stored procedure, but how do I usethe result set of a parameter query in a select query (in the same oranother sp)? In short, if a select query contains a result table that isgenerated as a parameter query, how do I pass the parameter through theselect query to the nested parameter query?For example, if I have a sp parameter query called [e_typerows], I couldwrite the following select query which uses the (single) result set from[e_typerows] plus other tables or views:SELECT TOP 100 PERCENT bi_occ_01_e.row, bi_occ_01_e.pos,bi_dictionary_e.typestring, bi_occ_01_e.shadowFROM bi_dictionary_e INNER JOIN(bi_occ_01_e INNER JOIN[e_typerows] ON bi_occ_01_e.row = [e_typerows].row) ONbi_dictionary_e.typeid = bi_occ_01_e.typeidORDER BY bi_occ_01_e.row, bi_occ_01_e.posEven though [e_typerows] is a (storted procedure) parameter query thiswill work IF my parameter in [e_typerows] has a default, say "yyyy". Inthe default case no parameter needs passing. But what if it doesn't havea default value or if I need to pass a parameter to it?Is there any way to replace[e_typerows]in the above query with somethinglike [e_typerows @myparameter='xxxx']? (Maybe I should try that! I'veseen nothing about it though.)My parameter query, [e_typerows], looks like this in fact:@typestringparameter varchar(100) = "yyyy"SELECT dbo.bi_dictionary_e.typestring, dbo.bi_occ_01_e.rowFROM dbo.bi_occ_01_e INNER JOINdbo.bi_dictionary_e ONdbo.bi_occ_01_e.typeid = dbo.bi_dictionary_e.typeidGROUP BY dbo.bi_dictionary_e.typestring,dbo.bi_occ_01_e.rowHAVING (dbo.bi_dictionary_e.typestring = @typestringparameter)Any suggestions would be greatly appreciated! (For the moment, I firstgenerate the parameter query result set, then I loop through each row ofthat result set and do a select query with that row as a parameter --very complicated!, though it does work.)
View 3 Replies View RelatedI am using Server 2012 and very new to SQL. I have a request from a physician for a list of his patients that meet a criteria. This is stored in a temp table names #cohort.
Using this cohort he wants each row to be one patient with a list of labs, vitals, etc. Three items are the most recent lab value and date. I could query each lab individually and place it into a temp table and then join all temp tables at the end, but I am trying to move past that and have all labs in one temp table. All temp tables are joined with PatientSID.
I tried to do something for just 2 labs, but it is not working. There could be nulls values when joined with the #cohort table.
Individually the SELECT statements pull in the most recent lab value and date, but I cannot get them into a temp table with one row of PatientSID and then the lab value and date if they exist.
IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab
SELECT
cohort.PatientSID
,SubQuery1.LabChemResultNumericValueAS 'A1c%'
,SubQuery1.LabChemCompleteDateTimeAS 'A1c% Date'
,SubQuery2.LabChemResultNumericValueAS 'LDL'
[Code] .....
Hi,I'm adapting access queries to sql server and I have difficulties withthe following pattern :query1 : SELECT * FROM Query2 WHERE A=@param1query 2: SELECT * FROM Table2 WHERE B=@param2The queries are nested, and they both use parameters.In MS Acccess the management of nested queries with parameters is soeasy (implicit declaration of parameters, transmission of parametersfrom main query to nested query)that I don't know what the syntax should be for stored procedures.The corresponding stored procedure would be something likeCREATE TABLE #TempTable (...table definition...)INSERT INTO #TempTable ExecProc spQuery2 @Param2SELECT * FROM #TempTable WHERE A=@Param1And spQuery2 would be : SELECT * FROM Table2 WHERE B=@ParamI was wondering if this syntax would work and if I can skip theexplicit declaration of #TempTable definition.Thanks for your suggestions.
View 5 Replies View RelatedHelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies View RelatedCan you give a whole SQL statement an alias so you can use it later?
Eg.
SELECT * FROM Employees WHERE age < 19
-- Could I call the above statement something like 'statement1' to use below as shown
SELECT * FROM Employees WHERE age < 25 AND NOT IN (statement1)
Soin effect I get a nested statement.
The reason I am asking about aliases is because this would need to be repeated for, E.g. age < 30 Then age < 35 and so on and so forth.
So basically, I just want to alias a qhole SQL statement
Any help would be greatly appreciated - George
Hi all,I have a query that looks like so:SELECT GLDCT AS [Doc Type], GLDOC AS DocNumber, GLALID ASPerson_NameFROM F0911WHERE (GLAID = '00181913')However by stipulating that GLAID = GLAID I cannot get the person_nameas not all the GLALID fields are filled in. from my reading of thehelpdesk I have a felling that a nested query might be the way to goor a self-join but beyond this I am lost!?Many thanks for any pointers in advance.Sam
View 3 Replies View RelatedI am trying to do some nested IF ELSE conditions. I get an error saying 'Error near work Begin'. Below is teh query and the variables comes in thru cursor.
Can somebody advise me on this and also let me know the best practices and alternative to this if any.
IF (@CCTable = 'Claiminassoc')
BEGIN
IF ( @ClaimCenterField = 'ClaimID' AND @VALUE ='Claim')
BEGIN
UPDATE dbo.Table SET ColName = 'Y'
WHERE ID = @ID AND CCTable = 'Claiminassoc' AND CCField = 'ClaimID'
AND DWField = 'CatastropheDesignationFlag'
END
END
ELSE IF (@CCTable = 'EmploymentData')
BEGIN
IF (@VALUE ='TRUE')
BEGIN
UPDATE dbo.Table SET ColName = 'Y'
WHERE ID = @ID AND CCTable = 'Claim' AND CCField = 'WagePaymentCont'
END
ELSE IF (@VALUE ='FALSE')
BEGIN
UPDATE dbo.Table SET ColName = 'N'
WHERE ID = @ID AND CCTable = 'Claim' AND CCField = 'WagePaymentCont'
END
END
Hi gang,
I have a challenge, which seems like it is probably trivial, but SQL chops are not up to the task, and I am hoping one of you hot-shot DBAs can throw me a bone!
I have a query that populates an OLAP Time dimension table (basically one row per day of the year over several years). What I want to do is expand that table to include each hour of each day over the time span.
The CTE I am using for the day population is:
Code Snippet
WITH dates(date)
-- A recursive CTE that produce all dates between 2006 and 2057-12-31
AS
(
SELECT cast('2006' AS dateTime) date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
SELECT (date + 1) AS date
FROM dates
WHERE
date < cast('2058' AS dateTime) -1
)
What I wanted to do was something like:
Code Snippet
WITH hours(hr)
AS
(
SELECT (DATEPART(hh,date) hr
UNION ALL
SELECT (hr + 1) AS hr
FROM hours
WHERE hr < 24
)
inserted just after
Code Snippet
FROM dates
in the initial CTE. But from what I have read, it seems as though nested CTEs are not allowed.
Can anyone show me how to do this?
Thanks in advance for ANY insight/input!
Cheers,
Chris
Greetings -
Can someone pls advise the maximum number of nested IIF statements allowed in a VS 2005 report builder layout textbox expression? I seem to be hitting a wall at about 10, but cannot find verification. In case the limitation is by characters, the full expression would run about 3,500. Any other limitations which may have a bearing?
Tks & B/R
Hi all! I have a problem with my stored procedure, What I'm trying to do here is whenever a user tries to register, the stored procedure will check if the username already exists, and if not it'll now check if the email has already been entered into the database then if not the stored procedure will go ahead and insert the values into the database. *If the username already exists it'll return -1, and if the email already exists then return -2.
SELECT Username FROM UserAccount WHERE Username = @UsernameIF @@ROWCOUNT = 0 SELECT Email FROM UserAccount WHERE Email = @Email
IF @@ROWCOUNT = 0 BEGIN
INSERT INTO UserAccount (Username, Password, Email, FirstName, LastName, Gender, BirthDate, Country, State, Zip, AdditionalInfo)
VALUES (@Username, @Password, @Email, @FirstName, @LastName, @Gender, @BirthDate, @Country, @State, @Zip, @AdditionalInfo)
END
ELSE BEGIN
RETURN -2
END
ELSE BEGIN
RETURN -1
END
END
Thanks!
Hi, I'm not sure if this is a good place to ask sql questions, so please bear with me here...I have a table like soid, parentidWhat I'm trying to do is to write a self join where given a random ID, it'll give me the whole tree of its decendents. (I don't need its parent)so say I have data liek soid parentid1 null2 13 2when I specificy 1, it'll give me 2 and 3, even though 3 is indirectly related to 1. when I say 2, it'll just give me 3. Thanks a lot. GREATLY appreicate it.
View 1 Replies View RelatedWhen running reports from data, is it faster using nested views approx 4 levels deep, or writing data to a temp tables then running the report?
View 3 Replies View RelatedWhat's worng, please help? SELECT TTarea,personel,Date FROM person_table WHERE TTarea = (SELECT TTarea FROM TTarea_table WHERE Center='CENTER_office') I have many TTarea and I want to send back from inner SELECT statement but give an error that inner select statement don't return many result.I want to return many result and I display many TTarea in the CENTER_office
View 2 Replies View RelatedHi All,
Im inserting some data into a table and grabbing the new UserID with this statement
SELECT @UserID = @@IDENTITY
I would like to use the @UserID to Execute another SP within the same proc..
..something like this
Exec AnotherSP(@UserId)
But this doesnt seem to be working....Its seems to me that this is a much better approach performance wise rather than returning the UserID to the Business Logic Layer and calling another SP....Im I correct in that assumption....any assistance would be greatly appreciated.
Thanks in advance
AMRITZ
I think I am getting an endless loop here... anyone know how to fix it?
***********************
CREATE PROCEDURE TrigSendPreNewIMAlertP2
@REID int
AS
Declare @RRID int
Declare @ITID int
Declare @FS2 int
Declare @FS1 int
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null
open crReqRec
fetch next from crReqRec
into
@RRID
Declare crImpGrp cursor for
select ITID from RequestRecords where RRID = @RRID
open crImpGrp
fetch next from crImgGrp
into
@ITID
while @@fetch_status = 0
select @FS1 = @@Fetch_Status
EXEC TrigSendNewIMAlertP2 @ITID
FETCH NEXT FROM crImpGrp
into
@ITID
close crImpGrp
deallocate crImpGrp
while @@Fetch_Status = 0
select @FS2 = @@Fetch_Status
FETCH NEXT FROM crReqRec
into
@RRID
close crReqRec
deallocate crReqRec
GO
Hi,
Although I am quite familiar with MS Access-grade SQL, I am struggling a bit with proper grown up SQL Server. My usual approach to counting things in Access is to first create a query with the conditions on the data, then use this as the basis of a second query that does the actual counting of the presorted data. I believe the way to do this in SQL server is to use a nested query. I want to generate the top 10 highest counts for each pesticide detected (detection is level>0) for a client between two dates.
Currently I am using
<code>
SELECT top 10 Count(Pesticide) AS CountOfPesticide, Pesticide FROM (SELECT tblData.Pesticide, tblData.Level, tblData.Clast, tblData.Client FROM tblData WHERE (((tblData.Day>@sdate) AND (tblData.Day<@edate))) and (tbldata.level>0) and (tbldata.clast=@clast) and (tbldata.client=@client)) as monkeyboy GROUP BY Pesticide ORDER BY Count(Pesticide) ASC"
</code>
The results that the above SQL turns out though are not reliable. For example, if I set the dates to now and 14 days ago, it produces higher counts for some pesticides then if I set the dates to now and 30 days ago. Any pointers or general advice about nested sql is gratefully accepted!
thanks
Mike
Hi,Please can somone help me with a nested SQL query. I have two tables please see belowTable 1CallIDEmployeeIDCallSummaryCallStatusTable 2CallHistoryIDCallIDDataAddedCallActionI would like to return the CallID, EmployeeID, CallSummary and CallStatus from Table 1, and also display the last CallAction from Table 2.This is a helpdesk database so a Call will have many CallActions i.e. Open, Held, Assigned Internal. How do I return the last CallAction Added against the selected CallID, I know I use the DateAdded but not sure about nested statements.The results I would like to return to the user would look like this:-Call ID: 1EmployeeID: 1Call Sumary: SQL ProblemCall Status: OpenCall Action (Last Action): Assigned Internal.
View 2 Replies View RelatedHi all,
I have a temp guy who is doing some work for us (seriously, I never wrote the query).
Now this is the scenario (hold tight). So we have a stored procedure as follows...
ALTER PROCEDURE createSegmentPoints AS
DECLARE @fLat INTEGER
DECLARE @fLon INTEGER
DECLARE @segId INTEGER
-- declare cursor to return the from lat and lon for all segments that do not have address point 109.
DECLARE c1 CURSOR FOR (SELECT From_Latitude, From_Longitude, id AS segment_id FROM Segments WHERE SegmentType != 109)
OPEN c1
FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
WHILE @@FETCH_STATUS = 0
BEGIN
-- insert into table the segId, from lat, from lon and returned segment id from function.
INSERT INTO test VALUES (@segId,@fLat,@fLon,dbo.points_test(@fLat,@fLon))
FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
END
CLOSE c1
DEALLOCATE c1
As you can see here I am using a Cursor, which in turn calls a function
with each row that is processed in the recordset. The function that is
called is as follows...
ALTER FUNCTION points_test(@x INTEGER, @y INTEGER)
RETURNS INTEGER
AS
BEGIN
-- function to find the closed segment point with address point 109 to the segment specified in procedure.
DECLARE @tempDistance FLOAT(4)
SET @tempDistance = 1000000
DECLARE @id, @seg, lat, lon INTEGER
DECLARE @distance, @xd, @yd FLOAT
DECLARE c1 CURSOR FOR (SELECT from_latitude, from_longitude, id FROM segments WHERE segmenttype = 109)
OPEN c1
FETCH NEXT FROM c1 INTO @lat, @lon, @id
WHILE @@FETCH_STATUS = 0
BEGIN
-- calucations to get distance.
SET @xd = (@lat-@x)
SET @yd = (@lon-@y)
SET @distance = SQRT((@xd*@xd) + (@yd*@yd))
-- test if you have shortest distance.
IF (@distance < @tempDistance)
BEGIN
SET @tempDistance = @distance
SET @seg = @id
END
FETCH NEXT FROM c1 INTO @lat,@lon, @id
END
CLOSE c1;
DEALLOCATE c1;
RETURN @seg
END
(This function works out an equation to get the shortest distance from
two parameters passed to the function calculated with data from each
row returned within the cursor)
As you can see here, this function contains ANOTHER cursor!! Ouch. The
fact that their is an SQL query in a function is a killer, but having
another embedded cursor there is also a killer - this has virtually
killer the application.
So, how best is it for me to correct this. Should I turn the function
into a stored procudure? But even if I do this, the nested cursor still
remains. I was thinking maybe to have the SQRT equations within the
SELECT expression and then wrapped in a MIN() to maybe get the lowest
value.
Any ideas would be of great help.
Thanks
Tryst
Guys,
Has anybody out there successfully nested roles?
SQL Server seems to let you do it, but then no permissions are carried up the hierarchy.
Regards,
ChrisH
Hello;
I have a question why does not the following nested transaction work?
begin tran
insert into t1 values('A')
begin tran
insert into t2 values('1')
commit
insert into t3 values('B')
begin tran
insert into t2 values('2')
commit
rollback
The rollback is rolling back everything.
Thanks
Nathan
I have a situation where I have two cursors: outer_cursor and inner_cursor. The inner_cursor is supposed to execute based on a value passed on from outer_cursor. It is not running as desired. At runtime, it complians that the inner_cursor is not open.
Thanks
Hi,
I want to write one query which will select multiple distinct records from one table
For e:g
Lets say in a table i have 3 fields name,tel_no,sex
Now i want to list all the records which are distinct in each of these fields
like distinct name,distinct address
IS IT POSSIBLE and if yes HOW
Thanks
Ashish
Hi. newbie here.
Also new to coding in SQL.
Struggling with how to implement the following psuedo-code in SQL server 2000.
** Can you use more than one CURSOR variable?
If yes, when use FETCH_STATUS is it for cur1 or cur2 ??
Sample data is at the bottom.
Thanks for ANY suggestions !!
** Assume TABLE 1 is sorted by Record_Type, Order_no, Order_line_no
************************************************** ***
dim @rectyp
dim @ord#
dim @lin#
Fetch (?) 1st record in TABLE1
While Still Records in TABLE1
Set sub_line# = 0
set @rectyp = Record_Type,
set @ord# = Order_no,
set @lin# = Order_line_no
while @rectyp = Record_Type and
@ord# = Order_no and
@lin# = Order_line_no
Set sub_line# = sub_line# + 1
update TABLE1 set line_ctr = sub_line#
get next record
end inner WHILE
end outer WHILE
************************************************** ****************************
Sample data :
Data as it currently exists:
Record_type ......Order No......Order line no ......Line Ctr
OP.....................458001................5.... ...............0
OP .....................458001..............5 .................. 0
OP..................... 458001..............5..................0
OP .....................458001..............5........ ..........0
OP.....................458191..............1 ..................0
OP.....................458191..............1 .................. 0
OP..................... 458308..............73..................0
OP .....................458308..............73....... ........... 0
OP.....................458308..............73..... .............0
OP.....................458308..............73..... .............0
Want data to look like this after executing code:
Record_type ......Order No......Order line no ......Line Ctr
OP.....................458001................5.... ...............1
OP .....................458001..............5 .................. 2
OP..................... 458001..............5..................3
OP .....................458001..............5........ ..........4
OP.....................458191..............1 ..................1
OP.....................458191..............1 .................. 2
OP..................... 458308..............73..................1
OP .....................458308..............73....... ........... 2
OP.....................458308..............73..... .............3
OP.....................458308..............73..... .............4
************************************************** *********************
What's wrong with this...
Code:
SELECT case when tab1.col2=tab1.col3 and tab1.col3!=0
then (SELECT tab3.col3 FROM tab3)
else (case when tab5.col2=tab5.col6
then (SELECT tab7.col1 FROM tab7)
else (case when tab1.col2=tab1.col3 then tab1.col4+7 end)
end as value
From tab5, tab1
WHERE tab1.col1=tab5.col1
I get the error as-
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'as'.
please help me.
Morning everyone,
I have a sp that I've created that is to show me everyone table name and column name using nested cursors. However when I execute the procedure it doesn't show me the names, it just tells me the command completed successfully. Here is the code:
CREATE PROCEDURE uspSeeAllViews
AS
SET NOCOUNT ON
DECLARE @strMessageVARCHAR(100)
DECLARE @strColumnVARCHAR(100)
DECLARE @strViewVARCHAR(100)
DECLARE @strCommandVARCHAR(250)
DECLARE crsViews CURSOR FOR
SELECT
name AS strView
FROM
sysobjects
WHERE
type = 'U'
OPEN crsViews
FETCH NEXT FROM crsViews INTO @strView
WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE crsColumns CURSOR FOR
SELECT
name AS strColumn
FROM
syscolumns
WHERE
name = @strView
OPEN crsColumns
FETCH NEXT FROM crsColumns INTO @strColumn
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @strView + ':' + @strColumn
FETCH NEXT FROM crsColumns INTO @strColumn
END
CLOSE crsColumns
DEALLOCATE crsColumns
FETCH NEXT FROM crsViews INTO @strView
END
CLOSE crsViews
DEALLOCATE crsViews
Thanks for looking, any ideas??
i am new to SQL Server DB.
i have a typical query problem.
master table contains Data in following format
id stype sid svalue
1 status 1 approved
2 status 2 pending
3 request 1 draft
4 request 2 waiting
etc
in my query i need to substitute the svalue in place of sid
e.g.
select requestid ,bstatus, brequest from request ;
should give
---------------------------------
R1122 approved draft
------------------------------
instead of
R1122 1 1
*****************
link field is column name (quite unusual )
like for status column , it is bstatus column name and it is stored in master table as status .
i need to do the following in 1 query only.
kindly suggest some nested query to do the same
kindly help
Smita
I need to decrease the amount of time it takes to return a set of data of the following format:
State[Provided vai user intput]->All Counties in State->All Cities in County->All Zones in City[usually < 50 or 75 per city]
current behavior:
1. currently user selects state all counties are returned from db
2. foreach county cities are returned
3. foreach city streets are returned
4. objects hydrated with data & display drawn
As you can see this gets expensive. I know there has to be a better way to do this. Any suggestions? We use sql server/c#.net.
I know you can do something like:
SELECT
ColumnA,
(SELECT Columnb FROM Table Where...),
ColumnC
...
Can you select multiple columns, and how if possible, such as:
SELECT
ColumnA,
(SELECT ColumnB, ColumnC, ColumnD FROM Table Where...),
ColumnE
...
If this is possible, how would the columns be aliased?
Thanks in advance.