sql + variable

[fmazzarelli]

Hi Community,

Who can help me?

SET vDate = '01/01/2015';

[Reload]:

LOAD "vDate";

select

// CONVERT(VARCHAR(10),"FATT_DAT",120) AS "DataDellaFattura",

CONVERT(VARCHAR(10),"FATT_DAT", 103) AS "Invoice_Date",

  FATT_DAT,

  FATT_APPKY ,

  FATT_NUMERO,

  FATT_TOT_IMPONIBILE as Imponibile,

  FATT_TOT_IVA,FATT_TOT as Totale,

  FATT_SERIE as Serie,

  FATT_COMP_ANNO as Anno,

  FATT_COMP_MESE as Mese,

  ANAB_RAG_SOC,

  FATT_TIPO_DESC,

  FATT_CLIENTEKY,

  ANAB_RES_LOC as "prov2011.Name",

  ANAB_RES_PRV as TERR_DESCR,

  ANAB_RES_NAZ

  from FreightConap.dbo.FATTURE_TESTATA

inner join FreightConap.dbo.ANABASE

  on FATT_CLIENTEKY = ANAB_KY

inner join FreightConap.dbo.FATTURE_TIPO

  on FATT_TIPKY = FATT_TIPO_KY

WHERE Invoice_Date >= $(vDate);

Si è verificato il seguente errore:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Il nome di colonna 'Invoice_Date' non è valido.

L'errore si è verificato qui:

select CONVERT(VARCHAR(10),"FATT_DAT", 103) AS "Invoice_Date", FATT_DAT, FATT_APPKY , FATT_NUMERO, FATT_TOT_IMPONIBILE as Imponibile, FATT_TOT_IVA,FATT_TOT as Totale, FATT_SERIE as Serie, FATT_COMP_ANNO as Anno, FATT_COMP_MESE as Mese, ANAB_RAG_SOC, FATT_TIPO_DESC,

why is it not granted a field of the table ?

[marcus_sommer]

Try it with single-quotes around the variable: WHERE Invoice_Date >= '$(vDate)';

- Marcus

[ogster1974]

Invoice_Date is an Alias in the data loader not known in SQL you must use the real value unless you have already defined it in a view in SQL already.

try WHERE CONVERT(VARCHAR(10),"FATT_DAT", 103) >= $(vDate);

[DavidFoster1]

This all depends on the date format your database prefers.

Firstly you should be using the source field FATT_DAT not the alias the expression is giving when the query results are output.

I would suggest experimenting in a SQL editor to find the best date format and as Marcus suggests dont forget to put quotes around the variable.

I always testing serial dates first e.g. today = 42304

[fmazzarelli]

Hi Marcus,

I have correct the line as below.

But I receive error source.

___________________________________

WHERE Invoice_Date >= $('vDate');

Si è verificato il seguente errore:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Sintassi non corretta in prossimità di '='.

L'errore si è verificato qui:

select CONVERT(VARCHAR(10),"FATT_DAT", 103) AS "Invoice_Date", FATT_DAT, FATT_APPKY ,

[marcus_sommer]

Have a look on the suggestions from Andy and David - both are right, that you couldn't use an alias-name and that the check should be best on a pure numeric level (avoids many problems with formats).

- Marcus

[fmazzarelli]

Hi,

but i use  CONVERT(VARCHAR(10),"FATT_DAT", 103) AS Invoice_Date,

to convert a format date

from YYYYMMDD to DD/MM/YYYY

[marcus_sommer]

try WHERE CONVERT(VARCHAR(10),"FATT_DAT", 103) >= '$(vDate)';

[ogster1974]

The issue is SQL doesn't recognise the Invoice_Date as a qualified name in your WHERE clause you need to use

CONVERT(VARCHAR(10),"FATT_DAT", 103) in your where clause instead.

[fmazzarelli]

Hi,

GRANDE!!!! (italian expression)