Shulou(Shulou.com)06/01 Report--

In order to solve the problem of how to write complex report SQL statements in data warehouse, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find an easier way.

In the process of making the basic report of the data warehouse, SQL is usually used as the data source, but the ordinary SQL is not suitable to deal with some complex logical judgments. Generally speaking, the data types to be queried mainly include date type, number type and string type. In front of the report query boundary, the query data will actually be processed by default, for example, some fields can be input or not, and the input fields need to be queried according to the input content. The unentered fields usually choose to ignore the existence of the condition. How to judge whether the field is entered or not? of course, some default values are provided for these unentered fields, such as fields of a certain number type are not entered. A default value of-1 is assigned. If a string field is not entered, the default value is''. If a date is not entered, the default value is SYSDATE. At this point, as long as the SQL for different default values and the value should be entered to deal with the OK.

Of course, when the more complex query logic is really not suitable for SQL processing, it is best to choose to use stored procedures; secondly, too complex SQL may bring database performance problems, so these SQL-based reports are best not to operate on large data tables.

The following is a data table containing the above three data types and populated with some test data:

CREATE TABLE TestReportParameter

(

VarcharField VARCHAR2 (20)

NumberField NUMBER (10dint 0)

DateField DATE

);

INSERT INTO TestReportParameter VALUES ('axiomagore1, Syndometh1)

INSERT INTO TestReportParameter VALUES ('baked Magazine 2CDMA Sysdate)

INSERT INTO TestReportParameter VALUES ('clockwork 3, SYSDATERE2)

INSERT INTO TestReportParameter VALUES ('daddy memorial4, SYSDATEMur2)

COMMIT

The secret of processing is to make a judgment between the default value and the input value, keeping the existence of any kind of value as TRUE.

For simple processing of single-valued data, see the following script

SELECT *

FROM TestReportParameter

WHERE ('= & VarcharField OR VarcharField = & VarcharField)

AND (- 1 = & NumberField OR NumberField = & NumberField)

For the processing of multi-selected values, the DECODE function must be used to avoid errors, and the logic of processing is the same as above.

SELECT *

FROM TestReportParameter

WHERE (

'' = DECODE (& VarcharField,'',', & VarcharField)

OR

VarcharField IN & VarcharField)

)

AND (

-1=DECODE & NumberField,-1,-1,&NumberField)

OR

NumberField IN & NumberField)

)

The answer to the question about how to write the complex report SQL statement in the data warehouse is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.