In the database i have a table with this information:
key_id =1
key_desc =43+34+22+12
I want sum the values in key_desc. Something like:
SELECT key_desc FROM table
But the result of the select was "111" and not "43+34+22+12".
Is this posible?
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
Hi there,
Problem:
Having an old webapplication with dynamic build sql queries. Now the database changed completly because of new db model. Queries are getting much more complex now, so i put them into stored procedures.
Now i want to work with the data in an application for sorting an filtering. All the 'WHERE' conditions are build dynamic within the code. For not modifying the whole application i would like to do something like:
SELECT * FROM (EXECUTE sp_someprocedure) WHERE X = 1
Is there a way to realize this ?
Thx for helping
hey there
I have parameters in my report set up
CallStatus = string
Available Values are non-queried and values are listed in the value list
closed, open, pending, select all
If I choose each one separately I get a result - if I choose select all I get no result.
This worked fine attaching to another datasource - I have now attached it to a new source.
Tried altering the relationship to properties to a left outer join but this made no difference.
Can someone tell me what else I should check?
thanks
Hi,
I ran a sql somedays ago it gave me result, I ran the same sql today it gave different result. The only difference is that the particular table in the sql is frequently used table with lots of new insert. But the sql I ran is not selecting the new rows, it only deals with already inserted records.
Any idea what will make the sql to give different result from same set of records in different time.
Thanks
John Jayaseelan
Let's say I have a simple query to return the results of my "Status" table. The query reads as follows:
Code:
Select statusID, statusName
From Status
Here is the result set that I am returned:
Code:
22 Associate Member
23 Is Not Paying
24 Exempt
25 Fair Share
26 Member
29 Retiree
30 Staff
32 Fair Share - Self Pay
34 Member - Self Pay
Now, I am using this query for reporting purposes and would like to inject some additional sql that will append one additional row to my result set -- this is what I am calling the 'static' row in the thread title.
In other words, without modifying my database I would like to return the above set of data but with one additional row that has an arbitrary ID with the name "Unknown" or something similar.
again, I want to avoid adding an "Unknown" field directly to my database -- is their any way to "hard code" the selection of this row in my sequal?
Thanks,
Zoop
Eliko writes "i would like to add a coloumn to a select result, so there will be another coloumn with indexed running numbers for each record.
how can i do it?
thank you
eliko"
Hi, I execute a select statement
SELECT COUNT(*) FROM TABLEA WHERE DTRECORD < '1-MARCH-2008'
every times I execute it I get different result.
1st result : 17036986
2nd result : 17037903
3rd result : 17038309
Any idea??? There is no any inserting on TABLEA, so it should not has changes. Is that because of statistic is still updating?? I don't have much knowledge on this so I cannot sure the cause of the problem. Please advise.
Can we select "last 10 rows" from the result set. (sql server 2000)
Something of an opposite of "top" functionality
Hi,I have this code following my signature to SELECT a number.I don't know what am I missing, because it always return the number which the variable banID was initialized (32 in this case).The strangest thing is that if I run the statement inside MS SQL SERVER, I get the right result.Any help would be appreciated.Warm Regards,Mário Gamito--// Get inserted user IDint banID = 32 ;SqlConnection myConn = new SqlConnection("Data Source=192.168.1.6; Initial Catalog=db1; User=sa; Password=secret");try{myConn.Open()}catch (Exception e){Console.WriteLine(e.ToString());}try{SqlCommand myCommand = new SqlCommand("SELECT MAX(PublisherID) FROM table1", myConn);banID = Convert.ToInt32(myCommand.ExecuteScalar);}catch (Exception e){ Console.WriteLine(e.ToString());}Response.Write(banID);
View 3 Replies View RelatedHi AllI hope some1 with more experience of Sql Server 2000 Stored proc's can help me or point me to somewhere I can find the info cos at the moment it's doing my head in, I have spent 2 day,s so far trying to solve the following query, the problem is I do not understand the errors I am getting or why so here is the code that won't work
ALTER PROCEDURE dbo.UpdateStock
@OrderID intASSELECT (dbo.Products.Stock - dbo.OrderDetails.Quantity) AS NewStockFROM dbo.OrderDetails INNER JOIN dbo.Products ON dbo.OrderDetails.ProductID = dbo.Products.ProductIDWHERE (dbo.OrderDetails.OrderID = @OrderID) UPDATE dbo.Products SET dbo.Products.Stock = NewStockWhat I am trying to do is get the OrderDetails.Quantity form the OrderDetails table the UPDATE the Stock feild in the Products Table by deducting the OrderDetails.Quantity From the Products.Stock to give me the new stock levelWell it works to an extent in that if I do this to SET dbo.Products.Stock = 10 it will put 10 in the Stock feild but I am unabke to get the value of NewStock , which caluculates the correct value into the set statment I keep getting invalid colum name, I have tried any combination I can think of but I still get error of 1 sort or another.So if any1 could help as I am pulling my hair out with this 1So perhaps some1 could cast there eye over it pleaseThanks
I want to export the data from a database and place it on a csv file to be imported to another database. However, I want some columns from the selected data (result of select statement) to be encrypted. What should I do? Is this possible? How do you decrypt the data during import?
The original data is from an MS SQL database to be transferred to a PostgreSQL database.
MS SQL ----- CSV (some columns are encrypted) ---- PostgreSQL (all columns decrypted)
I posted the same question on the mysql category. I wanted to delete it since I just realized today that I posted it on the wrong category but seems like there is no way I can delete.
I want to SELECT the result from table, but i want the result return in record entry order, instead of sort by index or ORDER BY certain field.
View 14 Replies View RelatedI have one table with primary key index, when i want to select the result from this particular table, i wish the result arrange by record inserted squence and not by the primary key i had been set.
View 4 Replies View RelatedI have the select statement below - where I use some external functions - and I would like the result to be saved to another table.After executing the statement I end up with a table containing the following columns:
accountno
d.date_start
d.date_end
TWRR_Month
TWRR_Cum
Normally I would use the command 'INTO TableName' but since the statement is so long I don´t know where to place it.The select statement is as follows:
IF OBJECT_ID('tempdb..#trn') IS NOT NULL
DROP TABLE #trn
IF OBJECT_ID('tempdb..#mv') IS NOT NULL
DROP TABLE #mv
SELECT PTR_sequence as trno, PTR_CLIENTACCOUNTNUMBER as accountno, PTR_DATE as date_trn,
CASE PTR_TAC
WHEN 'BUY' THEN 0
ELSE PTR_LOCALAMT
END as amt_trn
[code]...
Hi this is probably a very stupid question, but I still need to know.
How do I set the result of a 'SELECT' statement to a variable? I know I can use CURSOR, but I am certain the SELECT statement will return either 1 record or NULL. Can I use something else apart from CURSOR?
I have a function that is designed to return a variable that containsconcatenated values from a partinular field in the returned rows:DECLARE @output varchar(8000)SELECT@output =CASEWHEN @output IS NULL THEN CAST(TSD.ScheduledTime ASvarchar(4))ELSE @output+ ', '+ ISNULL(CAST(TSD.ScheduledTime ASvarchar(4)),'')ENDFROM TSDWHERE ClientGUID = 2000001447020001 ANDParentGUID = 6000006684068001Select @outputThe variable returned with this code contains:"1200, 1400, 1200, 1400"I want to only get the unique values so that the variable returns "1200,1400". Seems simple enough just to add DISTINCT to the SELECT statement.However, what is returned is simply "1400".I cannot figure out why that is the case. Is there any explanation to thisresult?Side note: I can work around this by using a cursor but I would like toknow why DISTINCT does not work.Many thanks in advance for any help that can be provided!Pat
View 5 Replies View RelatedHI, I'm a simple store procedure that returns a result such as this one:
AM
AM-1
AM-2
AM-n
and in other store procedure I need to filter result from this list.
I think that some query like this is impossibile
select fields from table where id in (execute sp)
how can I make this?
Thanks a lot.
hello
Does anybody know how to store the result of a select top 1 into a variable??
I have this code:
Select @status = Select top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc
And also this:
Select @status = top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc
But none of them work
Any ideas??
Thanks
Hi
I have a table as follows
Table Cats
{
catergory varchar(20)
Update datetime
}
And the data in the table is as follows
Category Update
------------- --------------
cat1 d1
cat2 d2
cat3 d3
I would like to get only 'Category' in result set and work on it ( similar to foreach in C# )
select Category from Cats will return the required result , but how can i iterate thru then in T-Sql
Can any one please throw some light
Thank you
~Mohan Babu
in table Databackup
company keepmonth
----------------- -------------------
001 12002 12003 6005 607917 609747 6
I run this query
select Max(keepmonth) as keep from Databackup
why the result is 6 not 12? I think the max value should 12 , have no idea why it return 6
does my query error?
thank you
Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls.
currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do.
Problem:
i need to call an aggregate count on the results of another aggregate function (sum) with a group by.
*ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales
This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid.
thanks,
Tom Anderson
Software Engineer
Custom Business Solutions
Good morning;
My Problem is :im my transaction i use insert code like the following :
" Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in
(Select ORDN from OTAB where USER_ID = 'MIKE')"
In order to optimise my code ,
instead of using a subquery in my select
(I have different insert in my transaction with the same subquery).
I would like to DECLARE a varibale which will contain the select of the Subquery.
and then use it im my different insert. some thing like this.
" BEGIN TRANSACTION
DECLARE @OrdSelect int
Set @OrdSelect = (Select ORDN from OTAB where USER_ID = 'MIKE')
Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in @OrdSelect
COMMIT Tran "
I know that the @OrdSelect will receive the last value of the select not an array of values. which will make my transaction incorrect.
I dont want to use Cursor to resolve this issue Too.
Thinks.
an example for the pb
1)First i have created a dynamic cursor :
DECLARE authors_cursor CURSOR DYNAMIC
FOR Select DISTINCT LOCATION_EN AS "0Location" from am_location WHERE LOCATION_ID = 7
OPEN authors_cursor
FETCH first FROM authors_cursor
2)The result for this cursor is for expamle 'USA'.
3) If now i do an update on that location with a new value 'USA1'
update am_location set location_en = 'USA1' WHERE LOCATION_ID = 7
4)now if i fetch the cursor , i''ll get the old value (USA) not (USA1).
If i remove DISTINCT from the cursor declaration , the process works fine .
Hello all:
How can I return the result of a SELECT statement from a stored procedure (function)?
CREATE FUNCTION returnAllAuthors ()
RETURNS (what do i put here??)
BEGIN
// Is this right??
RETURN SELECT * FROM authors
END
Thanks!
I want to show the number of rows returned by a select query in the result.
e.g. select policy,policynumber,datecreated,Firstname, '-' as recordcount from policy
If it returns 5 rows, then the 'recordcount' need to show '5' in all row in the result
OutPut
0y96788,HGYG564,29/07/2013,SAM,5
FJUFBN7,JLPIO67,29/07/2013,Test,5
...
..
..
How can i get this number in the result.
Till now I get data form multiple table using join, but unable to understand how can i get the this result based on given table -
Result should be -
ProCodeProductName
PRO00001;PRO00002Product Test SearchedPromotion One;Promotion Two
PRO00001;PRO00002;PRO00002Product Final SearchedPromotion One;Promotion Two;Promotion Three
PRO00002TestingPromotion Two
Tables -
select * from ProMaster
CodeName
PRO00001Promotion One
PRO00002Promotion Two
PRO00003Promotion Three
select * from ProDetail
IDProCodeProduct
1PRO00001;PRO00002Product Test Searched
2PRO00001;PRO00002;PRO00002Product Final Searched
3PRO00002Testing
Hi,
I need to create a table which has the columns from the select statement result.
I tried in this way
drop table j9a
SELECT er.* into j9a
FROM caCase c
LEFT OUTER JOIN paPatient pp ON c.caCaseID=pp.caCaseID
Left Outer JOIN paManagementSite pm ON pp.paManagementSiteID=pm.paManagementSiteID
Left Join exexposure ee ON ee.cacaseID=c.caCaseID
LEFT OUTER JOIN exExposureRoute eer ON eer.caCaseID=c.caCaseID
LEFT OUTER JOIN exRoute er ON er.exRouteID=eer.exRouteID
WHERE c.caCallTypeID =0
AND c.Startdate between '1/1/2006' and '12/1/2006'
AND (ee.exMedicalOutcomeID=4 OR ee.exMedicalOutcomeID=10)
AND pp.paSpeciesID=1
AND c.PublicID_adOrganization_secondary is null
declare @1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,
@9 int,@10 int,@11 int,@12 int,@21 int,@22 int,@23 int,@24 int,@25 int,
@26 int,@27 int,@28 int,@29 int,@30 int,@31 int,@32 int
set @21=(select count(*) from j9a whereIngestion=1)
set @22=(select count(*) from j9a whereInhalation/nasal=1)
set @23=(select count(*) from j9a whereAspiration=1)
set @24=(select count(*) from j9a whereOcular=1)
set @25=(select count(*) from j9a whereDermal=1)
set @26=(select count(*) from j9a whereBite=1)
set @27=(select count(*) from j9a whereParenteral=1)
set @28=(select count(*) from j9a whereOtic=1)
set @29=(select count(*) from j9a whereRectal=1)
set @30=(select count(*) from j9a whereVaginal=1)
set @31=(select count(*) from j9a whereOther=1)
set @32=(select count(*) from j9a whereUnknown=1)
Create table table9(Route varchar(30),Fatal int)
insert into table9 values ('Route_Ingestion',@1,@21)
insert into table9 values ('Route_Inhalation',@2,@22)
insert into table9 values ('Route_Aspiration',@3,@23)
insert into table9 values ('Route_Ocular',@4,@24)
insert into table9 values ('Route_Dermal',@5,@25)
insert into table9 values ('Route_Bite',@6,@26)
insert into table9 values ('Route_Parenteral',@7,@27)
insert into table9 values ('Route_Otic',@8,@28)
insert into table9 values ('Route_Rectal',@9,@29)
insert into table9 values ('Route_Vaginal',@10,@30)
insert into table9 values ('Route_Other',@11,@31)
insert into table9 values ('Route_Unknown',@12,@32)
select * from table9
The exRoute result is like this
70 Ingestion
71 Inhalation
72 Aspiration
73 Ocular
74 Dermal
75 Bite/sting
76 Parenteral
77 Other
78 Unknown
524 Otic
525 Rectal
526 Vaginal
The above giving the errors
Msg 207, Level 16, State 1, Line 19
Invalid column name 'Ingestion'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'Inhalation'.
Thanks in advance
hi all
how can i put select query result in excel sheet.
can any one help me
Regards
js.reddy
I have the following SQL. I need the results to be in the @NSTATUS variable. How do I do this?
DECLARE @HISTORY_ID INT,
@NSTATUS VARCHAR(20),
@IMPORT_DATE DATETIME,
@TSQL VARCHAR(8000);
SET @HISTORY_ID = 350721;
SET @TSQL = 'SELECT DM_IMPORT_STATUS FROM OPENQUERY(NGDEV2_LINK2, ''SELECT DM_IMPORT_STATUS from NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID = ''''' + CAST(@HISTORY_ID as nvarchar(30)) + ''''''')'
SELECT @TSQL, @HISTORY_ID;
EXEC (@TSQL) ;
An example for my pb
1) Created a dynamic cursor :
DECLARE cursor_teste CURSOR DYNAMIC
FOR Select DISTINCT name from table WHERE ID = 1
OPEN cursor_teste
FETCH first FROM cursor_teste
2)The result for this cursor is for example 'teste'.
3) If now i do an update on that name with a new value 'teste1'
than if i fetch the cursor , i''ll get the old value (teste) .
any idea how to make a select distinct result in a dynamic Cursor?
I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.
Current structure is:
Colour Size Quantity
-----------------------
Red 10 100
Red 12 200
Red 14 300
Blue 10 400
Blue 12 500
Blue 14 600
Green 10 700
Green 12 800
Green 14 900
Green 16 1000
I want to produce this result set:
Colour Size10 Size12 Size14 Size16
-------------------------------------
Red 100 200 300 0
Blue 400 500 600 0
Green 700 800 900 1000
There could be any number of sizes or colours.
Is this possible? Can anyone give me any pointers?
Thanks in advance
greg