I have a Requirement Where I have to create SELECT statement using Columns of Below Table .
FunctionNAME: Either SQL inbuilt Function or USER function
SYSTEM_FLAG= IF function is SQL inbuilt Function then "Y" else N
OPERAND =DatabaseName..TABLENAME..COLUMNName used in FUNCTION
OPERATOR =which Operator to be used between SELECT QUERies
LOGIC of LOCATE function=Below is Sample Table
/*I have Table in Below Format ,
I NEED TO CREATE SELECT STATEMENT USING BELOW COLUMNS, */
CREATE TABLE CUSTOM_FIELD(ID INT ,CUSTOM_FUN_SEQ INT,FUNCTION_NAME VARCHAR(20),OPERAND VARCHAR(100),OPERATOR CHAR(1),SYSTEM_FLAG CHAR(1)) INSERT INTO CUSTOM_FIELD SELECT 1,1,'SUM','DB1..CUSTOMdata.QTY','+','Y' UNION ALL SELECT 1,2,'LOCATE','DB1..CUSTOMdata.QTY,DB1..CUSTOMdata.QTY2','-','N' UNION ALL SELECT 1,3,'UNION_AS','DB1..CUSTOMdata.QTY,DB1.CUSTOMdata.QTY2','*','N' UNION ALL SELECT 1,4,'MAX','DB1..CUSTOMdata.QTY','','Y' UNION ALL SELECT 2,1,'VARIANT','DB1..CUSTOMdata.QTY,DB1..CUSTOMdata.QTY1,DB1..CUSTOMdata.QTY2','-','N' UNION ALL SELECT 2,2,'MIN','DB1..CUSTOMdata.QTY','','Y' UNION ALL SELECT * FROM CUSTOM_FIELD /*lOGIC OF LOCATE =MAX(OPERAND)-MIN(OPERAND) lOGIC OF UNION_AS =MAX(OPERAND)+MIN(OPERAND)*2 lOGIC OF VARIANT = MIN(OPERAND)+MIN(OPERAND)+MIN(OPERAND) CUSTOM_FUN_SEQ REPEATE FOR "ID"COLUMN MEANS WE NEED TO CREATE SINGLE SELECT QUERY FOR SAME ID */ /* BELOW IS OUTPUT REQUIRED */ SELECT (SELECT SUM(DB1..CUSTOMdata.QTY) FROM CUSTOMdata)+ (SELECT MAX(DB1..CUSTOMdata.QTY)-MIN(DB1..CUSTOMdata.QTY2) FROM CUSTOMdata)- (SELECT MAX(DB1..CUSTOMdata.QTY)+MIN(DB1..CUSTOMdata.QTY2)*2 FROM CUSTOMdata)* (SELECT MAX(DB1..CUSTOMdata.QTY) FROM CUSTOMdata) SELECT (SELECT MIN(DB1..CUSTOMdata.QTY)+MIN(DB1..CUSTOMdata.QTY1)+MIN(DB1..CUSTOMdata.QTY2) FROM CUSTOMdata )- (SELECT MIN(DB1..CUSTOMdata.QTY) FROM CUSTOMdata)
- Edited by SQL_helpus Tuesday, August 20, 2013 12:25 PM
You will need to write your own interpreter - and you will find tsql poorly suited for doing that. In short, you've chosen a difficult path for reasons unknown. Given your questions, it is likely that success will be elusive.
While you are considering your approach, you will need to more completely define the domain of values that you intend to support - what they mean, how they are to be used, etc. You are defining a language - and you cannot do that by simply throwing together a few examples. I'll also point out one shortcut that highlights the difficulties that lie ahead. You said: "MAX(OPERAND)+MIN(OPERAND)*2" - is that "(max() + min()) * 2" or "max() + (min()*2)"?
Thanks for Reply
What ever may be the Logic of Function ,Whether ((max()+min()) *2 or max()+(min()*2) it should come in SELECT statement as it is ..You can consider any of the Logic of "LOCATE" or "Union_AS"as this is Sample Data ,As in Real scenario I have multiple Function with Different Logic .
My Concern is How to Make Single SELECT QUERY of it as I mentioned above in my Thead based on Order of CUSTOM_FUN_SEQ and SYSTEM_FLAG columns.
- Edited by SQL_helpus Tuesday, August 20, 2013 1:31 PM
So that makes it different? or OK? As I mentioned, you will need to write your own interpreter. A cursor can be used to traverse rows within a table, from which you can generate your single query which you then execute dynamically. Beyond that, there is little I will offer as this an exceedingly complex task, IMO, that requires skills your team does not possess.
Please post DDL, so that people do not have to guess what the keys, constraint, Declarative Referential integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
>> I have a requirement where I have to create SELECT statement using columns of below table.<<
This is the worst way to try to use SQL. You could not even follow ISO-11179 rules for this crap code. You write with assembly language flags in SQL. You mix data meta-data. Etc.
SQL is a data language. It never used for this kind of programming. NEVER.
How often are you told by a world class expert, who helped standard the language you are using, that you need to stop programming?
--CELKO-- 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