July 25, 2013 at 10:36 pm
I Have A Product Name FIX_10_MTH_AM_CT_NC_DB_LC
I Have a Validation Like All Components Of The ProductName Must Be The Codes From the Tables A,B,C,D,E,F,G,H,I,J
Like THat .
Fix Must be A Code From A
10 must be a value From B .................
If All the components are from the tables then i Should return 1 else return 0.
Please Help ME...........
July 25, 2013 at 10:44 pm
Please provide table structures and try to explain more clearly what it is you are trying to accomplish.
A good way to help explain what you want is to provide sample data and expected sample results.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 25, 2013 at 10:53 pm
Something like this should list out any of the products that fail your audit.
WITH ValidationTables (n, )
SELECT 1, [Key] FROM A
UNION ALL SELECT 2, FROM B
UNION ALL SELECT 3, FROM C
UNION ALL SELECT 4, FROM D
UNION ALL SELECT 5, FROM E
UNION ALL SELECT 6, FROM F
UNION ALL SELECT 7, FROM G
UNION ALL SELECT 8, FROM H
),
Key2Validate (Product) AS (
SELECT 'FIX_10_MTH_AM_CT_NC_DB_LC'
)
SELECT DISTINCT Product
FROM Key2Validate a
CROSS APPLY DelimitedSplit8K(Product, '_') b
LEFT JOIN ValidationTables c ON b.ItemNumber = c.n AND b.Item = c.Item
WHERE c. IS NULL
The DelimitedSplit8K[/url] can be downloaded from the link provided.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 25, 2013 at 10:54 pm
dwain.c (7/25/2013)
Something like this should list out any of the products that fail your audit.
WITH ValidationTables (n, )
SELECT 1, [Key] FROM A
UNION ALL SELECT 2, FROM B
UNION ALL SELECT 3, FROM C
UNION ALL SELECT 4, FROM D
UNION ALL SELECT 5, FROM E
UNION ALL SELECT 6, FROM F
UNION ALL SELECT 7, FROM G
UNION ALL SELECT 8, FROM H
),
Key2Validate (Product) AS (
SELECT 'FIX_10_MTH_AM_CT_NC_DB_LC'
)
SELECT DISTINCT Product
FROM Key2Validate a
CROSS APPLY DelimitedSplit8K(Product, '_') b
LEFT JOIN ValidationTables c ON b.ItemNumber = c.n AND b.Item = c.Item
WHERE c. IS NULL
The DelimitedSplit8K[/url] can be downloaded from the link provided.
And how did you derive this result from the description provided :crazy:?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 25, 2013 at 10:58 pm
SQLRNNR (7/25/2013)
dwain.c (7/25/2013)
Something like this should list out any of the products that fail your audit.
WITH ValidationTables (n, )
SELECT 1, [Key] FROM A
UNION ALL SELECT 2, FROM B
UNION ALL SELECT 3, FROM C
UNION ALL SELECT 4, FROM D
UNION ALL SELECT 5, FROM E
UNION ALL SELECT 6, FROM F
UNION ALL SELECT 7, FROM G
UNION ALL SELECT 8, FROM H
),
Key2Validate (Product) AS (
SELECT 'FIX_10_MTH_AM_CT_NC_DB_LC'
)
SELECT DISTINCT Product
FROM Key2Validate a
CROSS APPLY DelimitedSplit8K(Product, '_') b
LEFT JOIN ValidationTables c ON b.ItemNumber = c.n AND b.Item = c.Item
WHERE c. IS NULL
The DelimitedSplit8K[/url] can be downloaded from the link provided.
And how did you derive this result from the description provided :crazy:?
Actually I missed the part about returning 1 or 0 but probably the OP can manipulate my result to get there.
I understood he wanted to validate each sub-product code against a different table.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 25, 2013 at 11:01 pm
dwain.c (7/25/2013)
SQLRNNR (7/25/2013)
dwain.c (7/25/2013)
Something like this should list out any of the products that fail your audit.
WITH ValidationTables (n, )
SELECT 1, [Key] FROM A
UNION ALL SELECT 2, FROM B
UNION ALL SELECT 3, FROM C
UNION ALL SELECT 4, FROM D
UNION ALL SELECT 5, FROM E
UNION ALL SELECT 6, FROM F
UNION ALL SELECT 7, FROM G
UNION ALL SELECT 8, FROM H
),
Key2Validate (Product) AS (
SELECT 'FIX_10_MTH_AM_CT_NC_DB_LC'
)
SELECT DISTINCT Product
FROM Key2Validate a
CROSS APPLY DelimitedSplit8K(Product, '_') b
LEFT JOIN ValidationTables c ON b.ItemNumber = c.n AND b.Item = c.Item
WHERE c. IS NULL
The DelimitedSplit8K[/url] can be downloaded from the link provided.
And how did you derive this result from the description provided :crazy:?
Actually I missed the part about returning 1 or 0 but probably the OP can manipulate my result to get there.
I understood he wanted to validate each sub-product code against a different table.
Hmmm. That's some pretty good reading between the lines 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 25, 2013 at 11:05 pm
SQLRNNR (7/25/2013)
dwain.c (7/25/2013)
SQLRNNR (7/25/2013)
dwain.c (7/25/2013)
Something like this should list out any of the products that fail your audit.
WITH ValidationTables (n, )
SELECT 1, [Key] FROM A
UNION ALL SELECT 2, FROM B
UNION ALL SELECT 3, FROM C
UNION ALL SELECT 4, FROM D
UNION ALL SELECT 5, FROM E
UNION ALL SELECT 6, FROM F
UNION ALL SELECT 7, FROM G
UNION ALL SELECT 8, FROM H
),
Key2Validate (Product) AS (
SELECT 'FIX_10_MTH_AM_CT_NC_DB_LC'
)
SELECT DISTINCT Product
FROM Key2Validate a
CROSS APPLY DelimitedSplit8K(Product, '_') b
LEFT JOIN ValidationTables c ON b.ItemNumber = c.n AND b.Item = c.Item
WHERE c. IS NULL
The DelimitedSplit8K[/url] can be downloaded from the link provided.
And how did you derive this result from the description provided :crazy:?
Actually I missed the part about returning 1 or 0 but probably the OP can manipulate my result to get there.
I understood he wanted to validate each sub-product code against a different table.
Hmmm. That's some pretty good reading between the lines 😉
Obviously you've never used:
EXEC sp_universal_translator @ForumPost='http://www.sqlservercentral.com/Forums/FindPost1477853.aspx'
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 25, 2013 at 11:12 pm
dwain.c (7/25/2013)
SQLRNNR (7/25/2013)
dwain.c (7/25/2013)
SQLRNNR (7/25/2013)
dwain.c (7/25/2013)
Something like this should list out any of the products that fail your audit.
WITH ValidationTables (n, )
SELECT 1, [Key] FROM A
UNION ALL SELECT 2, FROM B
UNION ALL SELECT 3, FROM C
UNION ALL SELECT 4, FROM D
UNION ALL SELECT 5, FROM E
UNION ALL SELECT 6, FROM F
UNION ALL SELECT 7, FROM G
UNION ALL SELECT 8, FROM H
),
Key2Validate (Product) AS (
SELECT 'FIX_10_MTH_AM_CT_NC_DB_LC'
)
SELECT DISTINCT Product
FROM Key2Validate a
CROSS APPLY DelimitedSplit8K(Product, '_') b
LEFT JOIN ValidationTables c ON b.ItemNumber = c.n AND b.Item = c.Item
WHERE c. IS NULL
The DelimitedSplit8K[/url] can be downloaded from the link provided.
And how did you derive this result from the description provided :crazy:?
Actually I missed the part about returning 1 or 0 but probably the OP can manipulate my result to get there.
I understood he wanted to validate each sub-product code against a different table.
Hmmm. That's some pretty good reading between the lines 😉
Obviously you've never used:
EXEC sp_universal_translator @ForumPost='http://www.sqlservercentral.com/Forums/FindPost1477853.aspx'
ROFL
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply