Error In Row

[LostAngel]

Hello, I am writting a visual basic program that connects to a progress database (db not written by me). I have the following query that works perfectly:

SELECT DISTINCT pub.sol."Item-code", pub.sol."Rev-code", pub.itm."pieces-per-ctn", pub.iix."Item-desc-1", pub.sol."Customer" 
FROM pub.sol, pub.itm, pub.iix, pub.cus, pub.sox 
WHERE pub.sol."item-code" = pub.itm."item-code" AND 
(pub.sol."Seq-Pre" = 'SO' OR pub.sol."seq-Pre" = 'RL')  AND
pub.iix."Item-code" = pub.itm."Item-code" AND 
pub.sol."Customer" = pub.cus."Customer" AND 
pub.sol."Seq-pre" = pub.sox."Seq-pre" AND 
pub.sol."Seq-num" = pub.sox."Seq-num" AND 
pub.sol."Ship-flg" <> 'S' AND 
pub.sol."Ship-flg" <> 'P' AND 
pub.sol."item-code" <> 'SU' AND 
pub.sol."item-code" <> 'CO' AND 
pub.sol."Item-code" = pub.itm."Item-code" AND 
pub.sol."Item-code" = pub.iix."Item-code" AND 
LEFT(pub.sol."Qty-ord", InStr(pub.sol."Qty-ord", ';')-1) <> 0 ORDER BY pub.sol."Seq-pre", pub.sol."Seq-num", pub.sol."So-line"

Now, when I modify the sql statement to include one more criteria for the WHERE statement, I get an 'Error in Rows' message back. Below is the new SQL statement, bolded is the new line:

SELECT DISTINCT pub.sol."Item-code", pub.sol."Rev-code", pub.itm."pieces-per-ctn", pub.iix."Item-desc-1", pub.sol."Customer" 
FROM pub.sol, pub.itm, pub.iix, pub.cus, pub.sox 
WHERE pub.sol."item-code" = pub.itm."item-code" AND 
(pub.sol."Seq-Pre" = 'SO' OR pub.sol."seq-Pre" = 'RL')  AND
pub.iix."Item-code" = pub.itm."Item-code" AND 
pub.sol."Customer" = pub.cus."Customer" AND 
pub.sol."Seq-pre" = pub.sox."Seq-pre" AND 
pub.sol."Seq-num" = pub.sox."Seq-num" AND 
pub.sol."Ship-flg" <> 'S' AND 
pub.sol."Ship-flg" <> 'P' AND 
pub.sol."item-code" <> 'SU' AND 
pub.sol."item-code" <> 'CO' AND 
pub.sol."Item-code" = pub.itm."Item-code" AND 
pub.sol."Item-code" = pub.iix."Item-code" AND 
[B]pub.sol."Customer" = '7' AND[/B]
LEFT(pub.sol."Qty-ord", InStr(pub.sol."Qty-ord", ';')-1) <> 0 ORDER BY pub.sol."Seq-pre", pub.sol."Seq-num", pub.sol."So-line"

I know that there are records where Customer = '7'....and whenever I try any other customer numbers, the same error happens (though I know they exist...).

 

[Lee Curzon]

I don't know much about this VB/SQL stuff, but as 7 is an integer, and presumably the statement you've posted is a string to be evaluated at run time, try removing the quotes around the 7.

 

[LostAngel]

That gives a syntax error...it's wierd that that one line would cause problems...

 

[Lee Curzon]

I know that there are records where Customer = '7'....and whenever I try any other customer numbers, the same error happens (though I know they exist...).

Presumably, you mean you know there are records that satisfy the entire Clause (including Customer = 7)?

Can you run the query against the database successfuly, without piping it through VB?

 

[Lee Curzon]

Hold on a minute - you've got two 'Customer = ' lines!

They will be mutually exclusive, returning a null set.

 

[Lee Curzon]

returning a null set.

(Or, more properly, an error).

 

[Lee Curzon]

I am intrigued by this clip though:

: AND
pub.sol."Item-code" = pub.itm."Item-code" AND
pub.sol."Item-code" = pub.iix."Item-code"
:

 

[LostAngel]

Hmm...I took out the customer = customer line...and it works!

 

[Lee Curzon]

In case you don't realise,

your SELECT statement didn't make sense with (eg.):

Customer = <value1> AND
Customer = <value2>

An object (eg. a field in a table) cannot exist in two different states at the same time, unless it's a cat in a box in a weird physics experiment.

eg (pseudocode):

SELECT Orders WHERE
Customer = 'Fred' AND
Customer = 'Dave'.

is illogical, because the Customer field will have one value only.

You might use

SELECT Orders WHERE
Customer = 'Fred' OR
Customer = 'Dave'.

instead, if you wanted to show orders for both customers.

 

[LostAngel]

In case you don't realise,

your SELECT statement didn't make sense with (eg.):

Customer = <value1> AND
Customer = <value2>

An object (eg. a field in a table) cannot exist in two different states at the same time, unless it's a cat in a box in a weird physics experiment.

eg (pseudocode):

SELECT Orders WHERE
Customer = 'Fred' AND
Customer = 'Dave'.

is illogical, because the Customer field will have one value only.

You might use

SELECT Orders WHERE
Customer = 'Fred' OR
Customer = 'Dave'.

instead, if you wanted to show orders for both customers.

Thanks, I'll re-write my query.