Oledb if query

[VratHimbo]

Hello guys, 
just a quick explanation about Oledb..
I need to check if i've at least an empty value inside the row of an excel file included in a where clause.
FOR EXAMPLE:
Select * FROM Table1 WHERE invoice = [InvoiceID]  
If exist at least one row from this query, i need to check if one of Column1, Column2 or Column3 is empty. Can i implement this check in the where clause? For ex. WHERE invoice = [InvoiceID]  AND [Column1] != '' AND [Column2] != '' AND [Column3] != '' 
I'll tried but it not works.. Any advice?
Thanks in advance

------------------------------
Vrat Himbo
------------------------------

[PvD_SE]

You write that it does not work. What does not work? Which stage action is at fault? What is the exception it throws?

Try this:
Assuming you have the connection string correct, done a connect and an open, including some wait stages, you might want to try and format the Excel Sheetname as [Table1$] instead of plain Table1.

SQL:
Your statement: 
WHERE invoice = [InvoiceID]  AND [Column1] != '' AND [Column2] != '' AND [Column3] != '' 
...will return the row with the matching InvoiceID where comumns 1 and 2 and 3 all must have a value. 
This does not seem to match what you describe.

You can put the check in the SQL but you can also only select on the InvoiceID and then have BP validate the collection that was returned. 

The SQL might look like this:
WHERE invoice = [InvoiceID]  AND ([Column1] = '' OR [Column2] = '' OR [Column3] = '') 
...this would require at least one of the columns 1, 2 or 3 to be empty.


On the other hand is your description  "one of Column1, Column2 or Column3 is empty" a bit fuzzy, in which case I'd like you to ask you to describe better what it is you want. 🙂

------------------------------
Happy coding!
Paul
Sweden
------------------------------

[DaulatRam2]

Select * From [Table1$] Where [invoiceID]= "1234"  AND ([Column1] <>'' OR [Column2] <>'' OR [Column3] <>'') 
will filter for InvoiceID and at least one of Columns having value

Table1: is the name of the Sheet in excel file where you are checking for Invoice ID
invoiceID: is the name of the column in Table1 sheet within excel file.
1234: example Invoice ID you can use a data item stage as well instead of hardcoded value.
Column1,Column2,Column3 are names of the column

cheers

------------------------------
Daulat Ram
------------------------------

[VratHimbo]

Thanks, i need to check if i have empty values in 1 number column [column1] and in 2 string columns [column2] and [column3].
Which is the syntax in that case

------------------------------
Vrat Himbo
------------------------------

[VratHimbo]

Thanks Paul,
sorry if I have forgotten some things.
The problem with my query is that some columns are "Number", not only "String" type.
The second question is, in your opinion, is better to do with some queries or with a multi decision, where I go to check if the empty is empty or not? And in case throw a business exception?
Thx

------------------------------
Vrat Himbo
------------------------------

[DaulatRam2]

Basically it will remain the same for empty columns

------------------------------
Daulat Ram
------------------------------

[VratHimbo]

I have this message when i try to perform OR [Column1] = ''

------------------------------
Vrat Himbo
------------------------------

[DaulatRam2]

Try str([Column 1] )=''

------------------------------
Daulat Ram
------------------------------

[VratHimbo]

I have a syntax error 😞

------------------------------
Vrat Himbo
------------------------------

[GopalBhaire]

Hi Vrat,

Have you tried with IS NULL like [Column1] IS NULL

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------