Query Error

[Former Member]

I need do the next query in Query Generator and I don't know how is the mistake: (I think that the problem is "Order by")

Select CYO.NumArticulo, CYO.Proveedor, CYO.NomProveedor, Count(*)as cantidad From

(Select OIT.ItemCode as NumArticulo, OIN.CardCode as Proveedor, OIN.CardName as NomProveedor

From [OINV] OIN

inner Join [INV1] INV on INV.DocEntry = OIN.DocEntry

inner Join [OITM] OIT on OIT.ItemCode = INV.ItemCode

Where (OIN.CardCode = '[%0]' or '[%0]' = '[%0]') And

(OIN.U_TGV_ACC_ORD_FLAC = 1) And

(OIN.TaxDate >= '[%1]' or '[%1]' = '[%1]') And

(OIN.TaxDate >= '[%2]' or '[%2]' = '[%2]') And

(OIN.DocDueDate >= '[%3]' or '[%3]' = '[%3]') And

(OIN.DocDueDate >= '[%4]' or '[%4]' = '[%4]') And

(OIN.DocDate >= '[%5]' or '[%5]' = '[%5]') And

(OIN.DocDate >= '[%6]' or '[%6]' = '[%6]') And

(OIT.U_TGV_PERCEP_FLAG = 0)

Union ALL

Select RIN.ItemCode as NumArticulo, ORI.CardCode as Proveedor, ORI.CardName as NomProveedor

From [ORIN] ORI

inner Join [RIN1] RIN on RIN.DocEntry = ORI.DocEntry

inner Join [OITM] OIT on OIT.ItemCode = RIN.ItemCode

Where (ORI.CardCode = '[%0]' or '[%0]' = '[%0]') And

(ORI.U_TGV_ACC_ORD_FLAC = 1) And

(ORI.TaxDate >= '[%1]' or '[%1]' = '[%1]') And

(ORI.TaxDate >= '[%2]' or '[%2]' = '[%2]') And

(ORI.DocDueDate >= '[%3]' or '[%3]' = '[%3]') And

(ORI.DocDueDate >= '[%4]' or '[%4]' = '[%4]') And

(ORI.DocDate >= '[%5]' or '[%5]' = '[%5]') And

(ORI.DocDate >= '[%6]' or '[%6]' = '[%6]') And

(OIT.U_TGV_PERCEP_FLAG = 0)) CYO

Group By CYO.NumArticulo,CYO.Proveedor,CYO.NomProveedor

[Former Member · Accepted solution]

Hi Roberto,

Try the following:

Change

From OINV OIN...... From ORIN ORI

to

From [dbo].[OINV] OIN...... From [dbo].[ORIN] ORI 

I assume your actual query is not missing the [] from each SAP parameter e.g.

'[%0]' ...

I'd also look at removing the parameter comparison with itself since it will return all records it finds regardless of the value you supply, since

'[%0]'

will always equal itself.

Instead of

OR '[%0]' = '[%0]'

use something like

OR ISNULL('[%0]','') = ''

This will only return records if you don't supply a parameter value.

Note there is no space between the above single quotation marks.

Regards,

Andrew.

[Former Member · Accepted solution]

Excelent!, it's working!

Thank you so much!

Roberto.

[Former Member · Accepted solution]

No problem. Glad I could help.

Actually there was a typo in my reply above;

This will only return records if you don't supply a parameter value.

should read

This will only return all records if you don't supply a parameter value.

Just incase there was any confusion.

Regards,

Andrew.

[Former Member · Accepted solution]

Perfect Andrew!!

Thanks again!

Roberto.