November 22, 2011 at 1:52 pm
In the 1st SQL Statement I am extracting part of a string, line 4, and get the result I need, as SpecID.
- The string's format is Z123456-1-- and I am extracting the numeric portion between the Z and first hyphen. (123456 represents a numeric value).
This Works OK, should I say - a column SpecID is displayed and the values are as expected:
select ORDER_NO, FORM_NO as Form, PASS_NO as Pass, ITEM_NO as ItemID, ord_mach_ops.MACH_NO as MachOp, DISPLAY_DESCR as Operation,
LAST_WORKED, PART_COMP_FLG as Status,
ISNULL(act_qty_gross,0) - ISNULL(act_qty_waste,0) as GoodQty,
CAST(ISNULL(SUBSTRING(Item_no,2,charindex('-',item_no,1)-2),'') As varchar(6)) as SpecID
from ORD_MACH_OPS
left join MACHINES on MACHINES.MACH_NO = ORD_MACH_OPS.MACH_NO
where ORDER_NO = 69852 and SCHEDCARDS_FLG = 'Y'
order by MACH_SEQ_NO
I need that value as part of the join with this subquery statement:
select job_number, form_no, ORDER_NO, CAST(SPEC_NO AS VARCHAR(6)) AS SpecID from orders As SpecIDList
When I attempt to include the subquery into the 1st query the SpecID in ORD_MACH_OPS is not recognized. That is, when I attempt to type in SpecID after ORD_MACH_OPS on line 10, SpecID is not displayed in the popup of valid items. When I execute the statement the error is that 'specid' is not a valid column.
select ORDER_NO, FORM_NO as Form, PASS_NO as Pass, ITEM_NO as ItemID, ord_mach_ops.MACH_NO as MachOp, DISPLAY_DESCR as Operation,
LAST_WORKED, PART_COMP_FLG as Status,
ISNULL(act_qty_gross,0) - ISNULL(act_qty_waste,0) as GoodQty,
isnull(SUBSTRING(Item_no,2,charindex('-',item_no,1)-2),'') as SpecID
from ORD_MACH_OPS
left join MACHINES on MACHINES.MACH_NO = ORD_MACH_OPS.MACH_NO
left join ( select job_number, form_no, ORDER_NO, CAST(SPEC_NO AS VARCHAR(6)) AS SpecID from orders ) As SpecIDList
on SpecIDList.JOB_NUMBER = ORD_MACH_OPS.ORDER_NO and SpecIDList.FORM_NO = ORD_MACH_OPS.FORM_NO
and SpecIDList.SpecID = ORD_MACH_OPS.specid <-- This is where things 'fail'
where ORDER_NO = 69852 and SCHEDCARDS_FLG = 'Y'
order by MACH_SEQ_NO
What don't I understand?
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
November 22, 2011 at 2:03 pm
As far as I know, you can't use the derived column name. You have to retype the full substring statement.
November 22, 2011 at 2:52 pm
Richard - Thanks!
What you know is correct.
And, I learned something else, because simply substituting for the column name didn't work. I also had to remove the ORD_MACH_OPS (table) reference - I have never done this before and it looks wierd because I am so used to having the table name precede the column name.
Here is the new SQL Statement (he additional columns that are needed have been added to the column list section):
select ORD_MACH_OPS.ORDER_NO, ORD_MACH_OPS.FORM_NO as Form, PASS_NO as Pass, ITEM_NO as ItemID,
isnull(specidlist.orderID,'') as OrderID,
ord_mach_ops.MACH_NO as MachOp, DISPLAY_DESCR as Operation, LAST_WORKED, PART_COMP_FLG as Status,
ISNULL(act_qty_gross,0) - ISNULL(act_qty_waste,0) as GoodQty,
cast(isnull(SUBSTRING(Item_no,2,charindex('-',item_no,1)-2),'') as varchar(6)) as SpecID
from ORD_MACH_OPS
left join MACHINES on MACHINES.MACH_NO = ORD_MACH_OPS.MACH_NO
left join ( select job_number, form_no, cast(ORDER_NO as varchar(6)) as OrderID, CAST(SPEC_NO AS VARCHAR(6)) AS SpecID from orders
where JOB_NUMBER = 69852) As SpecIDList
on SpecIDList.JOB_NUMBER = ORD_MACH_OPS.ORDER_NO and SpecIDList.FORM_NO = ORD_MACH_OPS.FORM_NO
and SpecIDList.SpecID = cast(isnull(SUBSTRING(Item_no,2,charindex('-',item_no,1)-2),'') as varchar(6))
where ORD_MACH_OPS.ORDER_NO = 69852 and SCHEDCARDS_FLG = 'Y'
order by MACH_SEQ_NO
Line 2 - has the added column from the subquery
Line 11 (3rd from the bottom) has the correct join clause
Thanks again! I really appreciate your help. This is a nice way to end my day - I learned something new!
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
November 22, 2011 at 2:56 pm
You're most welcome. Glad to help.
November 22, 2011 at 3:30 pm
EdA ROC (11/22/2011)
Richard - Thanks!What you know is correct.
And, I learned something else, because simply substituting for the column name didn't work. I also had to remove the ORD_MACH_OPS (table) reference - I have never done this before and it looks wierd because I am so used to having the table name precede the column name.
Here is the new SQL Statement (he additional columns that are needed have been added to the column list section):
select ORD_MACH_OPS.ORDER_NO, ORD_MACH_OPS.FORM_NO as Form, PASS_NO as Pass, ITEM_NO as ItemID,
isnull(specidlist.orderID,'') as OrderID,
ord_mach_ops.MACH_NO as MachOp, DISPLAY_DESCR as Operation, LAST_WORKED, PART_COMP_FLG as Status,
ISNULL(act_qty_gross,0) - ISNULL(act_qty_waste,0) as GoodQty,
cast(isnull(SUBSTRING(Item_no,2,charindex('-',item_no,1)-2),'') as varchar(6)) as SpecID
from ORD_MACH_OPS
left join MACHINES on MACHINES.MACH_NO = ORD_MACH_OPS.MACH_NO
left join ( select job_number, form_no, cast(ORDER_NO as varchar(6)) as OrderID, CAST(SPEC_NO AS VARCHAR(6)) AS SpecID from orders
where JOB_NUMBER = 69852) As SpecIDList
on SpecIDList.JOB_NUMBER = ORD_MACH_OPS.ORDER_NO and SpecIDList.FORM_NO = ORD_MACH_OPS.FORM_NO
and SpecIDList.SpecID = cast(isnull(SUBSTRING(Item_no,2,charindex('-',item_no,1)-2),'') as varchar(6))
where ORD_MACH_OPS.ORDER_NO = 69852 and SCHEDCARDS_FLG = 'Y'
order by MACH_SEQ_NO
Line 2 - has the added column from the subquery
Line 11 (3rd from the bottom) has the correct join clause
Thanks again! I really appreciate your help. This is a nice way to end my day - I learned something new!
That kind of function call in a join or where clause can lead to really horrible performance. You have killed the ability of sql to use indexes and it will always do a full table scan. If your tables are relatively small this may be ok. But if you are getting into anything resembling large datasets the performance will be dreadful. Point your favorite search engine to "sargable" or "sargability". I am about to bail out for the US holiday but maybe somebody else will pop in here with some good ideas to help you if you need it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 23, 2011 at 6:31 am
Sean - Thanks for your input. I appreciate it - especially since I'm still wet behind the ears and need to be aware of things to consider and be aware of.
Execution seemed to be immediate, but, I ran the sQL statement using my StopWatch:
-- StopWatch Execution
DECLARE @StartTime datetime,@EndTime datetime
SELECT @StartTime=GETDATE()
-- Insert Execution Commands Here
-- I inserted the command here
SELECT @EndTime=GETDATE()
SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in microseconds]
It only took 3 microseconds to execute. So, this time, using the functions is not an issue. However, your comment is appreciated, so that I learn to be aware of these things. I do need to read about sargable and associated optimization techniques.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply