instead of Timestamp sql function in QV Load Script

[user-azadi]

Hello All...

i have this syntax in sql syntax :

----------------------------------------------

SQL

SELECT

   Year

  ,Month

  ,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

,sum(Credit) - sum(Debit) as mandeh

      , Date(Floor(Timestamp(item.VoucherDate,'DD/MM/YYYY hh:mm:ss')),'DD.MM.YYYY') as VoucherDate

FROM

----------------------------------------------

when i run that  in qv script , don't identify Timestamp function (and maybe Date function) .because sql haven't Timestamp function, and maybe Date function)

what must i to do?

[kaushiknsolanki]

Hi,

Try this using the preceding load.

Load

Year

  ,Month

  ,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

,mandeh,

Date(Floor(item.VoucherDate),'DD/MM/YYYY') as VoucherDate;

SQL

SELECT

   Year

  ,Month

  ,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

,sum(Credit) - sum(Debit) as mandeh

      ,item.VoucherDate

FROM

Regards,

Kaushik Solanki

[rohitraut]

Hello ,

Try this

SQL

SELECT

   Year

  ,Month

  ,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

,sum(Credit) - sum(Debit) as mandeh

      , Date(Floor(TO_CHAR(item.VoucherDate,'DD/MM/YYYY hh:mm:ss')),'DD.MM.YYYY') as VoucherDate

FROM

[user-azadi]

Dear Rohit

with use To_Char function , show error message in qv script.

[user-azadi]

expression of fied "mandeh" is

sum(Credit) - sum(Debit) as mandeh

and in output qv , show zero value.this mean calculate in sql section , don't work in load section.

[rohitraut]

What is error message

[user-azadi]

[rohitraut]

Try this ,

SQL

SELECT

   Year

  ,Month

  ,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

,sum(Credit) - sum(Debit) as mandeh

      , Date(Floor(To_Timestamp(item.VoucherDate,'DD/MM/YYYY hh:mm:ss')),'DD.MM.YYYY') as VoucherDate

FROM

[user-azadi]

[rohitraut]

SQL

SELECT

   Year

  ,Month

  ,GacBranchRef

,GacSecondDetailAccountRef

,GacFourthDetailAccountRef

,sum(Credit) - sum(Debit) as mandeh

      , Date(Floor(Date#(item.VoucherDate,'DD/MM/YYYY hh:mm:ss')),'DD.MM.YYYY') as VoucherDate

FROM