Is there a simple command to find the 18th occurance/position of ";" in a field and I need to do this in the SQL code.
I can use charindex to find the 1st position and I can write a function to keep chopping off the data at the start of the field until I get to the 18th occurance but it takes 18 lines or so of code.
Is there a simpler way of doing this?
I need to find the 18th occurance of ";" and then take the next 4 characters.
Hello,
I'm trying to import data from an Excel sheet into a table. Not all of them is imported.
Exl: 0,000801054857569349 becomes
Sql: 0,000801054857569350 when it is imported.
The column in SQL-Server is defined as DECIMAL 28.18, should take all 18 numbers i thought. Tried 28.19 also but it only added another zero at the end.
I've tried importing via DTS and manually import,same result both times.
Any suggestions?
Hi!
I have a problem with a sql script.
This little nice script works fine by itself.
select top 1 MWh from
(
select Top 2 Mwh, tDeklarationElleverantorID from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID
and tDeklarationElleverantor.Borttagen IS NULL order by DeklarationElleverantorID
) as [Elleverantor 2 MWh]
But when I put in my long select it doesnt work. Also the long script works fine without these rows inserted. I hope someone can tell me why.
//Tommy
I post the entire script and the error message can be found below.
Msg 156, Level 15, State 1, Line 59
Incorrect syntax near the keyword 'select'.
Msg 170, Level 15, State 1, Line 114
Line 114: Incorrect syntax near '-'.
SELECT tDeklaration.Diarienummer,
IO.OrgNr,
IO.OrgNamn,
tDeklaration.KontaktpersonNamn,
tDeklaration.KontaktpersonEpost,
tDeklaration.KontaktpersonTele,
(select sum(tForbrukadEgen.EgenProduceradEl) from tForbrukadEgen where tForbrukadEgen.DeklarationID = tDeklaration.DeklarationID) as [Egen producerad],
-- Hämta ut Orgnamnet för en Elleverantör. Finns det inte i Tabellen ta intressentID:t och hoppa iväg till IREG.
(select
Case When
(Select OrgNamn from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) IS NULL
then
(Select intressentregister_skarp.dbo.tIntressentOrganisation.OrgNamn from intressentregister_skarp.dbo.tIntressentOrganisation Where intressentregister_skarp.dbo.tIntressentOrganisation.IntressentID =
(SELECT top 1 IntressentID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL ))
ELse (Select OrgNamn from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) End) [Elleverantor 1 Namn],
(select
Case When
(Select OrgNr from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) IS NULL
then
(Select intressentregister_skarp.dbo.tIntressentOrganisation.OrgNr from intressentregister_skarp.dbo.tIntressentOrganisation Where intressentregister_skarp.dbo.tIntressentOrganisation.IntressentID =
(SELECT top 1 IntressentID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL ))
ELse (Select OrgNr from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) End) [Elleverantor 1 OrgNr],
(Select MWh from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) as [Elleverantor 1 MWh],
-- Here is the problem.......
select top 1 MWh from
(
select Top 2 Mwh, tDeklarationElleverantorID from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID
and tDeklarationElleverantor.Borttagen IS NULL order by DeklarationElleverantorID
) as [Elleverantor 2 MWh],
(select (select sum(tDeklarationElleverantor.MWh) from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID)
+ (select sum(tForbrukadEgen.EgenProduceradEl) from tForbrukadEgen where tForbrukadEgen.DeklarationID = tDeklaration.DeklarationID)
) as [Summa använd och vidare fakturerad],
ElTillverkning AS Tillverkningsprocess,
ElDel AS [Tillverkningsprocess del av år],
ForsaljningsVarde AS Försäljningsvärde,
(select
Case When ElTillverkning > 0 or Forsaljningsvarde > 0 Then ElTillverkning/Forsaljningsvarde
ELse 0 End) As Kvot,
(select
Case When ElTillverkning <= 0 or Forsaljningsvarde <= 0 Then 0
When ElTillverkning/Forsaljningsvarde < 40 then 0
When ElTillverkning/Forsaljningsvarde >= 40 and ElTillverkning/Forsaljningsvarde < 50 then ElTillverkning/Forsaljningsvarde * 0.5
When ElTillverkning/Forsaljningsvarde >= 50 and ElTillverkning/Forsaljningsvarde < 60 then ElTillverkning/Forsaljningsvarde * 0.75
ELse ElTillverkning/Forsaljningsvarde End) as [Undantagen elmängd],
(select
(select SUM(tHjalpkraft.Bruttoproduktion * tSchablonAr.Schablon)/100 from
tHjalpkraft INNER JOIN tSchablonAr ON tHjalpkraft.KraftslagID = tSchablonAr.KraftslagID
where tHjalpkraft.DeklarationID = tDeklaration.DeklarationID and tSchablonAr.Ar=2007)
+
(select SUM( tFaktiskHjalpkraft.NettoProduktion) from tFaktiskHjalpkraft where tDeklaration.DeklarationID = tFaktiskHjalpkraft.DeklarationID)) AS [Hjälpkraft],
--Summa avdrag för vidare fakturerad el (ny kolumn, summan under rubrik 5)
(select SUM(tFornybarFrom07.Fornybar) from tFornybarFrom07 where tFornybarFrom07.DeklarationID = tDeklaration.DeklarationID) AS [Förnybar],
tDeklaration.Kvotplikt,
tDeklaration.BegartAnnullerat as Annullering,
tDeklaration.Ovrigt as [Övriga Upplysningar],
tDeklaration.Kvotplikt - tDeklaration.Annullerat AS Saknas,
tDeklaration.DeklarationStatusID,
tDeklarationStatus.StatusIntern
FROM tDeklaration
INNER JOIN tDeklarationStatus on tDeklaration.DeklarationStatusID = tDeklarationStatus.DeklarationStatusID
INNER JOIN tRegistreradKvotpliktIntressent ON tDeklaration.RegistreradKvotpliktID = tRegistreradKvotpliktIntressent.RegistreradKvotpliktID
INNER JOIN tIntressentforadOrg ON tIntressentforadOrg.IntressentforadOrgID = tRegistreradKvotpliktIntressent.IntressentForadOrgID
LEFT OUTER JOIN intressentregister_skarp.dbo.tIntressentOrganisation IO ON IO.IntressentID = tIntressentforadOrg.ForadAgarID
--left outer join tDeklarationElleverantor on tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID
WHERE (tDeklaration.AktuellIntern = 1) AND (tDeklaration.KategoriID = 3) AND (tDeklaration.DeklarationStatusID <> 1) AND
(tDeklaration.DeklarationStatusID <> 3)and tdeklaration.kvotpliktar=2007
order by IO.OrgNamn
Hey all:
I have a table that contains the following records:
ptrecidpaidbyamtchequenoname
4791A X A1200097760LOWE, Bernard
4791A X A4380097760LOWE, Bernard
4791A X A620106406LOWE, Bernard
I need to create a view that returns only the FIRST occurance of each distinct ptrecid. I do NOT want to sum amt/chequeno, but rather return only the values in the first record. Using a group by gets me close, but since the amt and chequeno are not unique, they do not group. Min also does not work, as 620 is returned for amt, and 097760 is returned for chequeno.
The desired result from this query would return:
4791A X A1200097760LOWE, Bernard
Any help on this is greatly appreciated.
I am trying to create a view that shows the first occurance of a particular value in a column.
For example, I have a table that contains multiple part numbers that can be associated with mulitple customers. The first occurance of the part number in the table is associated with the main customer for that part number. I want this view to only show the main customer for each part number.
I am able to do this in Access using the following SQL statement:
SELECT dbo_CustPattMast.PATTERN_NUM, First (dbo_CustPattMast.CUST_NUM)
FROM dbo_CustPattMast
GROUP BY dbo_CustPattMast.PATTERN_NUM
When trying to create the view in the SQL Manager, it will not allow me to use the First statement.
Thanks in advance for your help,
Jennifer
I am making a sql query and its bring back hundreds of results but I only need the first one. I am aware that getting the first one will be faster as well. Is there an efficient way to do this?
View 2 Replies View RelatedDeclare @MYTable Table
(
RecordID int identity,
EmployeeID int,
JobCode varchar(4)
)
Insert into @MYTable Values(1,'123')
Insert into @MYTable Values(2,'123')
Insert into @MYTAble Values (1,'123')
Insert into @MyTable Values (2, '123')
Insert into @MyTable Values (3,'123')
Insert into @MYTable Values (1,'222')
Insert into @MYtable Values (3,'222')
Insert into @MYTable Values (1,'222')
Insert into @MyTable Values (4,'222')
Insert into @MyTable Values (5,'123')
Insert into @MyTable Values (6,'123')
Insert into @MyTable Values (7,'457')
Select * from @Mytable
The each employee can come to this table multiple time however
the job code should be different for each every single record.
So If I have to check this error and see which record has been repeated with
the same Employee ID and JobCode and how many times. how would i do that
Thanks,
Hi friends,
I have a two table with following fields, table names are tbl_userinfo, tbl_Property.
tbl_userinfo fields are
user_id name
1 dhin
2 Mike
3 sam
4 Red
tbl_Property fields are
prpty_Id User_Id Address
1 1 3CostalRoad
2 1 westbengal
3 2 Loasass
what i want to do is, if tbl_info User_id occures in tbl_property, i want to display that full info abt tbl_userinfo
after comparing two tables Expected result is
user_id name
1 dhin
2 Mike
Please help me how to do this
Hello all
my problem is like this
I have a windows nt server 4.0 .its having IIS4.0 as well as Ms sql server 7.0 installed. I am using ASP ( active server pages) to store the fields entered by the customers in the order processing form in my site( thats the default page--form)
this form calls the asp file and asp file successfully stores the fields in the corresponding table.Now the problem is that how can i configure my sql server in such a way that sql server can mail me( as i am the administrator) informing the new order placed by the customer.
I know sql mail , and i have already configured that also. but how can i create the trigger for the table updation?
i tried using the enterprise manager, i was using xp_sendmail. but sql says "the stored procedure can't be found"
so anyone who knows this topic , respond please
Regards
Shabu
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies View RelatedI need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
View 2 Replies View RelatedDECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________
The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
View 1 Replies View RelatedI have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
Inserted Value -> Result in table
123456-8 -> 123456-8 *Correct
1234567-9 -> 1234567- *Wrong
123456789 -> 123456789 *Correct
1234567#9 -> 1234567# *Wrong
1234567a9 -> 1234567a9 *Correct
Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?
I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.
How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???
View 3 Replies View RelatedI need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
View 12 Replies View RelatedHi,
I'm importing an Access database to SQL Server 2000.
The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import?
I would appreciate any pointers.
We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.
ie.
Code Description Brand
ABC1 BLANK DVD SONY
ABC2 SONY BLANK DVD SONY
what I need to do is identify where the Brand is in the Description field ...
I have tried ;
select * from Table
where Description Like Brand
not very successful.
I want to update a field with a trigger only if a specific field is updated.
When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?
ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
i want to import/copy a varchar field numeric data in to number field pls suggest the solution
one thing more can i convert field type of a table how?
jto it
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View 26 Replies View RelatedI am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View 2 Replies View RelatedI know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
View 1 Replies View RelatedHi all,
i've a reasonable amount of experience with MS Access and less
experience with SQL Server. I've just written an .NET application that
uses an SQL Server database. I need to collate lots of data from around
the company in the simplest way, that can then be loaded into the SQL
Server database.
I decided to collect the info in Excel because that's what most people
know best and is the quickest to use. The idea being i could just copy
and paste the records directly into the SQL Server database table (in
the same format) using the SQL Server Management Studio, for
example.
Trouble is, i have a problem with line feed characters. If an Excel
cell contains a chunk of text with line breaks (Chr(10) or Chr(13))
then the copy'n'paste doesn't work - only the text up to the first line
break is pasted into the SQL Server database cell. The rest is not
pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a
memo field into SQL Server database, but with exactly the same problem.
I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases
interacting together, can someone suggest the simplest way of
transferring the data without getting this problem with the line feeds?
I don't want to spend hours writing scripts/programs when it's just
this linefeed problem that is preventing the whole lot just being
cut'n'pasted in 5 seconds!
cheers
Dominic
Hello,
I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.
How can I remove all speical characters and then convert that field to an int?
Also I tried the following query which did not work as well, same error about conversion.
UPDATE myTable SET field = CAST(field AS int)
Hi,
Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters.
Thanks.
I need formulating a view through which I can create a an output like below image -
Monthly Table
NUM STATUS ACTIVITYCODE
HAX603 Completed 0x45845a
HAX317 Completed 0x112z44
HAX465 Completed 0x1155x4
HAX523 Completed 0x124c69
Season Table
NUM STARTMONTH STARTDAY ENDMONTH EMDDAY
HAX603 JULY 1 OCTOBER 31
HAX317 DECEMBER 1 DECEMBER 31
HAX317 MARCH 1 MARCH 31
HAX317 July 1 July 28
[Code] ...
Final Output
NUM STATUS ACTIVITYCODE <SEASONS>
HAX603 Completed 0x45845a 1 JULY - 31 OCTOBER, 1 DECEMBER - 31 DECEMBER
HAX317 Completed 0x112z44 1 DECEMBER - 31 DECEMBER, 1 MARCH - 31 MARCH, 1 July - 30 July
HAX465 Completed 0x1155x4 1 MARCH - 31 MARCH, 1 July - 28 July, 1 August - 30 August
HAX523 Completed 0x124c69 1 November - 30 November
I have written a query to join the values of multiple field, but lacking in as how will I formulate a view which will check for the duplicate values of Num fields and merge there values in a single field like season.
select num, (CAST(startday AS VARCHAR(3)) + ' ' + startmonth + ' - ' + CAST(endday AS VARCHAR(3)) + ' ' + endmonth)AS Season from seasons;
Hi all experters,
Please suggest how to build the report in below case:
Raw data:
ID
Member
Functions
1
Alan
A
1
Alan
B
2
Tom
A
2
Tom
B
2
Tom
C
3
Mary
D
3
Mary
E
Report Shows:
ID
Member
Functions
1
Alan
A,B
2
Tom
A,B,C
3
Mary
D,E
I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.
Any good suggestion?
Thanks in advance,
Steve Wang
2008/4/2