|
Ahh you're right. Just took the inner query from the poster 
|
|
|
|
|
This will work but it is not efficiant as it sums the count for the table for each row.
<br />
create table #b1(cola int)<br />
<br />
declare @a int<br />
<br />
set @a = 0<br />
<br />
while @a < 10<br />
begin<br />
insert into #b1 select @a<br />
select @a = @a +1<br />
end<br />
<br />
set @a = 4<br />
while @a < 10<br />
begin<br />
insert into #b1 select @a<br />
select @a = @a +1<br />
end<br />
<br />
<br />
select cola,count(*),(select count(*) from #b1) <br />
from #b1<br />
group by cola<br />
<br />
<br />
drop table #b1
You would be better having this code:
declare @total int<br />
<br />
select @total = count(*) from #b1<br />
<br />
select cola,count(*),@total<br />
from #b1<br />
group by cola
Bob
Ashfield Consultants Ltd
|
|
|
|
|
try this
SELECT Name,<br />
(SELECT SUM(t1.Value) <br />
FROM myTable AS t1) AS sumofcnt,<br />
(SELECT COUNT(t1.Value) <br />
FROM myTable AS t1<br />
WHERE (t1.Name = myTable.Name)) AS cnt<br />
FROM myTable<br />
GROUP BY Name
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
modified on Monday, May 12, 2008 6:09 AM
|
|
|
|
|
hi
how can i do select query that have match case in access
example:
car - is not - CAR
thanks
|
|
|
|
|
If you are using the design view - in the "Field" write: UCase(FieldName)
and in the Criteria write CAR (or whatever you want it to be in upper case)
|
|
|
|
|
That will result in a case-insensitive match, not a case sensitive one, which is what the poster is asking for.
|
|
|
|
|
A quick google search yields this.[^]
|
|
|
|
|
Hi all,
Im having problem with Simple style combobox.
actualy i have a form window with a combobox and other one is Textbox.
combobox style is Simple(CBS_SIMPLE). there is so meny text. when user type some text and hit enter key it will be select current item.
but its nothong happen. user have to press down arrow.
my question is this.
i don't wanna use down arrow key, i want to use enter key to sellect current item.
what its posible to change Keys event?
please give me any idia with small source.
thank you
bir
|
|
|
|
|
This is a database forum and is not the right place to post this question. Please post it to vc++ forum.
|
|
|
|
|
Hi,
i want to Create a trigger [for intert/update] dynamically for a table which has been created dynamically.
Can anyone help me?
thanks in advance
sri
|
|
|
|
|
|
Hi, I am having a strange issue when using the RSClientPrint component to print reports from Reporting Services. I am printing tickets, and everything worked just fine until I tried to print a 2 pages report. The first page is printed ok, but the 2 page can't be printed.
By clicking the preview button of the component, I can see the first page of the report, but the second page shows an error.
Any suggestions? What am I missing? Is there a special attribute I have to define for my component? I am using the margins attributes, height and width, and cultures.
Thank you very much in advance.
|
|
|
|
|
Some code would be nice or at least pseudo just so we know what you have done. Anyhow, are you calculating when first page is ended so printing can continue to 2nd page? Just paste some code and then we can help.
|
|
|
|
|
Hi again and sorry for the delay. Thank you very much for your answer and here is the code I am using
RSClientPrint.MarginLeft = 0;
RSClientPrint.MarginTop = 0;
RSClientPrint.MarginRight = 0;
RSClientPrint.MarginBottom = 0;
RSClientPrint.Culture = 2058;
RSClientPrint.UICulture = 2058;
RSClientPrint.PageWidth = 85.2;
RSClientPrint.PageHeight = 279.4;
RSClientPrint.Print('http://ServerName\SQLinstance/ReportServer', '/FolderName/Impresion_Factura/Factura&CODIGO_SERIE='+serie+'&ID_SUCURSAL='+sucursal+'&CAJERO='+cajero+'&NIT='+nit+'&SUBTOTAL='+subtotal+'&DESCUENTOS='+descuentos+'&CODIGO_CAJERO='+codigo_cajero, 'Factura');
I really appreciate your help. You guys always get me out of trouble!!!
|
|
|
|
|
Good Evening Everyone
i have following Query, that gives me an Error
<br />
<br />
<br />
SELECT IDENTITY(int, 1,1) AS REC_NO, LIS_KEY, <br />
FUNC_KEY, <br />
UNIT_NO, RIGHTS_ZONING, USE_CODE, OWNER, RATEABILITY, <br />
EXCLUSION, MARKET_VALUE, EFFECTIVE_DATE, CATEGORY_CODE, <br />
SUPPLE_NUM, AREA, PROPERTY_ID, OLD_MARKET_VALUE, VALUATION_ID, <br />
REASON, SORT_DATE<br />
INTO SDE.EXPORT_OITPS_GV_VUYISWA<br />
FROM<br />
(<br />
SELECT DISTINCT TOP 100 PERCENT P.LIS_KEY, <br />
CASE LEN(FUNC_KEY)<br />
WHEN 8 THEN SUBSTRING(NEW_ATTRIB_CODE,5,1)+ '0' + SUBSTRING(FUNC_KEY, 6, 3)<br />
ELSE SUBSTRING(FUNC_KEY,5, 5)<br />
End AS UNIT_NO,<br />
V.NEW_ATTRIB_CODE, P.ATTRIB_CODE,<br />
SUBSTRING(NEW_ATTRIB_CODE, 1, 2) AS RIGHTS_ZONING, <br />
SUBSTRING(NEW_ATTRIB_CODE, 3, 2) AS USE_CODE, <br />
ISNULL(OWN_NAME, '') AS OWNER, <br />
CASE LEN(FUNC_KEY)<br />
WHEN 8 THEN SUBSTRING(FUNC_KEY, 1, 5) + '0' + SUBSTRING(FUNC_KEY, 6, 3)<br />
ELSE FUNC_KEY<br />
END AS FUNC_KEY,<br />
CASE SUBSTRING(P.ATTRIB_CODE, 7, 1) <br />
WHEN '1' THEN 'R'<br />
WHEN '4' THEN 'E'<br />
ELSE 'N'<br />
END AS RATEABILITY, -- CASE 7TH WHEN 1 = R , 4 = E, ELSE = N<br />
CASE --P.PROP_CATEGORY_ID<br />
WHEN P.PROP_CATEGORY_ID = '2' THEN 'RES'<br />
WHEN P.PROP_CATEGORY_ID = '4' THEN 'PSI'<br />
WHEN SUBSTRING(V.NEW_ATTRIB_CODE, 7, 2) = '41' OR SUBSTRING(V.NEW_ATTRIB_CODE, 7, 2) = '42' THEN 'REL'<br />
ELSE ' ' <br />
END AS EXCLUSION,<br />
CONVERT(DECIMAL, ISNULL(NEW_IMPROVED_VALUE, -1)) AS MARKET_VALUE, <br />
(<br />
CASE WHEN CONVERT(VARCHAR,DATEPART(DD, EFFECTIVE_DATE)) < 10<br />
THEN '0' + CONVERT(VARCHAR,DATEPART(DD, EFFECTIVE_DATE))<br />
ELSE CONVERT(VARCHAR,DATEPART(DD, EFFECTIVE_DATE))<br />
END +<br />
CASE WHEN CONVERT(VARCHAR,DATEPART(MM, EFFECTIVE_DATE)) < 10<br />
THEN '0' + CONVERT(VARCHAR,DATEPART(MM, EFFECTIVE_DATE))<br />
ELSE CONVERT(VARCHAR,DATEPART(MM, EFFECTIVE_DATE))<br />
END + <br />
CONVERT(VARCHAR, DATEPART(YYYY, EFFECTIVE_DATE))) AS EFFECTIVE_DATE,<br />
(SELECT SUBSTRING(NEW_ATTRIB_CODE,7,2)<br />
FROM SDE.VALUATION V INNER JOIN SDE.PROPERTY PS<br />
ON V.PROPERTY_ID = PS.PROPERTY_ID<br />
) AS CATEGORY_CODE,<br />
' ' AS SUPPLE_NUM,<br />
CONVERT(INT, ISNULL(P.ACTUAL_EXTENT, 0)) AS AREA, P.PROPERTY_ID,<br />
0 AS OLD_MARKET_VALUE, VALUATION_ID, LU_V.VAL_REASON AS REASON, <br />
V.STATUS_DATE AS SORT_DATE<br />
<br />
FROM sde.PROPERTY P INNER JOIN SDE.VALUATION V--sde.VALUATION V<br />
ON P.PROPERTY_ID = V.PROPERTY_ID<br />
LEFT JOIN ( SELECT GISCODE, MIN(OWN_NAME) AS OWN_NAME<br />
FROM SDE.VW_PROPERTY_DEED<br />
GROUP BY GISCODE) D<br />
ON P.LIS_KEY = D.GISCODE<br />
LEFT JOIN SDE.LU_VAL_REASON LU_V<br />
ON V.VAL_REASON_ID = LU_V.VAL_REASON_ID<br />
WHERE --(<br />
--V.PROCESS_DATE IS NULL) AND <br />
P.ARCHIVE_DATE IS NULL AND<br />
V.ARCHIVE_DATE IS NULL AND<br />
V.VAL_REASON_ID = 1 AND <br />
LU_V.MULTIPLE = 0 AND<br />
V.EFFECTIVE_DATE = '2008/07/01'<br />
ORDER BY SORT_DATE<br />
) TMP
It gives me an Error
Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Warning: Null value is eliminated by an aggregate or other SET operation.
(0 row(s) affected)
How can i Fix this query
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Somewhere you have to use TOP 1 on your subquery to fix it.
I Love T-SQL
Don't torture yourself,let the life to do it for you.
|
|
|
|
|
Thanks i have Fixed it, the Top 1 was supposed to be in
<br />
(SELECT top 1 SUBSTRING(NEW_ATTRIB_CODE,7,2)<br />
FROM SDE.VALUATION V INNER JOIN SDE.PROPERTY PS<br />
ON V.PROPERTY_ID = PS.PROPERTY_ID<br />
) AS CATEGORY_CODE,
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Great
I Love T-SQL
Don't torture yourself,let the life to do it for you.
|
|
|
|
|
Vuyiswa wrote:
Msg 512, Level 16, State 1, Line 6Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.Warning: Null value is eliminated by an aggregate or other SET operation.(0 row(s) affected)
Tells you the problem, your subquery is returning more than 1 row. Eithe rfix it by using SELECT TOP 1 ... or better yet write the query so the subquery can only return 1 value
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hiii..pls help me
1.How can i avoid insertion of null values into a foreign key attribute column....
-thr s a table EMPDETAILS with columns empid,name,age
-empid in EMPDETAILS foreign key references empid in
LOGIN table.
-LOGIN table has some data.
-when i insert values into name & age of EMPDETAILS
table, the values r inserted with NULL values for empid
field.
How can i solve this?????
2.How can i insert values into empid of EMPDETAILS table & LOGIN table at the same time,without using IDENTITY constraint????.......
-empid in LOGIN table s d primary key & in EMPDETAILS s d foreign key.
- i have to insert a string value for empid
pls help
|
|
|
|
|
Arun Krishnan wrote: -when i insert values into name & age of EMPDETAILS
table, the values r inserted with NULL values for empid
field.
Even though it is a foreign key you still need to insert the data. Foreign keys are for referential integrity only.
Arun Krishnan wrote: 2.How can i insert values into empid of EMPDETAILS table & LOGIN table at the same time,without using IDENTITY constraint????.......
-empid in LOGIN table s d primary key & in EMPDETAILS s d foreign key.
- i have to insert a string value for empid
Insert into the LOGIN table first, then the EMPDETAILS
Finally I suggest you try google to look for foreign keys as you clearly do not really understand them.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Arun Krishnan wrote: -thr s a table EMPDETAILS with columns empid,name,age
-empid in EMPDETAILS foreign key references empid in
LOGIN table.
-LOGIN table has some data.
-when i insert values into name & age of EMPDETAILS
table, the values r inserted with NULL values for empid
field.
How can i solve this?????
Do you pass the empid? if not, how do you expect the database to know which one do you mean?
Arun Krishnan wrote: How can i insert values into empid of EMPDETAILS table & LOGIN table at the same time,without using IDENTITY constraint????.......
-empid in LOGIN table s d primary key & in EMPDETAILS s d foreign key.
- i have to insert a string value for empid
No problem, insert into EMPDETAILS table the same value of empid that you insert into LOGIN.
By the way, using s d instead of is the does not save you a lot of your time, but makes reading your posts harder.
|
|
|
|
|
|
First, I would make the foreighn key field a NOT NULL field to prevent accidental insert without a non-null value for EMPDETAILS.EMPID.
2nd, make EMPDETAILS.EMPID have a Foreign Key constraint - this would require that the value supplied be a value that exisits in LOGIN.EMPID column
Alter table EMPDETAILS (Alter column EMPID NOT NULL)
ALter table EMPDETAILS ADD CONSTRAINT FK_LOGIN FOREIGN_KEY (EMPID) references LOGIN (EMPID)
Create a stored procedure for the combined insert:
(use appropriate types and sizes for your tables,
this is just an example of how, hot a solution)
Create procedure AddNewEMPID (@Empid nvarchar (20), @name nvarchar(20), @age int, @loginstuff1 varchar(10),...,@loginstuffN int)
as
begin
insert into login (empid, lofinstuff1,...loginstuffN) values (@empid, @loginstuff1,...,@loginstuffN);
insert into EMPDETAILS(empid,name,age) values (@empid, @name,@age);
end
call the stored procedure with values for the parameters either in code (you didn't say what language, but most have a database library like ADO.net, ADO, ODBC etc. that support parameterized query execution)
of in sql query analizer as
exec AddNewEMPID 'empidstring','l1,...,'ln', 'name','age'
|
|
|
|
|
DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. But when i'm using the following T-SQL as given below also doing rolling back with Truncate
Begin Tran
Truncate Table Rough1
Rollback
Plz make me clear.............
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
