SQL syntax for query

[hencyt]

Here is the situation-our app is not completing a process reliably. We have shipment and task tables, headers and lines in separate tables.

When shipment is in the header and lines table and the status of the header is 2- then I need to compare the lines of that shipment in lines_table with lines of that same shipment in task_table where the tsk_type = 13.

Below is what I have so far. I know it is wrong- I thought showing it might help explain where I am trying to get. I am not a programmer.

select * from d.task_table where tsk_ord_sur in (select order_surrogate from d.order_table where order_lines <> (select count(*)from d.task_table where (tsk_tp = '13')))


But this does not get me only orders that are in status 2- and it compares the # of lines =13 in the whole table- not just the lines for that order.

Let me know if you need more info. I appreciate your time.

Thanks in advance,
Tracie

 

[hneal98]

Is the tsk_ord_sur table the HEADER table? If so, you need to add an 'AND status = 2', without quotes, after the ')))' to get only those that have a status of 2.

Also, we need to know what fields relate the tables, in other what ID's can you use in each table to link the tables together.

 

[hencyt]

The task table is a lines table- the header table is the shipment_table and another lines table is the shipment_lines_table. There is a task created for each line in the shipment- thus each line in the shipment_lines_table should have a corresponding pick task (tsk_type 13), and each header record in he shipment_table should have X many corresponding shipment lines in the shipment_lines_table. )Shipment_lines_table = items and qtys ordered). I need to know where the shipment_lines_table and the task table differ per shipment. IE- when there isn't a pick task created for each line in the shipment, when the shipment is status = 2.

A field to link on would be shipment_number it is found in each table.

Hope that helps.

THANKS!
Tracie

 

[hneal98]

What is the output you are expecting? Are you expecting a line count or do you want to see the actual records that differ, etc.?

 

[hencyt]

I would like to see the actual records so they can be fixed.