Is there a more efficient and better way?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Is there a more efficient and better way??

Author

Topic

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2010-04-15 : 08:15:06

I am currently running a query on two tables. Table 1 which is called prog_data has an integer column called opcode. Table 2 which is called mach_spec has 5 integer columns called allow1 thru allow5, this number could grow in the future but right now 5 is more than sufficient with our given production equipment. SO here is my query:

select t1.machine, t1.jobtask from prog_data as t1 inner join mach_spec as t2 where (t1.opcode != t2.allow1 or t1.opcode != t2.allow2 or t1.opcode != allow3) and t1.jobtask = t2.jobtask

Basicly what I am trying to do is first of all make sure I am pulling and comparing the jobtask to ensure that they match. Then in the prog_data table it will have a designated opcode. In the mach_spec there will be listed upto 5 opcodes that that machine can perform. If the opcode from prog_data matches any of the opcodes in the mach_spec then I want the query to do nothing. However if there is no match found then report it. Hope this is clear. Thanks for looking and for any and all feedback and assistance.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 08:32:19

Try this:


select
	pd.machine
	, pd.jobtask
from
	prog_data as pd
WHERE
	NOT EXISTS (
		SELECT 1
		FROM
			mach_spec ms
		WHERE
			ms.[jobTask] = pd.[jobTask]
			AND (
				ms.[allow1] = pd.[opcode]
				OR ms.[allow2] = pd.[opcode]
				OR ms.[allow3] = pd.[opcode]
				OR ms.[allow4] = pd.[opcode]
				OR ms.[allow5] = pd.[opcode]
			)
	)

What is the reason for having 5 different columns of opcodes?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2010-04-15 : 09:16:36

The reason why we have to use so many opcodes is the type of business we are in. We have approx 63 different opcodes and about 23 different types of machines.

So lets take a look at it and I will show you. Take for instance machine serial number 1001 this is a standard master mailer inserter it has no validating camera system on it and no print capability and can handle upto a 9 by 12 form. Then take machine serial number 1048 it is a pinnacle it has a camera system on it but no print capability and it can only handle upto 6 by 9 size form. Then you have serial number 1055 which is a flowmaster it has no camera system, can do printing and can handle upto 9 by 12 if the correct parts are installed. And then you have 1085 which again is a flowmaster but this one has it all, it has cameras for matching, it has print capability, and it can handle upto 9 by 12 forms.

Now you take the opcodes, the opcode contains the information on what the job requires. Example code 31 is a 6 by 9 form, so that can be ran on any of the machines listed above. Where a code 35 mandates that it need to have match capability and a code 43 says it need to have match and print capability.

So each machine serial number will be listed with what opcodes can be ran on that machine, hence the several columns to place what can be ran there.

Hope that answers your question about why we need so many columns and as the mailing industry grows and different functions and features are added that is why I mentioned that the number of columns may grow as well. Currently 5 is sufficient and gives us some room to grow but who knows the future.

quote:
Originally posted by Transact Charlie

Try this:

select
pd.machine
, pd.jobtask
from
prog_data as pd
WHERE
NOT EXISTS (
SELECT 1
FROM
mach_spec ms
WHERE
ms.[jobTask] = pd.[jobTask]
AND (
ms.[allow1] = pd.[opcode]
OR ms.[allow2] = pd.[opcode]
OR ms.[allow3] = pd.[opcode]
OR ms.[allow4] = pd.[opcode]
OR ms.[allow5] = pd.[opcode]
)
)

What is the reason for having 5 different columns of opcodes?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 09:22:27

I would have thought a design like


TABLE machine
    <mach details>

TABLE opcodes
    <opcode details>

TABLE <machineOpcodes>
    <machineLink>
    <opcodeLink>

would be better as there are no limits then on the amount of opcodes per machine and it makes querying a lot easier.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources