SQL dans Webi

Sur les 30 derniers jours

SELECT

Table__1."D_DAY"

FROM

"_SYS_BIC"."move01.stock.StockMovementDaily/AN_STOCK_MVT_DAILY"  Table__1

WITH PARAMETERS ('PLACEHOLDER' = ('$$PERIOD_TYPE$$', 'Start Date-End Date'),'PLACEHOLDER' = ('$$START_DATE$$',’2017-09-18’),'PLACEHOLDER' = ('$$END_DATE$$','2017-09-18'),'PLACEHOLDER' = ('$$STORE_COMPARISON$$', 'Total Surface'),'PLACEHOLDER' = ('$$DATE_COMPARISON$$', 'Calendar Date'),'PLACEHOLDER' = ('$$CURRENCY$$','EUR'))

All the red values are now hardcoded in my SQL so the prompt will not appear for these fields.

Sometimes it can be very useful to hide these fields….Since few months the users ask us to hide some of these fields because it’s always the same value they have to enter.

Additional tips : It’s possible to have dynamic field like current date for example :

Example with the selection for the last 30 days :

Table__1."CALDAY"  BETWEEN (SELECT ADD_DAYS (TO_DATE (CURRENT_DATE, 'YYYY-MM-DD'), -30) "current date" FROM DUMMY)  AND (SELECT CURRENT_DATE "jour_actuel" FROM DUMMY)

ATTENTION : ce texte génére du bordel dans l'éditeur de script à cause de caractère invisible

Sur 2 journée uniquement et pas un intervalle

SQL POUR LES STOCKS (suivi PRMP)

SELECT bla bla bla

FROM

"_SYS_BIC"."move01.SalStocBud.StockBudgetDaily/CA_STOCK_BUDGET_DAILY" Table__2

WHERE

( ( (Table__2."CALENDAR_KEY" = ( ifnull(@Prompt(Start Date),now()) )

or Table__2."CALENDAR_KEY" = ifnull(@Prompt(End Date),now())

) ) )

AND

(

Table__2."C_SITE_LEVEL3" || ' ' || Table__2."L_SITE_LEVEL3" IN ( 'CASTO_STORES Castorama Stores' )

)

VINCENT

SELECT

RIGHT(Table__1."PurchasingDocument", 9),

Table__1."DeliveryDocument"

FROM

"_SYS_BIC"."kingfisher.logistics.DeliveryDocumentItem/DeliveryDocumentQuery" Table__1

WHERE

(

RIGHT(Table__1."PurchasingDocument", 9) IN @dpvalue('A', DP0.DO168)

AND

Table__1."SAPClient" = @Prompt(SAP Client)

)

/ Universe: @Variable('UNVNAME') - User: @Variable('BOUSER') - Document: @Variable('DOCNAME') /