February 1, 2012 at 12:24 pm
In English: Need to select for records where the value is not equal to %.
Here's where I am in developing the statement:
select ORDER_NO, SUM(qty_alloc)
from ORD_MATL_CST
where MATL_CATEGORY = 'RSCMP' and EST_UOM <> '[%]' and ORDER_NO = 66196
group by ORDER_NO
Wasn't thinking when I first wrote the statement and used '%' - realized that's a wildcard.
This still includes the records with % in the EST_UOM.
[BTW - Had problem searching the Forum for % - it didn't like it, message vague, guessing the % is a reserved character.]
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
February 1, 2012 at 12:40 pm
EdA ROC (2/1/2012)
In English: Need to select for records where the value is not equal to %.Here's where I am in developing the statement:
select ORDER_NO, SUM(qty_alloc)
from ORD_MATL_CST
where MATL_CATEGORY = 'RSCMP' and EST_UOM <> '[%]' and ORDER_NO = 66196
group by ORDER_NO
Wasn't thinking when I first wrote the statement and used '%' - realized that's a wildcard.
This still includes the records with % in the EST_UOM.
[BTW - Had problem searching the Forum for % - it didn't like it, message vague, guessing the % is a reserved character.]
If you want to find where EST_UOM just use <> '%'.
I think what you are trying to say is want to find rows where EST_UOM contains '%'? In this case you would say
where EST_UOM like '%[%]%'
That is how you would escape that sequence. Hope that helps.
_______________________________________________________________
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/
February 1, 2012 at 2:13 pm
Sean! Hi again!
No, at this time I want to exclude the % records.
The original clause was, WHERE EST_UOM <> '%'
The results included the % records. I want the results to EXCLUDE them.
That's why I figured SQL was interpreting the % as a wildcard. Hence, the next step was to tell SQL to use % literally, not treat it as a wildcard, which, if I understand it correctly is enclosing it in square brackets. I also read about the Escape keyword, but, couldn't get that to work.
If you want to know what I'm attempting to accomplish, it's the way the database is designed (3rd party design, so I can't touch it):
It's an odd design. These are records for Materials to be used in manufacturing. For the Board material they create 3 records.
The first one has the amount of Board that is necessary to generate the finished product quantity. i.e. If quantity ordered is 1,000 and 10 items can be produced from 1 sheet, then 100 sheets are needed.
The second record is the Board that is lost during the Set Up / Make Ready of an operation. Let's say 3 sheets are wasted just setting it up.
The third one is the Board lost while running the operation, i.e. 1.5% of the material (sheets) is thrown out as spoilage.
So, for those 10 sheets needed to produce 100 pieces we will need 14 sheets:
105 minus 3 Wasted during MR leaving 102 - 1.5% (i.e. 102 * 1.5% = 1.53 == 2 sheets spoiled.) = 100.
The 1st record Quantity Allocated = 100.
The 2nd record Quantity Allocated = 3.
The 3rd record Quantity Allocated = 1.5 (which translates as 1.5%)
So, once I add records 1 & 2, I then take that SUM and multiply by 1 + (Qty_Alloc * .01) and that's the Total Material quantity.
The final query will be quite a statement.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
February 1, 2012 at 2:19 pm
So what does the actual data in the column look like? Your description left me scratching my head in bewilderment. :w00t:
_______________________________________________________________
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/
February 1, 2012 at 2:44 pm
I'm also a bit confused about what the actual data looks like.
"WHERE EST_UOM <> '%'" assumes that the actual value in the column is "%". It will still return rows with values like 90% or 1.5% and only exclude rows where the value is the "%" sign and only the "%" sign because "<>" is doing a comparison of equality. You're saying you don't want records where est_uom is literally "%". Brackets wont help in that situation either. It will just make it exclude any row where that value is "[%]".
The wildcards, brackets, and the escape keyword will only work for pattern matching using LIKE or NOT LIKE.
Excluding items in a column that have a % sign (such as 90% or 1.5%) would be just like Sean said except "NOT LIKE" instead of "LIKE" so...
where EST_UOM not like '%[%]%'
or, specifying an escape character
where EST_UOM not like '%!%%' escape '!'
February 2, 2012 at 7:42 am
Please accept my apologies! Problem solved.
Your responses (and it being the morning when my mind is fresh) got me to stop and 'walk' through everything again.
I knew the wildcard pertained to LIKE. Yesterday, when the results included the "%" record I thought maybe it was also applicable within any string. (Even though, thinking back to everything I read yesterday referenced LIKE).
So, this morning here I am and I'm looking at the data again, here are sample records:
MtlCatest_uomEstQty
RSCMPTN1.8558149
RSCMPTN0.2264094
RSCMP %8.5
Then it hit me ... What if there are any spaces with the %.
Sure enough, the literal value that is stored is <space>%.
Well, in this post it is distinct. In the Results pane it wasn't - I just changed the results pane font from MS Sans Serif to Courier.
I edited the SQL statement:select ORDER_NO, SUM(qty_alloc)
from ORD_MATL_CST
where MATL_CATEGORY = 'RSCMP' and EST_UOM <> ' %' and ORDER_NO = 66196
group by ORDER_NO
And the results are as expected:ORDER_NO(No column name)
661962.0822243
Instead of 10.582224
Thanks for your help. And again, I apologize for not figuring it out before.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
February 2, 2012 at 7:51 am
OK that explains a lot then 🙂 Spaces tacked on at the start or end of a result can be a pain to troubleshoot when you're not expecting them, glad you got it worked out.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply