Posts by cgyrob

You should be able to clean it up during import with a SSIS package if you know how or else you can do a batch load into a temp table and then clean it up when inserting into maintb.

Since you have a reference table you can use s select statement to insert the correct values but this would be a two step process. First load file then run the insert script.

I would invest some time in learning how to create SSIS packages if you are going to be doing alot of data importing or manipulation.

This insert statement should work if you just want to load and then run the script afterwards.

Insert into maintb (id ,name, type)
Select a.id, a.name, b.id 
From temp a
Inner join  referencetb b on a.type = b.type

You can also use a case statement in the insert but you have would to manually enter every type and change the script if the types ever change.

@yangski

The user said he was using express which does not include SSIS which is used for the import wizard. The link Sknake provided has a blog on the subject and some possible solutions.

You should open a seperate topic because someone might have a different answer and they will not see the question plus it is frowned upon.

I would use a DTS/SSIS package for bulk inserts. They can be easily created for bulk inserts and scheduled to run whenever you want. You can also do transform operations within the package to cleanse the data being inserted.

The first question I have is why are you using a cursor just to fill a temp table when you can simply fill the temp table with your select statement.

Select emplid, name, division, payroll_sect
Into #tableMain
FROM tblRecords a
WHERE ((Finalized = 0) or
(Finalized = 1 and date_time = currentPayrollDate ))
ORDER BY Division, payroll_sect, employee_name

If you must use a cursor you should look up the syntax again. You have to declare variables and fill the variables in your fetch statements.

--keep in mind #tblMain has already been created...
--declare cursor variables
DECLARE @emplid int, @employee_name varchar(50), @division varchar(10), @payroll_sect varchar(10)

--declaring my looper
DECLARE ACREmpsCursor CURSOR SCROLL FOR

--only select the fields you require
SELECT DISTINCT
    emplid,
    employee_name,
    division,
    payroll_sect
  FROM tblRecords a
 WHERE ((Finalized = 0) or
(Finalized = 1 and date_time = currentPayrollDate ))
 ORDER BY Division, payroll_sect, employee_name 
-- do you really need to order the records, waste of resources if not required

-- I need to store above results into #tblMain, not sure how this 
-- part works...
-- does ACREmpsCursor hold the result set from the above query?
OPEN ACREmpsCursor
FETCH NEXT FROM ACREmpsCursor
Into @emplid, @employee_name, @division, @payroll_sect
--make sure variables are in the same order as select statement
-- while there is a record, do the stmts between begin and end..?
WHILE @@FETCH_STATUS = 0
BEGIN

	INSERT INTO #tblMain(emplid, name, division, payroll_sect)
	VALUES(@emplid, @employee_name, @division, @payroll_sect)

--fill cursor with next set of values
FETCH NEXT FROM ACREmpsCursor …

This should work for you.

update employee set salary = case 
          when salary <    100000 then  salary * 1.10
          when salary >=  100000 then  salary * 1.15
End;

Glad I could help. Can you mark the thread solved if your problem has been fixed.

Thanks.

The first thing you need to do is replace the left join with an Inner join. Right now you are joining all the customers even if they do not have any orders which is a waste of resources when you are looking for the customers that rank in the top5 of orders.

If that alone does not solve your problem you can try using a derived table to calculate the top 5 customers.

SELECT customers_firstname, customers_lastname,o.Number
FROM customers 
INNER JOIN (Select customer_id, count(*) as Number from orders Group by customer_id order by Number desc limit 5)AS o
on customers.customers_id = o.customer_id ;

Let me know if this helps.

I know this thread is a few days old but if you want the top 10 from the combined list you just sort the list after the unions not for each table.

(SELECT * from table_a )
union (SELECT * FROM table_b)  
union (SELECT * FROM table_c ) 
order by rating DESC
Limit 10

It is always faster accessing 1 table instead of multiple tables. Not quite sure what else you want in the table but try something like this would most likely be best

ID Code Code_Description ...Other fileds
1 Status desc
2 Title desc
....

Have you tried joining the subquery in the from clause instead of using the inline view.

You should use code blocks as it makes it alot easier to read.

[/code}

Did you try an inline view? something like this maybe

[code=sql]
select
distinct c.name as 'Company Name',
ss.name as 'Program'
,a.application_number as 'Application Number'
,a.sites as 'Site Count'
,convert(varchar(10), a.accreditation_start_date, 101) as 'Start of Accreditation'
,convert(varchar(10), aag.app_submission_due_date, 101) as 'App Due Date'
,aag.invoice_fee as 'Previous Accreditation Fee Paid'
,'' as 'POS Cycle'
,LTRIM((select i.firstName from individual i where i.individualid = aag.pc_id_mysql)) + ' ' +
RTRIM((select i.lastName from individual i where aag.pc_id_mysql = i.individualid)) as 'Primary Contact'
,(select i.email from individual i where i.individualid = aag.pc_id_mysql) as 'Primary Contact Email'
,(select i.title from individual i where i.individualid = aag.pc_id_mysql)as 'Primary Contact Title'
, LTRIM((select i.firstName from individual i where i.individualid = aag.am_id_mysql)) + ' ' +
RTRIM((select i.lastName from individual i where aag.am_id_mysql = i.individualid)) as 'Account Manager'
,(Select top 1 ast.application_status_description 
	from application_status ast where ast.application_status_idd = asl.status 
	order by entered_date desc) as 'Status'
,convert(varchar(10), a.accreditation_expiration, 101) as 'Expiration Date'
,(select 
convert(varchar(10),asl.user_date, 101)) as 'AC Decision Date'

from
[application] a
inner join accreditation_agreement aag on
a.client_id = aag.company_id
inner join company c on
a.company_id_mysql = c.companyid
inner join application_accreditation aa on 
a.application_id = aa.application_id 

inner join standard_set ss on 
aa.standard_set_id = ss.standard_set_id 
inner join application_status_log asl on
a.application_id = asl.application_id
inner join application_status ast on
asl.status = ast.application_status_id 

WHERE C.name not like 'URAC%'
order by 
'Company Name'

I can't test so not sure if this syntax will work. The other syntax that you can …

I think the best way is the syntax you feel more comfortable with.

I like the old joining syntax using the where clause but alot of people like the new syntax, I believe because of readability especially on outer joins where it shows in regular text that you are performing a LEFT or RIGHT join.

There is no performance difference that I am aware of but I could be wrong.

At my previous company we also resorted to using dotdefender after we came under a serious sql injection attack. The product worked very well and we were able to get it up and running very fast. It took a few more weeks to modify the product to not block some of our own queries. The product gave us the ability to maintain our business while we totally re-designed and developed an updated version with updated security.

It also comes with decent reporting so you can track what ip's are launching the attacks and the queries they are using.

They still maintain the dotdefender for the added layer of security but the product definately saved the company alot of money and possibly the business altogether.

just my 2 cents added to Danielos.

Here is a link to an article on creating an event schedule in mysql, unfortunately I don't use mysql so it is the best I can do for you.

http://dev.mysql.com/tech-resources/articles/event-feature.html

If this doesn't work possibly you can use windows task scheduler to call the job.

I don't know mysql very much but if you schedule a job it should not care if a file is open or not.

I think you want to try a query like this

SELECT Count(*) FROM Orders 
WHERE signNumber = ? 
(AND ? BETWEEN fromdate and todate 
Or ? BETWEEN fromdate AND todate)

You need to use the OR condition or else both conditions have to be met when you want it to pull records if either condition is met.

Schedule a job to run the script at whatever interval you want.

I am not quite sure what you want your final result to look like?

The join i provided would give you the following result

TOP10 | 510 | TASKNAME 1 | 6 | YEAR 2008
TOP10 | 510 | TASKNAME 1 | 6 | YEAR 2004
TOP11 | 511 | TASKNAME 2 | 7 | YEAR 2009
TOP11 | 511 | TASKNAME 2 | 7 | YEAR 2005

If you can give me an example of what you want the final result to be I can help you find the solution.

Rob

CONVERT(CHAR(10),GETDATE(),110) Will give you the format 02-05-2003 CONVERT(CHAR(10),GETDATE(),10) Will give you 02-05-03

The MSDN library will give you all the conversions. You can also create your own format if you wish with DATEPART functions.

Just insert the getdate().

If you want specific date formats look up the CONVERT function.

Insert into Table (date)
Values(getdate())

@anubina why are you re-treading old threads.

You already provided the inline view solution for this thread so why are you re-iterating your own solution?

Just use a join query. Sub query to just pull fields is not required.

Select a.Seq, a.An, a.NET_Id, a.DES, b. Short
From Activities a, Codeset b
Where a.R11 = b.code

Im sure you are just missing something in the syntax,
unfortunately I have never used this object and since it is being deprecated by Micorsoft in future releases I doubt I ever will.

I don't know too much about Rule Objects but did you run

sp_bindrule to bind the rule to the your coluum.

Mamtha,

If this solved your question can you please mark the thread solved.

thanks.

Yes intersects work well and are much more readable.

Try something like this

Select customer from Table
Where purchasedate between trunc(sysdate,'MM') and LAST_DAY (TO_DATE (trunc(sysdate,'MM')))
and product = 'ABC'
INTERSECT
Select customer from table 
where purchasedate between add_months(trunc(sysdate,'MM'),-11) and trunc(sysdate,'MM')-1
and product = 'ABC'

This will only show a customer that has purchased a particular product in the current month as well as in the last 11 months.

I think we would need a little more details to what you are asking.

Do you want a list of all customers that purchased a specific product in the current month only IF they had purchased the same product in the past 11 months?

The product will be a provided parameter?

It also sounds like this information would be held in multiple tables, are you not interested in how to pull these together; just how to make this comparison?

If you need to know how to join the tables a schema description of the tables would be required.

Im glad i could atleast point you in the right direction. good luck with the rest.

Pee,

Sknake is one of the most helpful posters here. He asked simply that you provide him with a create statement and sample data so he can provide you with a thorough solution for your problem.

Everyone here has their own work to do so it is the requester's responsibility to do the grunt work if you need help.

Natural Joins can be used in Oracle, but I have never used them. They are like using the 'on' or 'using' construct for inner joins but the construct will distinguish which fields are the same and join them so you do not need to explicitly name them.

Your DB has to be designed so that the joining fields have the same name, which is probably why I never use them.

Besides I like controlling what is being joined myself, just call me a control freak.

Try this

SELECT MAKE.MAKE, MAKE.count, color.Color, color.count 
FROM(
       SELECT MAKE, count(*)count
       FROM cars 
       Group by MAKE
    )MAKE,
   (   SELECT MAKE, color, count(*)count
       From cars
       Group by MAKE,color
   )color
Where make.make = color.make

Nevermind, I take it the attachment is the table schema.

Is it important to do it all in one query. What are you using the resultset for because if you do it in one query you will most likely get back something like the following.

BMW 2 RED 1
BMW 2 BLACK 1
AUDI 8 RED 3
AUDI 8 BLACK 2
AUDI 8 BLUE 3
etc....

I actually remember reading both of these threads and had commented on one of them but I still did not correlate them.

@sknake - thanks, I didn't realize there were multiple threads relating to this db.

I don't see where you are getting the alias 'c'

Besides that i think you are making it more difficult then it has to be.

select *
from dbo.tblLevelOneApprover a, dbo.tblLevelTwoApproverToLevelOneApprover b
Where convert(int, b.level_two_emplid) <> convert(int, a.emplid)

Since you didn't give too much information I thought of a few other interpetations of what you asked.

If field1 is a numeric value you might want the sum of the field grouped by field2

Select field2, sum(field1)
From Table
Group by field2

or you might want to know how many records of field1 there are that are also rouped by field2

Select field2, field1, count(*)
From table
Group by field2, field1

I'm sure one of the 3 syntax's will work for what you are asking.

do you mean something like this

Select field2,count(Field1)
From Table
Group by field2

@Ramy, you are right.

Not sure why I was thinking it was in Visual Studios. Its been awhile since I have used it and everything Microsoft just looks and feels the same I guess.

BIDS is part of Visual Studio not SQL Server.

When I did my final project instead of trying to think of an aimless program that hundreds of other students have already done and will just be scrapped the second it was graded.

I found a small charitable organization in my city and offered my services. Small organizations always need something but do not have the resources or know who to ask to get them done.

This will not only give you a project with more real world experience but you can benefit your community in a small way.

Why don't you try on this forum.

http://www.accessforums.net/forms/

It is devoted to MS access so it would be a good place to take a look or most likely get a more informed answer then I can provide. It doesn't look like anyone else here has anything to add so i would take a look there for more help for this question.

Not sure what I can tell you. i have never used the Macros in Access and have no clue what the syntax error is. There is most likely some Microsoft syntax that you need but I don't know where to find except to troll some Access specific forums and tutorial. Did you look through the Access help files, sometimes they aren't over cryptic.

When looking through a few pages I saw reference where someone used the syntax

Alter Tablename Add fieldname datatype(length)

Have you tried this syntax, not sure if it works.

I found this tutorial, not sure how good it is: http://fisher.osu.edu/~muhanna_1/837/MSAccess/tutorials/macro.pdf

or

MSDN Access Library: http://msdn.microsoft.com/en-us/library/bb979619.aspx

The basic syntax for the alter command is

ALTER TABLE Employees ADD COLUMN Emp_Email TEXT(25);

I think you should be able to run something more like

alter table 'Forms![Form1]![tname].text' add Column 'forms![form1]![fname].text' 'forms![form1]![ftype].text'(field length required) not null

I am not sure how your macro handles text fields. You might have to build the string command and then execute it.

Like I said earlier I am not an Access forms guru but I would guess that you would have to access the text boxes attributes from the macro. Something like 'textbox1.text', but again unless someone else who knows more about access form development chimes in I would suggest either reference book, tutorial, or help files to find the process. This process should not be too hard to find even a google search should give you some good examples on how to manipulate text fields in your forms.

The few times I have actually done anything in Access there was quite alot of documentation. Tons of people at microsoft are paid just to write docs so i am sure you should be able to find some decent resources.

In your form you should have a submit button. On the submit action you will add the alter commands. Like I said earlier it has been ages since I have even looked at Access but you should be able to double click on the button on the form which should bring you to the button click function. This is where you would put the commands.

If I were you, go through an Access tutorial or use the Access help manuals they should be able to walk you though the steps. Unfortunately I doubt they have a wizard for what you are looking for.

If you really want an 'AND' use an 'AND'

Select Product From Table
Where Component = 'part1'
and Component ='part3'

Select Product From Table
Where Component in ('part1','part3')

Then I am missing something about your schema. What exactly is TableD.Pointer? How many rows would you expect to retrieve from the table with each pointer?

Also what is the difference between TableD.Id and TableD.pointer?