The database version:
Select @ @ version;
SAP IQ/16.0.0.809/151113/P/sp08.39/Enterprise on - x86_64-2.6.18-194. The el5/64 - bit/2015-11-13 12:24:54
Sybase syntax is not clear how to construct the test data, party a and not let the build table, so the test data in oracle format listed, please forgive me,
Requirements, the use of SQL to complete a query,
There I have a day of payment days of the table, they should be continuous, but there may be a gap, I need to put these vacant payment days detail found out, oracle general will construct cartesian product outside doing relative to complete,
Like this:
With the TAB as (
Select the 'a' typ, 20180601 dt from dual union all
Select the 'a' typ, 20180602 dt from dual union all
Select the 'a' typ, 20180603 dt from dual union all
Select 'b' typ, 20180601 dt from dual union all
Select 'b' typ, 20180602 dt from dual union all
Select 'b' typ, 20180603 dt from dual union all
Select the 'c' typ, 20180601 dt from dual union all
Select the 'c' typ, 20180603 dt from dual
),
Tab2 as (
The select to_char (to_date (' 20180601 ', 'yyyymmdd') + level 1, 'yyyymmdd') full_dt from dual connect by level & lt;=3
),
Tab3 as (
Select * from tab2, (select distinct typ from TAB)
),
Tab4, as (
The select t1 full_dt, t1 typ, t2. Dt from tab3 t1, TAB t2
Where a t1. Typ=t2. Typ (+)
And t1. Full_dt=t2. Dt (+)
)
Select a t1 full_dt, typ from tab4, t1
Where a t1. Dt is null
;
But I only find a way to dynamically constructed continuous date of sybase, used in the subquery complains,
Select * from (
Select the top 6 NUMBER (*) num,
Convert (char (10), dateadd (mm - NUMBER (*), cast (' 2018/06/14 as datetime)), 112) acct_month
The from systable
);
With the TAB as (
Select the top 6 NUMBER (*) num,
Convert (char (10), dateadd (mm - NUMBER (*), cast (' 2018/06/14 as datetime)), 112) acct_month
The from systable
)
Select * from TAB.
My question is how to construct the cartesian product? Or use other ways to also go,
Under the real need is to: a user in the database entities
TABLE_A_20180601;
TABLE_A_20180602;
TABLE_B_20180601;
TABLE_A_201806;
TABLE_A_201805;
.
Such a strict naming convention of the table, I want to find out TABLE_A_M, TABLE_A_D, TABLE_B_D each logical table, such as the lack of detailed payment days, (M is on table, D is RiBiao), statistical payment days interval can be written to death,
Write a SQL, has been an error...
- three missing payment days
With the TAB as (
Select a t1 table_name,
Reverse (substring (reverse (t1, table_name), 0, charindex (' _ ', reverse (t1. Table_name)))) acct_month,
Reverse (substring (reverse (t1, table_name), charindex (' _ ', reverse (t1. Table_name)) + 1, 999)) | | case length (substring (reverse (t1) table_name), 0, charindex (' _ ', reverse (t1. Table_name)))) when 6 then '_M when 8 then' _D 'else' end logic_table,
Case length (substring (reverse (t1) table_name), 0, charindex (' _ ', reverse (t1. Table_name)))) when 6 then 'M' when 8 then 'D' else 'end date_type
The from (
The select a.t able_name from systable a, sysuserperm b
Where a.c reator=b.u ser_id
And b.u ser_name='A_USER'
) t1
),
Tab2 as (
Select * from the TAB t1
Where 1=1
And t1. Date_type is not null
And ((t1) date_type='M' and t1) acct_month & gt; 201711 and t1. Acct_month & lt;=201806) or (t1) date_type='D' and t1) acct_month & gt; 20180401 and t1. Acct_month & lt;=20180614))
),
Table_day as (
Select top 74 NUMBER (*) num,
Convert (char, cast (' 2018/06/15 as datetime) - number (*), 112) acct_day
The from systable
),
Table_month as (
Select the top 6 NUMBER (*) num,
Convert (char (10), dateadd (mm - NUMBER (*), cast (' 2018/06/14 as datetime)), 112) acct_month
The from systable
),
Tab3 as (
Select distinct t1 logic_table, t1. Date_type from tab2 t1
)
, tab4, as (
Select *
Table_day from tab3 t1, t2
Where a t1. Date_type='D'
)
, tab5 as (
Select a t1 logic_table, t2 table_name, t1. Acct_day from tab4, t1 left join tab2 t2
On (t1) logic_table | | '_' | | t1. Acct_day=t2, table_name)
)
Select * from tab5 t1
Where a t1. Table_name='or t1. Table_name is null or a t1. Table_name=null
;
Day, month lack detail is divided into two SQL write to also go, everybody has what good method?
CodePudding user response:
Don't understand the sybase, top posts,PS: there is a long time no new posts, lying here first, wait for someone to give you answer, I'll give you to move in the past,