Hi forum
I need some advice regards setting a serial prefix to PK ID colomn. Currently it starts at row 1 with increased increments of 1. Thats all good! but I would like is to add a prefix before the 1, ie W2W01/1 next W2W01/2 an so on. Many thanks Paul
Hello every body
i am using a view grid to display data
the viewgrid is tied to an sqldatasource control
i want to give the user the ability of modifing data by colomn instead of by row
how could i do ?
ghassan
How to generate serial numbers ? I had already tired ident. i am getting the error. Can any people who is willing to write a syntax for me.
In sybase if we use Number * function. It will automatically generates the serial numbers from 1 to n. similarly i need the same function in SQL server 7.0 so that my problem will be solved...
I am converting sybase stored procedure into sql server stored procedure that is why i am asking about that. i am struggling hard to find an answer...
please help me in this...
urs
vj
hi,
i am new here and interested in how to :
- execute serial dts in one single action or using vb script.
- change the status (enabled to disabled) and schedule using vb script.
Many dts make me tired when i have to change the status and schedule one by one.
regards.
This isn't so much purely a SQL Server question as a question on ASP.NET VB technique. In particular, I have a situation where I am either inserting a NEW row for a "profile table" (name, email, etc.) or Updating an existing one. In both cases, I need to create a new row in a related table which has the identity/serial column of the parent table as the primary key for the data to be inserted into this subsidiary table (for which there may be many rows inserted, all tying back to the parent).
At the time I do the update, of course, I have the identity/serial of the "parent" so it's easy to update/insert. However, if the profile is NEW, I need to capture the identity/serial which was inserted so as to use it for the child table insert. (I remember a call to an obscure function which was -- essentially -- "give me the identity/serial of that which was just INSERTed" but I am unable to locate equivalent functionality. (I have searched various online help files for "Insert serial", "Insert identity" and the like with no results.
Hints? Mahalos in advance ... :) KevInKauai
Hey folks,
Please give me the correct function name, otherwise please ignore in sending reply. I had used all the functions like identity, ident and so on. I need while selecting a querry, i need to generate serial numbers. I dont have identity column in my table. But, i need to generate serial numbers..
In Sybase SQL Any where, we have a function called Number (*) which will in turn generate serial numbers like ex..
Select Number (*) from x
The output will be
Number(*)
1
2
3
4
5
6
and so on..
I need a equivalent function in SQL Server 7.0 in which if i do select on that particular function with a table i should return above values..
Can any one solve this issue...
Please help me in this....
Urs
VJ
Hi,
I have a table with a primary key, what I really need is something like an IDENTITY, but with the character 'X' and the last to digits of the year added on the front. Is there another way to update the field automatically like an IDENTITY would do, automatically incrementing as fields are inserted.
hi forum
how to add Serial No in "Report.rdlc". I want to add SNo. in as a extra field in Report.
SNo. ItemName Price
1 abc 10
2 xyz 20
How to set or send output on serial port in C# .net
View 1 Replies View RelatedI need a query to find max serial number by comparing two different tables. Here is my requirementI am having two tables named Table1 and Table2. Each tables having more than 30,000,000 records.I want a simple query to find Max srno from two tables.For exampleIf Table1 max is 245 where partno=2 and ano=2and Table2 max is 343 where partno=2 and ano=2Then 343 is max serial noIf Table1 max is 435 where partno=2 and ano=2and Table2 max is 34 where partno=2 and ano=2Then 435 is max serial noI used this query but its taking more time select max(v.MaxSrNo) from ((select max(MaxSrNo) as MaxSrNo from Table1 where partno=@partno and ano=@ano)union all (select max(MaxSrNo) from Table2 where partno=@partno and ano=@ano)) as v Pls give me a simple query to find max srno.
View 3 Replies View RelatedIn microsoft sqlserver 7.0 i like to what is function name to generate serial numbers in a table.
For ex: in sybase we have number* which will automatically generate unique
numbers for each row.
It will be great if you could reply at the earliest
Tanks
vijay
I need to put the serial numbers for results in group in SQL Server 2000. Please see below:
My input:
procedureid procname
1 A
1 B
2 A
2 B
2 C
2 D
3 A
3 B
3 C
Output I need:
procedureid procname serial_num
1 A 1
1 B 2
2 A 1
2 B 2
2 C 3
2 D 4
3 A 1
3 B 2
3 C 3
Here is my table:
create table po(
procedureid int,
procname varchar(10),
)
insert into po values (1,'A')
insert into po values (1,'B')
[Code] ....
Good morning !Anyone knows how can i change a SQL Server Serial number without uninstallit ?Thanks in advancedJLuis
View 1 Replies View Related
Hi, guys,
I'm using MSSQL 2005, so I would like to know how to generate Serial no into database?
for example:
- serial no will be increase for each time confirmation has been done, The Serial no will insert into a new field as below:
00000001
00000002
00000003
and next...
thanks and regards
Am I right in saying that the MS Full-text search engine does not support a wildcard at the beginning of the word? Only at the end? E.g. "VB*" works but "*NET" doesn't.
View 2 Replies View RelatedMy problem is one of my query is returing party_codes . now i also want a column which returns serial numbers along with it . the serial numbers are not stored anywhere. they should be autogenerated. My query is combining two different databases and its using union in it so i can not use count in it . Is there any other way i can acheive . for eg Now my query output is party_code ---------- R06048 R06600 R06791 (3 row(s) affected) I want it like party_code serial number ---------- ------------- R06048 1 R06600 2 R06791 3 (3 row(s) affected)
The serial number column should be auto generated in the select statement it self Is there any system rowid i can use . Please suggest
Hello Friends
My problem is
Suppose Query is - Select * from tbl_Employee
TBL_EMPLOYEE HAS ONLY TWO COLUMNS NAME,POST
I need that an extra column get inserted in result through query showing serial Number with each row.So that query result look like this.
Serial Name Post
1 XYZ QER
2 SDF OPO
3 WER IPO
If any body knows please post the solution its urgent.
Add sno 1 to end of the coloum
I have table like ...
400000 laks rows in my table
name address id location
raju hyd 6789 hyd
vamshi vizag 9876 hashinapur
o/p like
I want insert sno coloum and sno 1 to 400000
sno name address id location
1 raju hyd 6789 hyd
2 vamshi vizag 9876 hasthinapur
3
4
5
6
..
.
.
.
.
40000
I am in need of converting serial date to regular date ie...735510.40461 and only need the hours, minutes and seconds, I have used the examples I've seen on different forums,
DATEADD(d, -1, DATEADD(m, DATEDIFF(m, '1900-1-1', getdate()) + 1, '1900-1-1'))
select SERIALNO, Max(TIME)
from dbo.TASK A
join dbo.Status B
on A.TID=B.TID
where A.ID in ('1111',2222')
and A.TYPE='Pen'
group by B.SERIAL_NO, B.TIME
order by BM.TIME
For this query I may get serial no duplicates but times are unique
For that serial no, I have to find the recent time. But if I use group by, I am getting the wrong no
I need a new field added 'Field1' which will add SEQUENCE number 1,2,.. based ON GROUP BY MasterID..AND another field TotalCount which will COUNT total masterID (here it will be 2)
CREATE TABLE #Temp1
( MasterID INT,
ClientName VARCHAR(10),
ProductName VARCHAR(50)
[code]...
I have two tables. One table has empty column (ID) and I want to generate serial numbers in that column based two tables columns(LoanNum) condition.
Table A
ID LoanNum
Null 1234
Null 2345
Null 3456
Null 4567
Null 5678
Null 6789
Table B
LoanNum
1234
2345
3456
4567
5678
6789
2324
4352
4235
Id is not primary key, but should not duplicates keys, it is just a column want to generate serial number (1,2,3,4...etc) How to generate?
im trying to run a query, my first column should be serial number based on results, how can i do that for example
qry = "Select products, sum(prod_price), sum(prod_sellingprice) from sales group by products"
or
qry = "Select products, sellingperson from sales where seldate=#" & date() & "#"
i want first column of both queries as serial number based on results of query.
Hi, I have created a table with the following
docmd.runSQL "Create Table #Test(A int)"
in Access 2000 and cannot find the table on my table list, it's not on my enterprise manager either. My guess is that maybe it has something to do with the prefix #? So I started searching for "Create table #" on google and dbforums and cannot find anything. Can someone tell me what's special about having a #prefix on a table and where can I find more information about it?
Thanks
SHK
Good Morning Db forum.
I am working on a query that I need to pull all fields that contain 3 alpha characters. for example BCB001, MCR001, CHP001 and so forth.
Is there a SQL alpha wildcard that I could use to pull all records that have the three alpha chars?
I have this table of Marks as shown below. All I need is to find the average Marks at various intervals of S.no. That is I need averages at every 3rd S.No. as shown.
S.No. Marks
1 ------ 5
2 ------ 5
3 ------ 6 1st Average Value here (16/3)
4 ------ 5
5 ------ 6
6 ------ 7 2nd Average Value here (18/3)
7 ------ 7
8 ------ 7
9 ------ 8 3rd Average Value here (22/3)
10 ----- 8
11 ----- 9
12 ----- 8 4th Average Value here (26/3)
So basically I need a new table which will have 4 average values for the table above. Of-course the table can be much bigger and the average values can be at any nth value of S.No.
I have Logs table and want to assign a serial number to the techs using the following query
Select
Date,
Case_ID,
Site,
Dept,
Tech,
Start_Time,
ROW_NUMBER () OVER (PARTITION BY Date, Site, Dept, Tech ORDER BY Start_Time) as Row_Num
FROM
Logs
Where Date = Getdate()
I get the following results.
Date Case ID Site DeptTechStart TimeRow_Num
7/28/14 10023 TartvilleMaintcAmy P.7:301
7/28/14 56789 TartvilleMaintcRem W.8:051
7/28/14 23098 TartvilleMaintcAmy P.8:352
7/28/14 70004 TartvilleMaintcAmy P.9:103
7/28/14 12708 TartvilleMaintcMag O.10:001
7/28/14 10004 TartvilleMaintcAmy P.12:304
7/28/14 40056 TartvilleServiceJoe F.7:301
7/28/14 23458 TartvilleServiceJoe F.7:552
7/28/14 69200 TartvilleServiceRus T. 7:301
Please notice the cases in Maintc department. See how Amy P.'s shift is broken by Rem W. and Mag O. But the Row Number does not recognize this, it still says Amy P's case as 2 and 4 the even though Rem's and Mag's cases were in between.
This is what I really wanted.
Date Case ID Site DeptTechStart TimeRow_Num
7/28/14 10023 TartvilleMaintcAmy P.7:301
7/28/14 56789 TartvilleMaintcRem W.8:051
7/28/14 23098 TartvilleMaintcAmy P.8:351
7/28/14 70004 TartvilleMaintcAmy P.9:102
7/28/14 12708 TartvilleMaintcMag O.10:001
7/28/14 10004 TartvilleMaintcAmy P.12:301
7/28/14 40056 TartvilleServiceJoe F.7:301
7/28/14 23458 TartvilleServiceJoe F.7:552
7/28/14 69200 TartvilleServiceRus T. 7:301
I tried many combination of columns for Partition by () and Order by () and the best I can get is the result at the top. How should I achieve it.
Dear All,I'm attempting to create a query that will transpose repeated fieldsinto a single table structure. Can anyone think of how this can be doneas I'm stumped at the minute? I'd like to do this without having tocreate a cursor due to the overheads and performance issues associatedwith cursors. The table may also include additional fields which I'mnot interested in.Serial Data is like this.............IkeyIval-----------------------------------------------RAF_EMAILJoin Bytes!RAF_FIRSTNAMEtestFirstName1RAF_LASTNAMEtestLastname1RAF_EMAILJoin Bytes!RAF_FIRSTNAMEtestFirstName2RAF_LASTNAMEtestLastname2....Transposed into table like this ..............EmailFirstnameLastname--------------------------------------------------------------------------Join Bytes!testFirstName1testLastname1Join Bytes!testFirstName2testLastname2....Any help, much appreciated ...Kind Regards,Tim-------------------------------------------------------------------------------------NOTE: these create temporary tables ....DECLARE @XML TABLE(ikey VARCHAR(200),ival VARCHAR(1000))INSERT INTO @XMLSELECT 'RAF_EMAIL', 'testemail1@hotmail.com'UNION ALL SELECT 'RAF_FIRSTNAME', 'testFirstName1'UNION ALLSELECT 'RAF_LASTNAME', 'testLastname1'UNION ALLSELECT 'RAF_EMAIL', 'testemail2@hotmail.com'UNION ALL SELECT 'RAF_FIRSTNAME', 'testFirstName2'UNION ALLSELECT 'RAF_LASTNAME', 'testLastname2'UNION ALLSELECT 'FORM_CATEGORY', 'nothing'UNION ALLSELECT 'NO_DOGS', '1'DECLARE @RESULTS(EMAIL,FIRSTNAME,LASTNAME)
View 1 Replies View RelatedWhat does the ap_ stand for as a prefix to stored procedures?
A DBA tried to teach me something about the prefixes etc.. but I can't recall what it meant.
In this case ap_ may represent "ONE" persons naming convention?
something procedure...
other common ones in our database are dt_ and zz_
SQL 2000
I am testing a query for use in Crystal Reports. It was copied from an existing query with the necessary adjustments. The first part of it works correctly;
SELECT NA.*
into #cl_temp
FROM OLT.dbo.NACBTR NA
WHERE NA.CourseCode in ('RGF00001','RGF00002','RGF00005','RGF00006', 'RGF00038','RGF00039','RGF00040','RGF00041','RGF00042','RGF00043') And
NA.completedDate >= '01/01/2006' and
NOT EXISTS (SELECT * FROM hrdw.dbo.E_View EV WHERE NA.ssn = EV.ssn)
but when I add the second line;
select #cl_temp.*,
ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp
I receive the error message:
The column prefix '#cl_temp' does not match with a table name or alias name used in the query.
Any ideas?
Thanks,
Tom
btw, sql newbie.
I have an Access database, that is in connection with sql server.
On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] )
SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price
FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says:quote:
ODBC call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]
The column prefix 'MS1' does not match with a table name or alias name used in the query.
The column prefix 'MS2' does not match with a table name or alias name used in the query.
And it will give this error 6 times.
I dont know what to do.
Can anybody help me?
Suppose there is a table containing these recodes.country-------CON_CHNCON_JAPJAPCON_CHNWhen I use the following sql:select country, count(*) as num from table group by countrythe normal result will be:country num---------------CON_CHN2CON_JAP 1JAP1However, my desired result is as follows:country num-----------------CON_CHN2CON_JAP 2How can I re-write my SQL? Or any other methods to do that?
View 2 Replies View RelatedHi,I would like to know details about the table name starts with prefixin sql server 2000.Actually i'm working on existing code.The existing code insert a record into a table, but the table name inthe code and table name in database are differentTable name in database : tbl_mmm_oxIn coding they are using table name as mmm only, the records areproperly inserted into mmm tableis it possible?The sample code is like that(using ado object)oCmd.CommandText = "INSERT INTO mmm (no,name) values (1,"mm")"The above code is perfectly working and inserting record intotbl_mmm_ox.Could anybody explain how is it possible?whether we can leave the prefix(tbl) and suffix(ox) and sql servertake care of this?Thanks & Regards,Mani
View 7 Replies View Related