I have created a form with box in MS Access. Later I added a query into MS Access. Now I want to make this query dynamic so that it can run with the value available in that box.
I tried several reference in "where" cluase of query but did not work.
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
View 10 Replies View Related
Here is the query:WHERE DATEPART(month, " + tableName + ".timestamp)>='" + startTextBox.Text + "' AND DATEPART(month, " + tableName + ".timestamp)<='" + endTextBox.Text + "'This is in a program using C# which is why it's in quotes and all that good stuff. The query itself works properly when startTextBox.Text = 8 and endTextBox.Text = 9. Itreturns results for both months 8 and 9. But when I want a result from a single month, say just 9... I put 9 in both text boxes and it ends up returning no results.Logic would tell me that say that both logics should come back TRUE but for some reason it's failing. Any ideas/suggestions? Thanks in advance!
View 2 Replies View RelatedHow can I run a query that searches for a reference number that may appear in two different columns? I want to pull all records for idenification numbers that can also show up in a secondary identification column.
View 4 Replies View RelatedHi everybody.
I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1
I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).
With the following query
SELECT USER_NAME()
I see that the approle is being used.
Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:
The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.
What should I do to make it work?
The table in DB2 has the same schema of the view in DB1 which refers to it.
I put the DB1 TrustWorthy and both the database have the db_chaining option activated.
Any idea on how to solve the problem would be widely appreciated.
Thank you very much.
Vania
In the Reference_Master table I have Total count based on date for each RefCode. Can I get the total count of each RefCode by monthly wise for each year
Reference_Master
RefCode Date Count
100012/18/20074
100012/19/20078
100012/20/20074
100022/18/20073
100022/19/20072
100022/20/20076
[Code] ...
I need the result as below
Result
RefCodeMonth/YearTotalCount
10001FEB-200716
10002FEB-200711
10003Mar-20077
10004Mar-20073
10005Mar-20072
10001FEB-200816
10002FEB-200811
10003Mar-20087
10004Mar-20083
10005Mar-20082
Folks,
I have an issue in producing output for the following query in MDX Query builder avaialable in BIDS. the error message I get is:
The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)
Code Snippet
WITH MEMBER [Measures].[Annualized Vs Target] AS
'[Measures].[Revenue]-[Measures].[Target Revenue]'
SELECT
NON EMPTY {
([Measures].[Balance],[Year].[Year - Year].&[2006]) ,
([Measures].[Balance], [Year].[Year - Year].&[2007]),
([Measures].[Revenue], [Year].[Year - Year].&[2006]),
([Measures].[Target Revenue], [Year].[Year - Year].&[2007]),
([Measures].[Revenue], [Year].[Year - Year].&[2007]),
([Measures].[Annualized Vs Target], [Year].[Year - Year].&[2007])
}
ON COLUMNS ,
NON EMPTY { ([Client - Coverage Region].[Region Name].[Region Name].ALLMEMBERS *
[Client].[Client Name].[Client Name].ALLMEMBERS)} ON ROWS
FROM [MIS POC FINAL]
Do Anybody has a solution for this? I require the output to be in the following format :
Region_Name(RegDim) Client_Name(ClientDim) Balance_2006 Balance_2007 Revenue_2007 TargetRevenue_2007
USA A 20000 30000000 40000000 5000000000
B 60000 70000000 80000000 9000000000
Note: We can execute the above query in SSMS-MDX Query window without any issues.
Many Thanks in Advance for any assistance.
Subhash Subramanyam, Suraj Magdum
Hi,
I have an application that uses following code:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
namespace TimeTracking.DB
{
public class sql
{
OleDbConnection conn;
//
//the constructor for this class, set the connectionstring
//
public sql()
{
DBConnectionstring ConnectToDB = new DBConnectionstring();
conn = ConnectToDB.MyConnection();
}
//
//
//
public void UpdateEntry(int ID, string Week, string Year, string Date, string Project, string Action, string Time, string Comment)
{
int m_ID = ID;
int m_Week = (Convert.ToInt32(Week));
int m_Year = (Convert.ToInt32(Year));
string m_Date = Date;
string m_Project = Project;
int m_ProjectID = new int();
string m_Action = Action;
int m_ActionID = new int();
Single m_Time = (Convert.ToSingle(Time));
string m_Comment = Comment;
//
//get the project ID from the database and store it in m_ProjectID
//
OleDbCommand SelectProjectID = new OleDbCommand("SELECT tblProject.ProjectID FROM tblProject"
+ " WHERE (((tblProject.Project) LIKE @Project))", conn);
SelectProjectID.Parameters.AddWithValue("@Project", m_Project);
try
{
//open the connection
conn.Open();
OleDbDataReader Dataset = SelectProjectID.ExecuteReader();
while (Dataset.Read())
{
m_ProjectID = (int)Dataset["ProjectID"];
}
Dataset.Close();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
//
//get the action ID from the database and store it in m_ActionID
//
OleDbCommand SelectActionID = new OleDbCommand("SELECT tblAction.ActionID FROM tblAction"
+ " WHERE (((tblAction.Action) LIKE @Action))", conn);
SelectActionID.Parameters.AddWithValue("@Action", m_Action);
try
{
OleDbDataReader Dataset = SelectActionID.ExecuteReader();
while (Dataset.Read())
{
m_ActionID = (int)Dataset["ActionID"];
}
Dataset.Close();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
//
//
//
OleDbCommand Query = new OleDbCommand("UPDATE [tblEntry] SET [tblEntry].[Weeknumber] = @Week,"
+ " [tblEntry].[Year] = @Year, [tblEntry].[Date] = @Date, [tblEntry].[Project] = @ProjectID, [tblEntry].[Action] = @ActionID,"
+ " [tblEntry].[Hours Spent] = @Time, [tblEntry].[Comments] = @Comment WHERE (([tblEntry].[ID]) = @ID)", conn);
Query.Parameters.AddWithValue("@ID", m_ID);
Query.Parameters.AddWithValue("@Week", m_Week);
Query.Parameters.AddWithValue("@Year", m_Year);
Query.Parameters.AddWithValue("@Date", m_Date);
Query.Parameters.AddWithValue("@ProjectID", m_ProjectID);
Query.Parameters.AddWithValue("@ActionID", m_ActionID);
Query.Parameters.AddWithValue("@Time", m_Time);
Query.Parameters.AddWithValue("@Comment", m_Comment);
try
{
Query.ExecuteNonQuery();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
finally
{
//close the connection
if (conn != null)
{
conn.Close();
}
}
}
}
}
Code Snippet
The update statement is not working in my application, no error in C# and no error in ms-access. When I paste the update query into the ms-access query tool and replace the parameter values (@....) with real values, is will update the record.
What am I overseeing here?
--Pascal
Is there a way to specify a MS Access table (or query object) in the select query of SQL Server.
Ex.:
MSAccessTable (in file.mdb)
col1
col2
a1
a2
b1
b2
SQL query in SQL Server:
SELECT col1, col2 into SqlTable from [file.mdb].MSAccessTable;
Thanks,
Hi,
I am trying to develop a custom algorithm. I have implemented and tested training methods, however I fail at prediction phase. When I try to run a prediction query against a model created with my algorithm I get:
Executing the query ...
Obtained object of type: Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
COM error: COM error: DMPluginWrapper; Object reference not set to an instance of an object..
Execution complete
I know this is not very descriptive, but I have seen that algorith doesn't even executes my Predict(..) function (I can test this by logging to a text file)
So the problem is this, when I run prediction query DMPluginWrapper gives exception -I think- even before calling my custom method.
As I said it is not a very descriptive message but I hope I have hit a general issue.
Thanks...
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
View 2 Replies View RelatedI have the following query created in MS Access that I would like to convert to MS SQL Server, but I can't get the IF logic to work properly, any help you can provide would be greatly appreciated.
Code:
SELECT Customer.Last_Name, Customer.First_Name, Customer.Middle_Name, Customer.Address, Customer.City, Customer.Region, Customer.Postal_Code, Customer.Country, Orders.Order_ID, Customer.Customer_ID, Employees.LastName, Employees.FirstName, Order_Line.Item_ID, Item.[Long Description], Order_Line.Units_Purchased, Order_Line.Price, Order_Line.Discount, CCur(Order_Line.Price*[Units_Purchased]*(1-[Discount])/100)*100 AS ExtendedPrice, Store.Store_Address, Store.Store_City, Store.Store_State, Store.Store_Zip_code, Store.Store_Phone_Number, Store.Store_Fax_Number, Item.Taxable_Nontaxable,
IIf(Item.Taxable_Nontaxable=Yes,([ExtendedPrice]*Tax_Table.Tax_Rate),([ExtendedPrice]*0)) AS Tax_Amt,
Tax_Table.Tax_Rate
I'm having trouble converting this access query into a sql 2000 query.
Your help would be appreciated:
IIf(nz([PTicketNum],"M999Z")="0","M999Z",IIf(Trim(nz([PTicketNum],"M999Z"))="","M999Z",nz([PTicketNum],"M999Z")))
Here is what I have, but I'm not confident it is correct:
CASE WHEN (PTicketNum = '0' OR PTicketNum IS NULL) THEN 'M999Z' else PTicketNum END AS Ticket
I have three tables, Equipment, LookUpGroups, and EquipmentGroups.
Equipment
----------
TagNumber
LocationCode
LookUpGroups
-------------
GroupTagNumber
TagNumber
EquipmentGroups
----------------
GroupTagNumber
I want my query to return the GroupTagNumber ONLY IF all of the TagNumbers in that group have a given LocationCode (Given by User).
Example:
GroupTagNumber: G1
TagNumber: A1
LocationCode: 1
GroupTagNumber: G1
TagNumber: A2
LocationCode: 2
GroupTagNumber: G2
TagNumber: A3
LocationCode: 1
So if the user enters LocationCode 1, then he should only get G2 back.
I've started by trying to do a count query:
Code:
SELECT tblEquipmentGroups.GroupTagNumber, tblEquipment.LocationCode
FROM tblEquipmentGroups, tblEquipment, tblLookUpGroups
WHERE tblEquipmentGroups.GroupTagNumber = tblLookUpGroups.GroupTagNumber
AND tblEquipment.TagNumber = tblLookUpGroups.TagNumber
GROUP BY tblEquipmentGroups.GroupTagNumber, tblEquipment.LocationCode
But really I'm completely stuck. Any help/suggestions would be appreciated!
Thanks,
BB
I have a database front end using access, back end uses sql server. In front end, i have a query, how can i run this query in sql server.
Another querstion, the cliend need the data, but that is too big, it is about 91700 records, How can i retrieve it and send to them. I try to use access, but when i save it as .xls file, it told me there is too many record to save it. Anybody has any idea about the data? Thanks in advance.
I've been using access to generate reports, queries... from progress db and now I need to switch to SQL. I realy need some help with converting my access statements into SQL. Here is statement I need help with:
LotStatus: IIf([pub_jobPhase].[warraid]="reg05" And [pub_jobphase].[Model]<>"_LotSale","Builder Available",IIf(Not IsNull([pub_jobsales].[finalsaledate] Or ([pub_jobphase].[Model]="_LotSale")),"Closed",IIf([description]=" ","Available",IIf(([sales status]="spec" Or [sales status]="model" Or [sales status]="ssbto" Or [sales status]="ssip" Or (Not IsNull([description]) And IsNull([pub_jobsales].[finalsaledate]))),"Allocated"))))
I know SQL doesn't have IF but CASE but I am stuck on multiple condition in one CASE and expecialy with if ... or... or (...and ...) type of statement!
Hi,
i need help on one of my issues.
i am working in MS Access.
i created first query as :
QueryABC:=
SELECT DISTINCT [BOT Proforma].[Jan_Net Sales] AS restated
FROM [BOT Proforma], GBMPPL
WHERE (([BOT Proforma].[Product Name]='Reflective') And ([BOT Proforma].[Country]='China') And
([BOT Proforma].Year=2006));
i have second query as :
SELECT DISTINCT [BOT Proforma].[Jan_Net Sales], [BOT Proforma].[Jan_Fact Cost],"i want to include the output of QueryABC in here"
From [BOT Proforma],[GBMPPL]
WHERE (([BOT Proforma].[Product Name]='Reflective') And (GBMPPL.[Sold By Country]='China') And (GBMPPL.[Specified by Country]='Canada') And ([BOT Proforma].Country='Canada') And ([BOT Proforma].Year=2006));
Kindly help.
Thanks & Regards,
Regards,
Sunaina Koul
I have a table that has numerical ID.I want a query that will get the 20 highest IDs. So in other words thequery would return the last 20 results entered.What would even be better is if they were distinct entries by anothercolumn, but it still returned 20 results.Can you specify in SQL how many results you want?ThanksDave
View 2 Replies View RelatedI have the MS access query below which uses an IIF statement to put a valuein either the Owned Pallets or Rented Pallets field, how do I do this in SQL?? ...SELECT KC_COPA1.[Posting date], KC_COPA1.SKUCode, KC_COPA1.HierLevel3Code,KC_COPA1.HierLevel5Code, KC_COPA1.LineNo, KC_COPA1.EndMarket,KC_COPA1.Dispenser, format(Sum(KC_COPA1.HA),"0.0000000000") AS Handling,format(Sum(KC_COPA1.ST),"0.0000000000") AS Storage,format(Sum(KC_COPA1.PF),"0.0000000000") AS [Primary Freight],format(Sum(KC_COPA1.IR),"0.0000000000") AS [InterRegion Freight],Format(Sum((IIf(KC_COPA1!MILLPAL="O",[MillPA],0)+IIf(KC_COPA1!RDC1PAL="O",[RDC1PA],0)+IIf(KC_COPA1!RDC2PAL="O",[RDC2PA],0))),"0.0000000000") AS [PalletsOwned], Format(Sum(KC_COPA1.SACost),"0.0000000000") AS SA,Format(Sum((IIf(KC_COPA1!MILLPAL="R",[MillPA],0)+IIf(KC_COPA1!RDC1PAL="R",[RDC1PA],0)+IIf(KC_COPA1!RDC2PAL="R",[RDC2PA],0))),"0.0000000000") AS [PalletsRented], Format(Sum(KC_COPA1.ODCost),"0.0000000000") AS Other,Format(Sum(KC_COPA1.DA),"0.0000000000") AS Admin,Format(Sum(KC_COPA1.CDPCost),"0.0000000000") AS SumOfCDPCost INTO [R3Results_Rev]FROM KC_COPA1GROUP BY KC_COPA1.[Posting date], KC_COPA1.SKUCode, KC_COPA1.HierLevel3Code,KC_COPA1.HierLevel5Code, KC_COPA1.LineNo, KC_COPA1.EndMarket,KC_COPA1.Dispenser;Thanks, John
View 1 Replies View RelatedHi what is the MSSQL eqivalent for this MSAccess Query
1 UPDATE tbl1, tbl2 SET
2 tbl1.ADJUSTED = tbl2.Code,
3 tbl1.CODE = tbl2.Code,
4 tbl1.OTHER_CODE = tbl2.Other_Code,
5 tbl1.STATUS = 'Check', tbl1.ORGK = Null,
6 tbl1.SOURCE = 'Manual'
7 WHERE (((tbl1.STATUS)='Invalid') AND
8 ((tbl2.Override)=0) AND
9 ((tbl1.Path) Like tbl2.webadmin_path))
I have this query:
SELECT Trim(Left([Display Name],InStr([Display Name],",")-1))+Trim(Mid([Display Name],InStr([Display Name],",")+2,1)) AS UserName, dbo_Employee.[Display Name]
FROM dbo_Employee;
Works good in access, but when trying to put it un sql server, it comes up with errors.
I know mid is substring, I just don't know what trim and instr are in mssql.
Any ideas?
I have this query:
SELECT Tabelle1.Feld1 AS a1, [Feld2]+1 AS a2, [a1]+[a2] AS Ausdr1
FROM Tabelle1;
It runs perfectly on a Access database but not on a SQL 7.0 server. Can anyone give me a idea on how to convert this?
Thomas Schoch
Hi, I hope this is in the right forum!
I'm trying to do something extremely simple, but I keep getting this error:
Runtime error '3134' Syntax error in INSERT INTO statement
This is the code it complains about:
CurrentDb.Execute "Insert Into Speechfiles(FileName, SpeakerID,
SamplingRate,
DirectoryPathID,
Text,
CreationDate,
Duration,
Range)
values
(" & Chr(34) & fName & Chr(34) & ",
" & spkrID & ",
" & rate & ",
" & dirPathID & ",
" & Chr(34) & txt & Chr(34) & ",
" & Chr(34) & crdate & Chr(34) & ",
" & duration & ",
" & range & ")"
Since its so long, I've shown it as multiple lines, but in my code it is one long statement:
CurrentDb.Execute "Insert into Speechfiles(...) values (...)"
The table is called Speechfiles and the fields are as follows (types are in brackets)
FileName (text)
SpeakerID (number)
SamplingRate (number)
DirectoryPathID (number)
Text (text)
CreationDate (text) <- this isn't date format, so not the prob
Duration (number)
Range (number)
and the values I'm trying to insert are all of the same type as the fields are.... SOOOOOOOOOOOOOooooo, can anyone spot anything? I've spent way to long on this. All my other CurrentDb.Execute "Insert Into...." statements are working fine, it's just this one!
Thanks so much in advance...
hi,
I am in the process of creating an append query that sends the fields of an open form from my ms access 2000 db to a table in ms sql 2000. I've successfully linked the ms sql tables and created the query to pull data from my form but when i run the append i get a key violation error and nothing gets appended. I know which field is the primary key in ms sql i just don't know how to change my access table to suit, any ideas?
To start I've made my append query simple and it just pulls the contact name. Here's my sql statement if that helps.
CODE
INSERT INTO dbo_PatientMaster ( PatientFirstName, PatientLastName, PatientAccountNumber )
SELECT [Forms]![Contacts]![FirstName] AS Firstname, [Forms]![Contacts]![LastName] AS [Last], [Contacts]![PatientAccountNumber] AS [Patient Account Number]
FROM Contacts, dbo_PatientMaster
GROUP BY [Forms]![Contacts]![FirstName], [Forms]![Contacts]![LastName], [Contacts]![PatientAccountNumber];
In access the patient account number is empty and I just created that field in my access table today.
thanks in advance
I am trying to sum up sales for employees and get the employee with the highest sales in one query. The query I have below works but it doesnt get me the EmployeeID. Assume all the fields are in the same table. If I try to do something like this it bombs on me: "MAX(SUM(OrderAmount)"
SELECT Max(OrdersSum) AS MaxOrders FROM (SELECT SUM(OrderAmount) as OrdersSum
FROM Orders
GROUP BY EmployeeID);
Thanks in advance!
Hi,I would like some help converting an access query to a SQL Server query.The access query is made up of the following and then repeated for each field:SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...FROM dbo.applicants I have tried using the following to test out an alternative, but it brings back the incorrect figure:SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleFROM dbo.applicants I've looked at the table and should get back 350, but only get back 193.But using the following query I get the correct figure:SELECT COUNT(gender) AS FemaleFROM applicantsGROUP BY genderHAVING (gender = 'Female') Although I can't use the above query because I want to also count how many 'Male' applicants there are.How can I do this?Thanks
View 5 Replies View RelatedI am being asked to convert this access query into sql server 2000.
Access query
SELECT Left(Trim([Notes_Primary_Key]),InStr(Trim([Notes_Primary_Key])," ")) AS PcnPID, Trim([Notes_Secondary_Key]) AS PcnTicketNum
FROM tri_offnotes;
I'm new to SQL and am not familiar with which function replaces the InStr access function.
Hi everyone,I am trying to access a client's SQL query anaylzer to run a SQLscript, and I can not find it anywhere.I have not used SQL in a few years, but I would always see a menu inthe program list, or go to Enterprise Manager. I could not find anymenu listings for SQL. I went to Programs - Administrative and did notsee anything related to SQL yet I know that I am looking at their SQLserver.I don't know if they are running SQL 2000 or 2005. Can anyone give mea directory path or perhaps the exe name that launches query analyzer?Thanks!
View 3 Replies View Relatedhi friends i need help in this sql queryi have table like,id fid__ _____autonumber textand i am storing values likeid fid___________________________________1 1,2,3,4,52 11,12,13,14,15now to find values i am using querysql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')only problem in this query is it is selecting 1 and 11 and i requireonly 1 as i am giving one in %1%now from this group some one give me the answer of this queryselect *from testwhere fid = '1' -- singletonor fid like '1,%' -- beginning of lineor fid like '%,1,%' -- middle of lineor fid like '%,1' -- end of linenow this query is running perfectly in other database except msaccess2000. can anyone solve this problem. this query is not giving anyanswer. it checks all those records which are singleton but not middleof line records. and it seems to be problem in access only not inmysql. it is working perfectly in mysql but not in access and as accessis my database in application i have to use access and i am reallyirritate when i find in help that either i can use ' * ' or ' % ' inexpression on any one side like '%,1' or '%1,' but not like middle ofline that i am using '%,1,%'here is example of my problemsample table:=id fid___________________________________1 1,2,3,4,52 11,12,13,14,15query likeselect *from testwhere fid = '1' -- singletonor fid like '1,%' -- beginning of lineor fid like '%,1,%' -- middle of lineor fid like '%,1' -- end of linewill result id=1 perfectly but when i searchselect *from testwhere fid = '2' -- singletonor fid like '2,%' -- beginning of lineor fid like '%,2,%' -- middle of lineor fid like '%,2' -- end of lineit will not give me no output. plz help me i dont know what is theproblem if anyone can solve this i will be really thankful.
View 1 Replies View RelatedHiI'm trying to convert an SQL Query from Access to SQL Server. TheAccess Query goes like this:SELECT Format(EntryDate, 'ddd mm dd') AS [Day]FROM JournalEntriesThis query returns the name of the day followed by month and date (Suaug 21)What would this be like in SQL Server ? What style id do I have to useto get the correct format ?RegardsRune
View 1 Replies View RelatedI would like to call this stored procedure, but I am unable to passparameters to the @Start and @End.Is thier a way to pass parameters to a pass through query from MSAccess?SELECT COUNT(dbo.tblPersActionHistory.PersActionID) AS [Total Ct],[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.Pe rsActionID, 2)AS [Signed PP]FROM dbo.tblPersActionLog INNER JOINdbo.tblPersActionHistory ONdbo.tblPersActionLog.PersActionID =dbo.tblPersActionHistory.PersActionIDWHERE (dbo.tblPersActionLog.StatusID BETWEEN 4 AND 7) AND(dbo.tblPersActionLog.Rejected = 0) AND(dbo.tblPersActionLog.IsPayAction = 0) AND(dbo.tblPersActionHistory.ActionTypeID = 5) AND(dbo.fn_IsParACorrection(dbo.tblPersActionHistory. PersActionID) = 0)AND([dbo].fn_ParNotException(dbo.tblPersActionHistory.PersA ctionID) = 1)AND (dbo.tblPersActionHistory.ItemDTG >= @StartDate) AND(dbo.tblPersActionHistory.ItemDTG <= @EndDate)GROUP BY[dbo].fn_FindStartPayPeriod(dbo.tblPersActionHistory.Pe rsActionID, 2)
View 2 Replies View RelatedI will explain (or at least try to) first and then give an exampleafter.I need to append a number of rows from several tables into one mastertable. Unfortunately there are certain columns (which are UNIQUEcolumns in the master table) in the source tables that are repeated inthe same table or across the source tables.Example:Source 1 Source 2[SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]MSFT STOCK AAPL STOCKAA STOCK MER OPTIONMER OPTIONMSFT OPTIONMaster[SECURITY] --- Unique Key[SECURITY_TYPE]As you can see in the example, MSFT is listed twice in Source 1 -- asa stock and an option. MER is listed as an option in both Source 1 andSource 2.My first solution (when I wrote it in Access) was to read in a sourcerow, check if it existed in the master table, and then add it if itdid not. This of course ran very slow. To fix that I added all thesource rows to a temp table and then appended that temp table to themaster. Since I was able to turn off warnings with the SetWarningsaction in access any row that caused a duplicate key error got ignoredBUT the query continued to the end.(Note: DISTINCT on a source table does not work since MSFT-Stock is arow while MSFT-Option is another.)I rewrote the query to run in SQL Query Analyzer but cannot seem tofigure out how to turn off errors so the query runs to completion. Itmay not even be possible to do so in which case I am hoping for asolution other than checking each row to see if it exists before I addit.
View 3 Replies View Related