按两列排序表

Hi Everybody,

       I have a table which contains two type of Establishments:

If the SIRET IS NULL --> IT is an ENTREPRISE

如果SIRET为NULL - > IT是ENTREPRISE

IF the SIRET IS NOT NULL --> It is an etablishement

如果SIRET不是空 - >这是一个etablishement

In My table, I have always this condiction: if SIRET is NULL that means SIREN is NOT NULL.

在我的表中,我总是有这个condiction:如果SIRET为NULL,则意味着SIREN不是NULL。

What I want is to display the SIRET and SIREN in the same COLUMNS and order the table by company name and (SIRET IF NOT NULL) Or ( SIREN if Not NULL)

我想要的是在同一个COLUMNS中显示SIRET和SIREN,并按公司名称排序表(SIRET IF NOT NULL)或(如果不为空,则为SIREN)

This is my query:

这是我的查询:

          select  CUSTOMER.CUSTOMER_ID, CUSTOMER.CUSTOMER_RCE_ID, 
          CUSTOMER.COMM_REGNUM_CUST,CUSTOMER.GROUP_CODE, 
          CUSTOMER.CUSTOMER_CORPORATE_NAME, 
          Replace(CUSTOMER.ADDRESS_COMPANY, '|', ', ') as ADDRESS_COMPANY, 
          CUSTOMER.COUNTRY_CODE, CUSTOMER.POST_CODE, CUSTOMER.CITY, 
          CUSTOMER.CUSTOMER_ATTRIBUTE1,CUSTOMER.CUSTOMER_ATTRIBUTE2, 
          CUSTOMER.CUSTOMER_ATTRIBUTE3, CUSTOMER.CUSTOMER_ATTRIBUTE4, CUSTOMER.CUSTOMER_ATTRIBUTE5, CUSTOMER.CREATED_DATE, 
          CUSTOMER.CREATED_BY,CUSTOMER.UPDATED_DATE, CUSTOMER.UPDATED_BY, CUSTOMER.TENANT_ID, 
          CUSTOMER.CUSTOMER_ACCOUNT, CUSTOMER.VAT_INTRA, CUSTOMER.VAT_SETTLEMENT,CUSTOMER.IS_HEADQUARTERS, 
          CUSTOMER.CUSTOMER_SAP_ID, CUSTOMER.ADDRESSGUID, CUSTOMER.SIREN_CORP as SIREN, CUSTOMER.COMMERCIAL_SEGMENT, CUSTOMER.CODE_NAF, 
          CUSTOMER.JUR_STATUS_COMP, CUSTOMER.SHARE_CAP_AMT,CUSTOMER.IS_ACTIVE_RCE, CUSTOMER.REGISTRATION_PLACE,STATUS.STATUS_NAME , 
          CUSTOMER.RCE_ETAB_ID, REPLACE (CUSTOMER.COMM_REGNUM_CUST,' ','') AS SIRET  from CUSTOMER


          join CUSTOMER_STATUS on CUSTOMER_STATUS.CUSTOMER_STATUS_ID = ( 
          select * from (select CUSTOMER_STATUS_ID from CUSTOMER_STATUS 
          where CUSTOMER.CUSTOMER_ID = CUSTOMER_STATUS.CUSTOMER_ID order by CUSTOMER_STATUS.UPDATED_DATE desc)  CUSTOMER_STATUS where rownum = 1 )


          join STATUS on CUSTOMER_STATUS.STATUS_ID = STATUS.STATUS_ID

How can I order by CUSTOMER.CUSTOMER_CORPORATE_NAME(COMPANY OR ESTABLISHEMENT NAME) and another column (SIRET OR SIRET ASSUMING IF ONE IS NULL THE OTHER IS NOT) and Put the SIRET AND SIREN IN THE SAME COLUMN RESULT

我如何通过CUSTOMER.CUSTOMER_CORPORATE_NAME(公司或公司名称)和另一栏(SIRET或SIRET假设,如果其中一个是空的,其他不是),并将SIRET和SIREN置于同一列结果中

Thans you in advance.

提前你。

1 个解决方案

#1

A partial solution might be

部分解决方案可能是

SELECT 
        .
        .
        .
       customer.customer_corporate_name, 
       isnull(customer.siren_corp
              ,Replace(customer.comm_regnum_cust, ' ', '')) AS SiretSiren
        .
        .
        .
FROM   customer 
       JOIN customer_status 
        .
        .
        .
         ON customer_status.status_id = status.status_id 
ORDER by customer.customer_corporate_name,SiretSiren