|
Hi,
I have a query that needs to act as one single query:
Here's the first part:
select distinct a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled
from a10 a
inner join escrow d on a.escrow = d.escrow
inner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21 ' and j.fstatus = 'PAID' group by a.officer
Here's the second part:
x.amount as New from escrow k inner join e120 x on k.escrow = x.escrow
inner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer having x.amount <> 0.0
Here's the third part:
b.amount as Refi from escrow c inner join e120 b on c.escrow = b.escrow
inner join a10 f on c.escrow = f.escrow where c.ftype = 'R' group by b.amount, f.officer having b.amount <> 0.0
Is there a way to make this act as one single query? I have an application that reads only one cursor at a time.
Thanks In Advance,
Nino
|
|
|
|
|
I must ask why is the second part using different alias names from the third part? The queries seem to only differ by escrow.ftype.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
My reasoning behind that was to get the values of two different fields.
The second part is getting the values for the 'S' which is defined as a "New" escrow sale.
The third part is getting the values for the 'R' which is defined as a "Refi" or refinance.
After these two fields are retrieved they are then added based on the escrow field based on the join.
Nino
|
|
|
|
|
I might ask why the second & third part have 'GROUP BY' & 'HAVING' clauses when there is only 1 field selected and it is not an aggregate.
I also question the counting of distinct dates in the first query. Can't an officer handle more then one open/closed/cancelled transaction per day?
|
|
|
|
|
I have a database that I created localy and the owner is listed as me. But when trying to access the database (conn.open()) I get the following error:
SqlException: Cannot open the database requested in login 'labels'.
Login failed for user 'AFFORDABLERTO\brian'
I triple checked the ownership, and the user named in the error is the owner.Here is the connection string
SqlConnection conn = new SqlConnection("Data Source=affordable02;Initial Catalog=Labels;Trusted_Connection=true");
What else could I check....this is the first time I have had this problem.
Thanx in advance!
Jude
|
|
|
|
|
Delete the user 'AFFORDABLERTO\brian' from the database security and then add the user back in again in database security. Also look at the database security rights for 'AFFORDABLERTO\brian' on the database ( are they Public or Dbower or even SysAdmin for the whole box).
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
|
i m working on the stored procedures in SQL that computes the division of two columns having smallint as their datatype,for each row , the results are stored in a column of another table the datatype of the result is taken as float but it is correct upto 1 decimal place
How do i increase the precision of float data type that gives the correct result "
|
|
|
|
|
You could cast the two smallints to floating point numbers before doing te calculation. You can also cast the result to any precision you would like as well.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
7/1612 = 0.004342431761.............
6/1397 = 0.0042949176807.................
above examples shows the division of 2 smallints giving a floating pt. result. but in our case I m getting the result column as 0.0 only for all values.
y is this happening, y isn't it considering the whole result or atleast it should give 3 to 4 values after decimal.
|
|
|
|
|
Hi saneng
In my tests i'm getting the full results which are:
7/1612 = 4.3424317617866E-03
6/1397 = 4.29491768074445E-03
I do the same. Values (of type smallint) are stored in one table, and division results (of type float) are stored in another table. Moreover with all my search I didn't find that how to change/set the precision + scale of float type. Its default precision is 53 which cannot be changed.
saneng wrote: 7/1612 = 0.004342431761.............
6/1397 = 0.0042949176807.................
above examples shows the division of 2 smallints giving a floating pt. result. but in our case I m getting the result column as 0.0 only for all values.
y is this happening, y isn't it considering the whole result or atleast it should give 3 to 4 values after decimal.
I guess you are using some other type like numeric or decimal. If this is so then check and set its precision + scale value.
Wish you all the best.
_____________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
Hello,
I am looking to cancel a long running ODBC query. The ODBCCommand.Cancel() method doesn't seem to be working. Basicly I have a DataSet.Fill command executing on a worker thread. My WinForm's cancel button calls my CancelDownload method which calls the DataAdapter.SelectCommand.Cancel() method on the worker thread.
I've been struggling with this for a few days now. From what I have read from Microsoft this is supposed to work. Other products like Hummingbrird BI Query appear to cancel the query. What is the secret?
Thanks for your help.
|
|
|
|
|
Could you try using a transaction. Use trans.Rollback() then cmd.cancel. I don't know why the command.cancel wouldn't work. What version of sql server are you using?
//Open the connection and begin a SQL Transaction
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
trans.Rollback()
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06
|
|
|
|
|
hi all
i have string = "Jonh, Enghland $& Mike, VietName $& Bush, America"
i have table "Temp" have 2 colum : Name , Contry
I want use SqlServer 2000 insert to table "Temp" with 3 row Seperate by $&
how will i do ?
|
|
|
|
|
u can use strings split method.
rahul
|
|
|
|
|
can u send me Example but Syntax is SQLSERVER 2000
|
|
|
|
|
exec splitinsert 'Jonh, Enghland $& Mike, VietName $& Bush, America'
CREATE PROCEDURE SplitInsert
@cNameCountry varchar(500)
AS
declare @nLocNameStart int
declare @nLocComma int
declare @nLocDelim int
declare @cName varchar(100)
declare @cCountry varchar(100)
select @nLocNameStart = 1
while (@nLocNameStart<len(@cNameCountry))
begin
select @nLocDelim = CHARINDEX('$&',@cNameCountry,@nLocNameStart + 1 )
select @nLocComma = CHARINDEX(',',@cNameCountry,@nLocNameStart + 1 ) + 1
if (@nLocDelim=0)
select @nLocDelim = len(@cNameCountry) + 1
select @cName = ltrim(SUBSTRING(@cNameCountry,@nLocNameStart,@nLocComma-@nLocNameStart-1))
select @cCountry = ltrim(SUBSTRING(@cNameCountry,@nLocComma,@nLocDelim-@nLocComma))
insert into tblNameCountry (Name,Country) values (@cName,@cCountry)
select @nLocNameStart = @nLocDelim + 2
end
GO
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
thanks u verry much but @cNameCountry varchar(500)
when u assgin cNameCountry is varchar or nvarchar have only character (4000) but when
larger 4000 only type TEXT or NTEXT but will have error "The text, ntext, and image data types are invalid for local variables."
And Your Example have while (@nLocNameStartbegin is error Syntax can u see again
-- modified at 23:37 Monday 24th July, 2006
|
|
|
|
|
AnhTin wrote: thanks u verry much but @cNameCountry varchar(500)
when u assgin cNameCountry is varchar or nvarchar have only character (4000) but when
larger 4000 only type TEXT or NTEXT but will have error "The text, ntext, and image data types are invalid for local variables."
If this is an issue, than you need to rethink your design.
AnhTin wrote: And Your Example have while (@nLocNameStartbegin is error Syntax can u see again
Look again...there is no variable called @nLocNameStartbegin You are missing a carriage return between Start and begin .
I actually ran the code I posted...and it does work.
|
|
|
|
|
Hello,
I have two tables users and news. Users table contains a column which consists of keywords seperated by "," e.g "C#,Java,OOPS" and in the news table there is a column description. I should search for keywords in description column.I am using freetext predicate to perform searching.Now my doubt is
I have to search for each user the complete news tables. So I have to write a subquery. I should get the keywords from the user table and submit it to news tables in the freetext predicate. freetext predicate is not allowing me to give a subquery.
i am giving u the query here
select newsid from news where freetext(description,' "C#,java"' )
for the above query I will get the list of newsid where the description contains either c# or Java or both.
now in the above query i should get the keywords dynamically from the users tables for each user.
if i am giving it as below it is giving me error
select newsid from news,users u where freetext(description,select keywords from users where userid = u.userid )
I want to retrieve the data in a single query with the help of subqueries.
Can some one give a solution for this problem.
WIth Regards
Uma
|
|
|
|
|
see your code again:
select newsid from news,users u where freetext(description,select keywords from users where userid = u.userid )
you must change it as belows as you want to use:
1-select newsid from news,users u where freetext in (select keywords from users where userid = u.userid )
2-select newsid from news,users u where freetext=(select keywords from users where userid = u.userid )
in your case one of this codes can be used ,
i can't understand what is the description in subquery and i delete it.
Human knowlege belongs to the world
|
|
|
|
|
select newsid from news,users u where freetext(description,select keywords from users where userid = u.userid )
In the above code description is name of the column
and the syntax is select collist from table where freetext(colname,searchlist)
freetext is a phrase used in where clause
|
|
|
|
|
ok.sorry about my less knowledge,
i see syntax of freetext ...
but another way is here:
select newsid from news,users u where description in(select keywords from users where userid = u.userid )
Human knowlege belongs to the world
|
|
|
|
|
Its ok.Even I learnt abt it recently. But when u say col in(list) column cannot contain descriptive text. column should contain single value. It will not help me for this situation.
|
|
|
|
|
Hi,
i have one textbox and one litstbox. If i am typing some thing on the textbox and i am selecting one value from the listbox if i am clicking one buttion then the textbox value and listbox value should be placed in the datagrid. Can you tell how to achive this
|
|
|
|