Join Returns More Than One Row, Post Code Regular Expressions
Mar 23, 2006
Hi,
I trying to write a select statement that will return each of my sales
men a region code based on a table of post codes using wildcards... eg.
MK1 1AA would be matched in the region code table to MK1%
SELECT dn.DEALER_CODE, dn.NAME AS DNAME, rc.REGION_ID,
rc.POST_CODE, dn.POSTAL_CODE
FROM REGIONAL_CODES rc CROSS JOIN
DEALER_NAW dn
WHERE (dn.POSTAL_CODE LIKE rc.POST_CODE)
The above statement works BUT there are some post code areas such as
our friends in Milton Keynes that are split into two regions... eg MK1
is region id 2 and MK10 is region 3.
So a dealer with post code MK10 1AA would be matched to both rows
returning duplicates
POST_CODE REGION_ID
MK1% 2
MK10% 3
I think the answer would lie in a subquery which returns the ID of the
region with the longest length of the postcode match (e.g.
len(POST_CODE) for the rc table... return only the MAX....
any ideas????
Any help muchos appreciated, and I apologies now for the naming of the
dealers name as a reserve word... not me!
Ct
View 2 Replies
ADVERTISEMENT
Jun 24, 2008
Hi, the Microsoft SQL Server version is 2000
Basically I want use a basic regular expression but can't seem to get the syntax right.
i want to compare ID_short = ID_Long
ID_short is a truncated version of ID_Long, and I want to search on the beginning only (hence I can't use the 'LIKE' comparative on it's own).
What is the syntax to use Reg Expressions (or if anyone knows a non RegExp way of searching the beginning please let me know).
Thanks
View 5 Replies
View Related
Feb 9, 2007
Hi,
I have a crappy old database that has an email field with a lot of bad data, and I need to use SSIS to extract a list of names & email addresses.
I have an OLE DB Source set up to pull the relevant columns out of the database. Is there a way that I could setup a Regular Expression based filter to disregard any rows where my email Regex does not match the value of the email column?
Thanks,
John
View 4 Replies
View Related
Sep 27, 2007
Hi everyone,
Is it possible? I haven't idea how to implement as if it were done from conventional .Net code
I'd like to check every field for a plan file
First field NNNNNNN
Second field XXXNNNNNNN
,,
Thanks for any input or idea,
View 11 Replies
View Related
Sep 24, 2006
Hi all,
i wrote a little function that is basically supposed to give me a match of a regular expression in C#. i tested it out in VS05 and it seems to work fine. It hits the fan when i try it on SQL Query analyzer once deployed. Anyone have any idea why?
here is the C# class that I deploy to a db using VS05.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class RegExTest
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static string RegExMatch(string pattern, string matchString)
{
Regex r1 = new Regex(pattern);
if (r1.Match(matchString.TrimEnd(null)).Success)
{
Regex testReg = new Regex(pattern);
Match regMatch = testReg.Match(matchString);
string runnumber = regMatch.ToString().TrimEnd('_');
runnumber = runnumber.TrimStart('_');
return runnumber;
}
else
{
return null;
}
}
};
Basically i have a teststring that looks like abc_xxx_nnndef. xxx and nn are both numbers, and im using the pattern _\d{3}_ to test it out in SQL (i need the numbers between '_' and '_'). But it never even proceeds past the if statement in SQL i think. here is the basic SQL that i use to test the above function:
use northwind
go
declare @teststring varchar(50),
@pattern varchar(10),
@out varchar(50)
set @teststring = 'dw_012_34.ext'
set @pattern = '_\d{3}_'
set @out = dbo.RegExMatch(@pattern, @teststring )
select @teststring
select @pattern
select @out
Anyone have any suggestions as to why it works in VS 05 when I use it in a C# program, but fails on the SQL version?
View 8 Replies
View Related
Apr 27, 2007
HiI have a large table with allot of data in it, Im trying to find the correct SQL statement to select all the records that do not start with a letter or a number.So some records may start with a '~' and a few '$' and even some '?'. So I would like a statement that would select all of these while leaving out any records that start with anything else, such as numbers or letters.And on a similar theme, Im also trying to select all records that start with a number. Ive been using the "select blah from blah where LIKE 'a%'" for each letter and ive tried to have a play and search around that statement....
Today I was told to use a regex within my 'SELECT' statement, but I had never heard of this being done before... so I done a number of searches on Google and although I found references, I didnt actually find any examples. So when I tried to put something together I kept getting errors.
Ive used regular expressions before so theres no problem writing the expression that I need, however Ive no idea how to implement it into a SELECT statement.
If someone could please show me an example of the structure/syntax of an sql select query that contains a regex then I would be most appreciative! :)
View 2 Replies
View Related
Jul 20, 2005
Is it possible to use the REPLACE function in SQL Server 2000 so thatit returns a string containing only alpha-numeric characters (muchlike using regular expressions)?Thank you in advance for any suggestion.Darren.
View 1 Replies
View Related
Oct 30, 2006
How can I use regular expressions in WHERE clause directly?
select * from mytbl where myfld like '[0-9]*key'
View 3 Replies
View Related
Mar 31, 2006
Hi-
Someone posted on a thread that you can use VB (or C#) to write Regular Expressions for SQL Server 2005. I know how to write RegEx in VB, so can someone point me to a reference which explains how to use them in / with SQL2005? Or, can someone explain how?
Thanks,
-David
View 4 Replies
View Related
Apr 20, 2007
I am running PHP with SQL Server 2005. I have [bold] in some of the values in my result set, which needs to be striped out. I had a function in SQL Server2000 that did that, but the same function does not work in SQL Server2005. Any dieas?
CREATE FUNCTION
dbo.fn_regex(@pattern varchar(255), @matchstring varchar(8000))
RETURNS int
AS
BEGIN
declare @obj int
declare @res int
declare @match bit
set @match=0
exec @res=sp_OACreate 'VBScript.RegExp',@obj OUT
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OASetProperty @obj, 'Pattern', @pattern
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OASetProperty @obj, 'IgnoreCase', 1
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OAMethod @obj, 'Test',@match OUT, @matchstring
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OADestroy @obj
return @match
END
GO
View 7 Replies
View Related
Feb 11, 2008
Hello forum, I need extract a substring form a string that follows a regular expressions -->
T:1º/PQ:1/TALLA:2(MOD.51 100/150)
T:<number>º/PQ:<number>...
I need to extract only the numbers. The SUBSTRING functions is insuficient for me, because in some cases de number can be 1, 10, 100, 1000, ...
I would like me use it as:
SELECT RegularExpression (Field)
Can you help me, please.
Thanks for all in advanced. I will appreciate a lot your help.
View 3 Replies
View Related
Apr 5, 2008
help,a regular text file how to sql 2000 table code ?i have a text file as follow, line with ¡°|¡±and {LF},8|-000000186075919.|+000000000387820.|2008-03-31|20010423|9|-000000000003919.|-000000000123620.|2008-03-31|20010123|8|-000000018623419.|+000000000381230.|2008-05-30|20010423|i want to sign char(1)£¬year decimal(18,3) , month decimal(18,3), trandatesmalldatetime£¬update smalldatetime£¬after to sql table is as follow,sign year month trandate update8 -186075919.000 387820.000 3/31/2008 4/23/20019 -3919.000 -123620.000 3/31/2008 1/1/20018 -18623419.000 387820.000 5/30/2008 4/23/2001could you help me how write the sql code ?
View 1 Replies
View Related
Jun 16, 2007
Is there any way to write C# code in SQL Server Reporting Services?
View 1 Replies
View Related
Feb 5, 2015
Why does this right join return the same results as using a left (or even a full join)?There are 470 records in Account, and there are 1611 records in Contact. But any join returns 793 records.
select Contact.firstname, Contact.lastname, Account.[Account Name]
from Contact
right join Account
on Contact.[Account Name] = Account.[Account Name]
where Contact.[Account Name] = Account.[Account Name]
View 3 Replies
View Related
Feb 13, 2008
I am looking for sql code/approach to export a text file and 2 excel works sheets(from an excel workbook) in a folder, to 3 different tables in sql server, perform data scrubbing and then export the data from all 3 tables to 3 different excel sheets and post them in 3 different web parts in a share point site.
Mainly sql code(or any code that i can integrate with SQL Server) to post files to different web parts in a share point site is what i am looking for.
Any sort of pointers/suggestions would be really helpful.
Thanks & Regards,
View 4 Replies
View Related
Jan 2, 2008
Hi all,
I have the following T-SQL code of Common Table Express (CTE) that works in the SQL Server Management Studio Express (SSMSE):
--CTE.sql--
USE ChemAveRpd
GO
WITH PivotedLabTests AS
(
SELECT LT.AnalyteName, LT.Unit,
Prim = MIN(CASE S.SampleType WHEN 'Primary' THEN LT.Result END),
Dupl = MIN(CASE S.SampleType WHEN 'Duplicate' THEN LT.Result END),
QA = MIN(CASE S.SampleType WHEN 'QA' THEN LT.Result END)
FROM LabTests LT
JOIN Samples S ON LT.SampleID = S.SampleID
GROUP BY LT.AnalyteName, LT.Unit
)
SELECT AnalyteName, Unit, avg1 = (abs(Prim + Dupl)) / 2,
avg2 = (abs(Prim + QA)) / 2,
avg3 = (abs(Dupl + QA)) / 2,
RPD1 = (abs(Prim - Dupl) / abs(Prim + Dupl)) * 2,
RPD2 = (abs(Prim - QA) / abs(Prim + QA)) * 2,
RPD3 = (abs(Dupl - QA) / abs(Dupl + QA)) * 2
FROM PivotedLabTests
GO
===========================================
How can I execute this set of the CTE.sql code in the VB 2005 Express via the Stored Procedure programming?
Please help and respond.
Thanks in advance,
Scott Chang
View 1 Replies
View Related
Sep 8, 2015
I am querying with a SELECT statement against an address table that has a post code column with corrupt data.
Sample record:- Northants NN4 0NB
Should be NN4 0NB
I have used the following on another column:-
LEFT(A.Addr1,CASE WHEN CHARINDEX ('(', A.Addr1) =0 THEN LEN(A.Addr1) ELSE CHARINDEX('(' ,A.Addr1) -1 END) AS 'Address 1'
but unable to correct this problem?
View 9 Replies
View Related
Aug 6, 2007
As part of configuring my SQL Server 2005 box to be able to use the xp_cmdshell function, I ran sp_xp_cmdshell_proxy_account to set up the credentials:
sp_xp_cmdshell_proxy_account 'DomainDomainUser', 'password'
Unfortunately, it just returns:
Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '5'.
I am assuming that this is an "Access Denied" error message. Has anyone got any ideas on why this is happening or what I can do to narrow down what the problem is. I can't find any logs with further information.
I have used a similar setup on another SQL Server 2005 box before and have never had a problem running the sp_xp_cmdshell_proxy_account function. I can't see what I have done differently this time.
Further information:
The SQL Server and SQL Server Agent services are running under the DomainDomainUser account.
I have given the DomainDomainUser the following privileges:
Act as part of the operating system
Log on as a batch job
Log on as a service
Replace a process level token
Let me know if you need any further information.
Thanks in advance for any help.
Ben
View 14 Replies
View Related
Nov 22, 2006
I have the next code in my proyect:
If CurrentProject.IsConnected Then
MsgBox "Base ya Conectada.Procedemos a cerrarla primero", vbOKOnly
If SQLConexion.State = adStateOpen Then SQLConexion.Close
SQLConexion.Close
End If
SQLConexion.Open SQLSentencia
AbreConexionSQLSERVER = 0
If I detect that the connection is opened while I am initializazing varriables, I want to close always the connection and then begins my aplicattion always opening the connection, but it always returns a error 91..
suggestions?
View 1 Replies
View Related
Nov 1, 2005
Hi all,
I am trying to build a association table (t2) to store a list of users
have viewed an item in my records table (t1). My goal is to send the
UserID parameter to the query and return to the user a read / not read
marker from the query so I can handle the read ones differently in my
.net code. The problem is that I cannot work out how to return anything
but the read data to the client. So far my stored proc looks like this
DECLARE @UserID AS Int -- FOR TESTING
SET @UserID = 219 -- FOR TESTING
SELECT t1.strTitle, t1.MemoID, Count(t2.UserID) AS ReadCount,t2.UserID
FROM t1
LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID
WHERE t2.UserID = @UserID
GROUP BY t1.MemoID, t1.strTitle,t2.UserID
It works fine but only returns those records from t1 that are read. I
need to return the records with null values also! I may have built the
assoc table wrong and would really appreciate some pointers on what I
am doing wrong. (assoc table has rID, MemoID and UserID columns)
Please help!
Many thanks
View 2 Replies
View Related
Jan 25, 2006
I have my SQL call:
SELECT CallLog.CallID, Journal.HEATSeqFROM CallLog INNER JOIN Journal ON CallLog.CallID = Journal.CallID
There are multiple enteries in the Journal table for every entry in the CallLog table, so I receive multiple records:
CallID HEATSeq00000164 983290904 00000164 983291548 00000164 983295209 00000231 984818271 00000231 985194317 00000231 985280248
I only want to return the LAST record in the Journal table, so the output will be:
CallID HEATSeq00000164 983295209 00000231 985280248
Can this be done directly in the SQL call?
View 7 Replies
View Related
Oct 23, 2007
Hi,
I'm having a little trouble with the following code:
SELECT DISTINCT cd1.*, cd2.*
FROM Table1 cd1 LEFT JOIN Table2 cd2
ON cd1.RegNr=cd2.RegNr
WHERE cd1.RegNr = $RegNr
I want it to return the 2 rows that is present in the tables but it returns 4.
1262007-10-20 10:14:00
1262007-10-20 10:14:00
1262007-10-20 10:17:00
1262007-10-20 10:17:00
View 18 Replies
View Related
Feb 24, 2007
I have the following query:
select sq.*, p.numero, p.nombre
from paf p right outer join dbo.GetListOfSquaresForShippingLot(@lot) sq on sq.number = p.numero and sq.version = p.numero
The @lot parameter is declared at the top ( declare @lot int; set @lot = 1; ). GetListOfSquaresForShippingLot is a CLR TVF coded in C#. The TVF queries a XML field in the database and returns nodes as rows, and this is completed with information from a table.
If I run a query with the TVF only, it returns data; but if I try to join the TVF with a table, it returns empty, even when I'm expecting matches. I thought the problem was the data from the TVF was been streamed and that's why it could not be joined with the data from the table.
I tried to solve that problem by creating a T-SQL multiline TVF that is supposed to generate a temporary table. This didn't fix the problem.
What can I do? Does anybody know if I can force the TVF to render its data somewhere so the JOIN works? I was thinking a rowset function could help, but I just can't figure out how.
PLEASE HELP!!!!
Let me know if you want the code for the CLR TVF. This is the code for the T-SQL TVF:
CREATE FUNCTION [dbo].[GetTabListOfSquaresForShippingLot]
(
@ShippingLot int
)
RETURNS
@result TABLE
(
Number int, Version int, Position smallint,
SubModel smallint, Quantity smallint,
SquareId nvarchar(5),
ParentSquareId nvarchar(5),
IsSash smallint,
IsGlazingBead smallint,
Width float,
Height float,
GlassNumber smallint,
GlassWidth float,
GlassHeight float
)
AS
BEGIN
INSERT INTO @result
SELECT *
FROM dbo.GetListOfSquaresForShippingLot(@ShippingLot)
RETURN
END
View 6 Replies
View Related
Dec 3, 2013
Here is my query which returns multiple rows
SELECT
R.name, R.age,R.DOB,
ISNULL(D.Doc1,'NA') AS doc1,
ISNULL(C.Doc2,'NA') AS doc2
FROM
REQ R
inner join RES S ON R.Request_Id=S.Request_Id
inner join RES1 D ON D.Response_Id=S.Response_Id
inner join REQ1 C ON C.Request_Id=R.Request_Id
select * from RES1 where Response_Id = 111 -- return 3
select * from REQ1 where Request_Id = 222 --- returns 2
So at last inner join retuns 3*2 = 6 records , which is wrong here and i want to show 3 records in doc1 row and 2 records in doc 2 rows ...
View 5 Replies
View Related
Jul 22, 2007
Hello All.
I am struggling with the below join block in my stored procedure.
I can't seem to get the duplicate row problem to go away. It seems that SQL is treating each new instance of an email address as reason to create a new row despite the UNIONs.
I understand that if I am using UNION, using DISTINCT is redundant and will not solve the duplicate row problem.
Primary Keys: none of the email address columns are primary keys. Each table has an incrementing ID column that serves
as the primary key.
I am guessing I am encountering this problem because of how
I have structured my Join statements? Is it possible to offer advice without a deeper understanding of my data model or
do you need more information?
Thanks for any tips.
Code:
select emailAddress from Users union
select user_name from PersonalPhotos union
select email_address from EditProfile union
select email_address from SavedSearches union
select distinct email_address from UserPrecedence union
select email_address from LastLogin) drv
Left Join Users tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join PersonalPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join LastLogin tab4 on (drv.emailAddress = tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress = tab6.email_address
View 8 Replies
View Related
Jun 18, 2015
I have a query that based 2 tables. I wrote a query with a left join on the base table but the result set returns multiple rows for each occurrence in the second table because of the left join. I want but to return all records from on table A and only matching records from table B which id but I would wan tit to keep return them vertically as the because it make it difficult to read when put in a spreadsheet. It want it to return the values horizontally so the rows are not increasing for each occurrence on table b.
View 5 Replies
View Related
Nov 16, 2006
I have a query which is returning a different result set when it is run against identical tables in 2 different environments.
The query is like:
Select
F.LicenseeID, IsSpecialLicensee
from FactTable F
left join View_SpecialLicensee SL on F.LicenseeID = SL.LicenseeID
The Create Statement for the view is like
Create View [dbo].[View_SpecialLicensee]
as
Select LicenseeID, LicenseeName, IsSpecialLicensee = 1
from DimensionLicensee
where LicenseeName like '%ibm%'
or LicenseeName like '%cisco%'
or LicenseeName like '%hp%'
In my test environment, I get the query result I expected:
LicenseeID, IsSpecialLicensee
1 , 1 - (where LicenseeName = 'IBM')
2, null - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, null - (where LicenseeName = 'Microsoft')
5, null - (where LicenseeName = 'Oracle')
6, null - (where LicenseeName = 'Apple')
In my production environment, I get the following query result:
1 , 1 - (where LicenseeName = 'IBM')
2, 1 - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, 1 - (where LicenseeName = 'Microsoft')
5, 1 - (where LicenseeName = 'Oracle')
6, 1 - (where LicenseeName = 'Apple')
Ideas as to what changed gratefully received.
FYI the production environment which returned the 2nd dataset is SQL2000, I have got the result I expected in both SQL2000 and SQL2005 development environments.
View 6 Replies
View Related
Nov 29, 2015
I have a query that returns material(items) that are used in an event on a certain day.
SELECT C.categoryName, count(I.itemID) AS InMission
from items as I
RIGHT JOIN Categories AS C on I.categoryID = C.categoryID
INNER JOIN LinkMissionItem as LM on I.itemID = LM.itemID
INNER JOIN Missions as M on LM.missionID = M.MissionID
where '2015/12/19' BETWEEN M.freightLeave and M.freightReturn AND isReturned = 0
GROUP BY C.categoryName, C.categoryID
ORDER BY C.categoryID
There are a total of 20 categories and I would like all the categories listed in the result even though there are no items booked in a mission. At the moment, I can only get the categories that have items in that category booked in a mission. I hoped that the RIGHT JOIN on the categories table would do the trick but it doesn't.
View 4 Replies
View Related
May 11, 2015
java code to retrieve the data returned by SQL server stored procedure which is of CURSOR VARYING OUTPUT type and display the details on console.
View 3 Replies
View Related
Dec 11, 2007
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user))
BEGIN
DROP PROCEDURE InsertTimeCard
END
go
/*********************************************************************************************************
** PROC NAME : InsertTimeCardHoursWorked
**
** AUTHOR : Demetrius Powers
**
** TODO/ISSUES
** ------------------------------------------------------------------------------------
**
**
** MODIFICATIONS
** ------------------------------------------------------------------------------------
** Name Date Comment
** ------------------------------------------------------------------------------------
** Powers 12/11/2007 -Initial Creation
*********************************************************************************************************/
CREATE PROCEDURE InsertTimeCard
@DateCreated DateTime,
@EmployeeID int,
@DateEntered DateTime,
@SerializedXML text,
@Result int output
as
declare @NewTimeCardID int
select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards
-- proc settings
SET NOCOUNT ON
-- local variables
DECLARE @intDoc int
DECLARE @bolOpen bit
SET @bolOpen = 0
--Prepare the XML document to be loaded
EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--The document was prepared so set the boolean indicator so we know to close it if an error occurs.
SET @bolOpen = 1
--Create temp variable to store values inthe XML document
DECLARE @tempXMLTimeCardExpense TABLE
(
TimeCardExpenseID int not null identity(1,1),
TimeCardID int,
ExpenseRptID int,
ExpenseDate datetime,
ProjectID int,
ExpenseDescription nvarchar(510),
ExpenseAmount money,
ExpenseCodeID int,
AttachedRct bit,
SubmittoExpRep bit
)
DECLARE @tempXMLTimeCardWorked TABLE
(
TimeCardDetailID int not null identity(1,1),
TimeCardID int,
DateWorked DateTime,
ProjectID int,
WorkDescription nvarchar(510),
BillableHours float,
BillingRate money,
WorkCodeID int,
Location nvarchar(50)
)
-- begin trans
BEGIN TRANSACTION
insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid)
values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0)
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML
INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2)
WITH ( ExpenseRptID int 'ExpenseRptID',
ExpenseDate datetime 'ExpenseDate',
ProjectID int 'ProjectID',
ExpenseDescription nvarchar(510) 'ExpenseDescription',
ExpenseAmount money 'ExpenseAmount',
ExpenseCodeID int 'ExpenseCodeID',
AttachedRct bit 'AttachedRct',
SubmittoExpRep bit 'SubmittoExpRep')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked
-- commit transaction, and exit
COMMIT TRANSACTION
set @Result = @NewTimeCardID
RETURN 0
-- Error Handler
ErrorHandler:
-- see if transaction is open
IF @@TRANCOUNT > 0
BEGIN
-- rollback tran
ROLLBACK TRANSACTION
END
-- set failure values
SET @Result = -1
RETURN -1
go
View 1 Replies
View Related
Oct 22, 2007
---------------------------------------------------------------
My Original Post
I have to query n table(NLRImports) using the Distinct keyword, to retrieve a set of ID numbers. ( "Select DISTINCT id_nbr from NLRImport" ).
Now i want to use those values i retrieved, to process the records in the table(NLRImports) 1 by 1. How do i use those ID no's i retrieved as Variables or parameters for my next query?? If this makes sense?
----------------------------------------------------------------
First, thanks for the response.... now here is what im trying to do.
I created a simple application in delphi to import information to a table in MSSql2005. This is some of the resulting columns...
date | id_nbr | account_nbr | sub_account_nbr | ... etc
-------------------------------------------------------------
Now there will be several entries with the same id no but on different dates, so i take it dates would rather be my pkey.
Then i need to take one person's entries(i work on id_nbr) and go thru all the entries taking the earliest date and comparing all the other entries for that person to the first date and select all the dates more than 19 days after the first date and less than 91 days from first date and place it in a new table.
I used cursor s and while loops to kind of get it going but i know that cursors are not really recommended use but the performance implications dont bother with this particular job.
What other ways should i be using to accomplish this?
thanks, i hope this is clear...
View 1 Replies
View Related
Aug 29, 2007
Hey, i need some help with my query structure.. I have my zip code database, and then a listings table, with a zip code in one of the columns. I want to return all that data from both frields where the zip code radius results are. here's my stored procedure:*removed*Any ideas?
View 1 Replies
View Related
Jul 20, 2005
Hello.Newbie on SQL and suffering through this.I have two tables created as such:drop table table1;godrop table table2;gocreate table table1(name varchar(10),code1 integer,code2 integer,code3 integer,code4 integer,code5 integer);gocreate table table2(code integer,descript varchar(50));goINSERT INTO table1 VALUES ('mary',1,7,8,9,13)INSERT INTO table1 VALUES ('mary',1,7,8,9,13)INSERT INTO table1 VALUES ('mary',1,7,7,7,13)INSERT INTO table1 VALUES ('mary',1,7,8,9,13)INSERT INTO table1 VALUES ('joe',1,7,8,9,3)INSERT INTO table1 VALUES ('bob',1,7,8,9,3)INSERT INTO table1 VALUES ('larry',22,17,18,19,113)INSERT INTO table1 VALUES ('mary',1,3,2,9,13)INSERT INTO table2 VALUES (1,'code1')INSERT INTO table2 VALUES (3,'code3')INSERT INTO table2 VALUES (7,'code7')goTable1 will have duplicate name entries and code1 - code5 couldcontain any range of numeric codes, including dups in the same row orother rows.Table2 sorted and indexed unique by code contains only the codes I'minterested in reporting.I need to produce report:name code count (of names that contain at least 1 code in Table2)I'll go back to suffering through it now, but I thought I'd post sinceI'm not exactly sure where to begin.Thank you for any help or information!My above email is no longer active, so please post to the list so thateveryone can benefit and your contribution lives on.
View 4 Replies
View Related
