October 11, 2016 at 12:07 pm
Hi,
I'm trying to write a code to do a recursive check. I can get the below sample data from different table into a single table. My first record should always start with the Code D and end last record should always end with A. So my idea here is to find out the breakage in this link. That is to identify, the records which starts with D but does not end with A, either end with C or B.
To reach till A. I have to do a recursive / iterative check to find out In/Out records. If I have an out, that corresponding In should be identified using "ProductID". Once If found the corresponding In, respective Out should be identified using GlobalID. So alternatively I have to found the Out and In and then In and Out.
ProductIDGlobalIDTypeCode
001 100 Out D
001 200 In C
002 200 Out C
002 300 In C
003 200 Out B
003 300 In B
004 300 Out B
004 400 In A
How to write a looping code, to get the above given result set.
October 11, 2016 at 12:16 pm
What are you checking? Why do you need it to be recursive?
October 11, 2016 at 1:14 pm
kalyav21 (10/11/2016)
ProductIDGlobalIDTypeCode
001 100 Out D
001 200 In C
002 200 Out C
002 300 In C
003 200 Out B
003 300 In B
004 300 Out B
004 400 In A
How to write a looping code, to get the above given result set.
maybe would help us all if we knew what your initial data looked like me thinks.
see here for a friendly explanation of how to post some set ups for your data
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 13, 2016 at 12:24 pm
>> I'm trying to write a code to do a recursive check. <<
I am not sure what that means. A recursive check () constraint or what?
>> I can get the below sample data from different table into a single table. My first record [sic: rows are not records] should always start with the Code D and end last record [sic] should always end with A. <<
Where is the DDL? Why did you post a picture instead of code? Please read the forum rules before you do this again. But even worse than your bad manners, is the lack of the key which is required for anything to be a table, and the way you ignored the information principle. If you had read Dr. Codd or any book on RDBMS, you would know the relationships are encoded as scaler of values in the columns of the rows of a table, and the table has no ordering. Your depending apparently on the physical ordering in your picture, and not in the DDL!
Finally, your next design flaw is thinking that "in" and "out" are values. No, they are not! They are attributes that hold the value (namely a timestamp as to when something went from one state to the other. The (in, out) pair is itself a value, just like (longitude, latitude). Would you actually split longitude and latitude like this? No, of course not!
>> So my idea here is to find out the breakage in this link [sic]. That is to identify, the records [sic] which starts with D but does not end with A, either end with C or B. <<
Again, this is completely absurd. The term "link" refers to pointer chains and has nothing to do with RDBMS.
This is so incomplete, by the way, you did not even give us the name of this table! I am still trying to figure out exactly what a "global_id" is; what is a global? What is the ISO or ANSI standard that we use to identify it? Why did you invent your own product_id when all the industries have standards for these identifiers? GTIN? EAN? UPC?
>> To reach till A. I have to do a recursive / iterative check to find out In/Out records [sic]. If I have an out, that corresponding In should be identified using "product_id". Once If found the corresponding In, respective Out should be identified using global_id.<<
One of the principles of RDBMS is that an entity has one and only one name. Something cannot come in as a product and then mutate into a "global" without some kind of process.
There is no such thing as a generic "<nothing in particular>_type" or a "<nothing in particular>_code"; this is derived directly from the law of identity, the foundation of all Western logic. It states that "to be is to be something in particular; to be nothing in particular or everything in general is to be nothing at all."
I am also trying to figure out why you think this would be recursive.
If you are trying to see that your product goes through steps {A,B,C,D} then were looking at a relational division problem. Would you like to try again with more information and follow the forum rules?
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply