none
SQL-Create Single SELECT String From Multiple Rows

    Question

  • Hi ,,
    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)


    any help??????



    • Edited by SQL_helpus Tuesday, August 20, 2013 12:25 PM
    Tuesday, August 20, 2013 9:19 AM

All replies

  • 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)"? 

    Tuesday, August 20, 2013 1:20 PM
  • HI Scott,
    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
    Tuesday, August 20, 2013 1:29 PM
  • And I see now that you simply reposted your question - using a new identity. 

    previous thread

    Tuesday, August 20, 2013 1:30 PM
  • Hi

    Yeah  ,But Thats was by my team Member(Colleague).What I mentioned is current Challenge\Requirement . 



    • Edited by SQL_helpus Tuesday, August 20, 2013 1:43 PM
    Tuesday, August 20, 2013 1:43 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.
    Tuesday, August 20, 2013 2:36 PM
  • 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

    Tuesday, August 20, 2013 5:02 PM
  • Hi ,

    Any Help in Achieving above task ??

    Wednesday, August 21, 2013 5:47 AM