Poll table 1 with table 2 in hotstandby

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

Poll table 1 with table 2 in hotstandby

Author

Topic

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2009-04-06 : 15:55:29

Sorry I was not sure what else to write in the subject column that would describe what I am attempting to now accomplish. Here is what I have written so far for my query:

SELECT machine, i.jobtask, coalesce(a.type,'') as type,coalesce(a.mfg,'') as model,coalesce(b.wotopdesc,'') as client, cpm, accum, t_stamp, prodtime as totaltime,
shift, i.serial,runtime,idle,proddate, d.prod_std, b.wotopcode FROM def_employee_performance i
left join machine_desc a on a.serial=i.serial left join djt_mt_info_to_prodtrack b on b.jobtask = i.jobtask
left join production_std_by_customer d on d.op_code = b.wotopcode and d.code = a.code and d.num_up = b.NumberUP and d.cust_num = b.cuskey

Now this works but the bold area is where I need help. Notice in the select statement the d.prod_std, this is the production standard from the bold highlighted table. Occasionally we get new orders that dont fall within this highlighted table, in which case we have a generic table that we want the data to be queried from. So if it looks thru the bold highlighted table and does not find data there then it will go to:

Table name: prod_std_by_opcode
the columns that it will use are similar: d.op_code = b.wotopcode and d.code = a.code and d.num_up = b.NumberUP

My only thing is I am not sure how to integrate it. At first I thought that if I modified my code like the following it would work:

SELECT machine, i.jobtask, coalesce(a.type,'') as type,coalesce(a.mfg,'') as model,coalesce(b.wotopdesc,'') as client, cpm, accum, t_stamp, prodtime as totaltime,
shift, i.serial,runtime,idle,proddate, isnull(d.prod_std,f.prod_std) , b.wotopcode FROM def_employee_performance i
left join machine_desc a on a.serial=i.serial left join djt_mt_info_to_prodtrack b on b.jobtask = i.jobtask
left join production_std_by_customer d on d.op_code = b.wotopcode and d.code = a.code and d.num_up = b.NumberUP and d.cust_num = b.cuskey left join prod_std_by_opcode f on f.code=a.code and f.num_up=b.NumberUP

The new parts are underlined. But this did not function as I thought it should. So any help greatly appreciated. Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 10:05:18

does prod_std_by_opcode has multiple records existing for same op_code,f.code,f.num_up value combination? else the below should work


SELECT machine, i.jobtask, coalesce(a.type,'') as type,coalesce(a.mfg,'') as model,coalesce(b.wotopdesc,'') as client, cpm, accum, t_stamp, prodtime as totaltime, 
shift, i.serial,runtime,idle,proddate, isnull(d.prod_std,f.prod_std) , b.wotopcode FROM def_employee_performance i
left join machine_desc a on a.serial=i.serial left join djt_mt_info_to_prodtrack b on b.jobtask = i.jobtask
left join production_std_by_customer d on d.op_code = b.wotopcode and d.code = a.code and d.num_up = b.NumberUP and d.cust_num = b.cuskey 
left join prod_std_by_opcode f on f.op_code = b.wotopcode and f.code=a.code and f.num_up=b.NumberUP

anyways some sample data should help to make your problem clear to us. would you please post some?

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2009-04-07 : 10:21:29

I will try your code and see what happens and let you know. But to answer your question, I will do my best and hope that I make it clear.

In table standards_by_customer we have the following fields: record, op_code, cust_code, prod_std, form_length, and code. The opcode can be repeated for several records. Here is sample data.

record opcode cust_code prod_std form_length code
1 41020 117 8000 6 0
2 41020 117 7500 11 0
3 41020 119 8000 7 0
4 41020 119 5000 14 0

In the standards_by_opcode we have the following fields: record, op_code, prod_std, form_length and code. Here is sample data.

record opcode prod_std form_length code
1 41020 6000 7 0
2 41020 9000 4 0
3 41020 10000 2 0

The reason for the code field is it is used by certain other types of equipement and can not be used here, mainly because the form length equipment is so veratile and the ones that use code are not.

So when the query is ran it will first look in the customer table, if it matches opcode and customer ID it will take that production standard. However if no match is found in the customer table it will then go to the opcodes table and using opcode and formlength it will pull the production standard from that table.

Hope this clears things up.

quote:

Originally posted by visakh16

does prod_std_by_opcode has multiple records existing for same op_code,f.code,f.num_up value combination? else the below should work


SELECT machine, i.jobtask, coalesce(a.type,'') as type,coalesce(a.mfg,'') as model,coalesce(b.wotopdesc,'') as client, cpm, accum, t_stamp, prodtime as totaltime, 
shift, i.serial,runtime,idle,proddate, isnull(d.prod_std,f.prod_std) , b.wotopcode FROM def_employee_performance i
left join machine_desc a on a.serial=i.serial left join djt_mt_info_to_prodtrack b on b.jobtask = i.jobtask
left join production_std_by_customer d on d.op_code = b.wotopcode and d.code = a.code and d.num_up = b.NumberUP and d.cust_num = b.cuskey 
left join prod_std_by_opcode f on f.op_code = b.wotopcode and f.code=a.code and f.num_up=b.NumberUP

anyways some sample data should help to make your problem clear to us. would you please post some?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 10:30:12

the data posted is different from your earlier explanation as you dont seem to have num_up field in both the tables. can you post columns contents too if it exists

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2009-04-07 : 11:43:28

Sorry my mistake I was in a rush and was not watching what I was doing. You are absolutely correct that I forgot the number_up column. And as for column contents I hope you mean what is the column set to as VARCHAR, INT, etc.. If so here is that information.

record, opcode, number_up and code are all INT
prod_std is BIGINT
form_length is REAL

I think that is everything. If not I will post again. Thanks for your quick responce.

quote:
Originally posted by visakh16

the data posted is different from your earlier explanation as you dont seem to have num_up field in both the tables. can you post columns contents too if it exists

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 12:54:39

nope...what i asked was data in column

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources