one field from multirow lookup to multivalue field

[ray.wurlod]

And SystemReturnCode is less than zero, indicating that an error has occurred?

Please post ALL the text captured in the third argument, not just one field.

I suspect - but don't know without seeing the entire output, that the hashed file name does not exist in the VOC file. How, and where, did you create the hashed file?

Another possibility is that you've left off the trailing semi-colon that terminates the SQL query. In this case, UniVerse is prompting for it. Do make sure that it is present.

[ppalka]

And SystemReturnCode is less than zero, indicating that an error has occurred?

SystemReturnCode=1

Please post ALL the text captured in the third argument, not just one field.

All the result:
TEST #1
*******

Arg1 = CUSTOMER_HASH

Test completed.


Result = SQLcount ( * )

0

1 records listed.

How, and where, did you create the hashed file?

By loading data from Oracle table to UV stage.
Everything is OK for a query:
select count(*) from HOLDER_UV where ID=1;
It properly counts the rows, but for:
select count(*) from HOLDER_UV where PRODUCT_ACCOUNT_NUMBER='184306';
it always return 0 rows. ID is integer key column, PRODUCT_ACCOUNT_NUMBER is non-key varchar column

[kduke]

I would not process this with ODBC in a routine.

MyLookup(ID, PRODUCT_ACCOUNT_NUMBER)

Code: Select all

Ans = 0
open "HOLDER_UV" as HashFile else stop
read Rec from HashFile, ID then
   NoAcctNos = dcount(Rec<1>, @VM)
   for i=1 to NoAcctNos
      if Rec<1> = PRODUCT_ACCOUNT_NUMBER then
         Ans += 1
      end
   next i
end

This is much faster. The else stop needs to be changed but this is close enough.

[ppalka]

This is much faster. The else stop needs to be changed but this is close enough.

Thanks a lot. I will test it.
Why there is an ID as an argument? Is it neccesery? If so, what I should give as ID? I just want to count the number of rows where PRODUCT_ACCOUNT_NUMBER is equal to something.

[ppalka]

When I paste your code as routine I get the following warnings:
Compiling: Source = 'DSU_BP/DSU.MyLookup', Object = 'DSU_BP.O/DSU.MyLookup'
*
WARNING: Variable 'as' never assigned a value.
WARNING: Variable 'HashFile' never assigned a value.

??

[kduke]

Sorry, brain needs tea.

Code: Select all

open "whatever" to FilePointer else print "whatever"

You have a syuntax error. ID or some key value needs to be sent to this routine unless I do not understand your problem. I am assuming you have some key which is assigned mutiple PRODUCT_ACCOUNT_NUMBERs. Is this true?

[ppalka]

Sorry, brain needs tea.
You have a syuntax error. ID or some key value needs to be sent to this routine unless I do not understand your problem. I am assuming you have some key which is assigned mutiple PRODUCT_ACCOUNT_NUMBERs. Is this true?

No, the issue is that I have only PRODUCT_ACCOUNT_NUMBER and I need to count all rows with that number. I don't know the ID. So without ID it couldn't be count?

[ray.wurlod]

Get to a TCL prompt on the server or use the Administrator client's Command window and execute the query

Code: Select all

select count(*) from HOLDER_UV where PRODUCT_ACCOUNT_NUMBER='184306'; 

It is syntactically valid.
Post the result that you receive - all of it. What you posted is the column heading generated by COUNT(*) in the query - there should have been more lines.
So - probably - all you need to do is to parse the Result variable to get the count

Code: Select all

Oconv(Result,"MCN")

or to extract the appropriate field (line) from the result.
Once you've posted the result of executing the query manually, we will show you at least three ways to achieve that.

[kduke]

This is not even a multiple row lookup. It is custom SQL only.

[ppalka]

Code: Select all

select count(*) from HOLDER_UV where PRODUCT_ACCOUNT_NUMBER='184306'; 

But this query return 0, which is not a valid result. When I use the query:
select count(*) from HOLDER_UV where ID=1;
then it's working fine.
All result from executing the query I have posted a few posts before. Do you want to see something more?

[kduke]

Because you do not need quotes around a number. Why did you waste our time making this a lot more complicated than it should be.

[ppalka]

Because you do not need quotes around a number. Why did you waste our time making this a lot more complicated than it should be.

Cause it's not working. In this example there is a number, but in UV table PRODUCT_ACCOUNT_NUMBER column is a VARCHAR type. However thanks for taking your time. I will try to find other solution using slower aggregation with aggregator stage.

[ray.wurlod]

Please post the full output from the SQL query.

Please also post the full output from LIST DICT HOLDER_UV so that you and we can verify your assertion about the data type.

If you wish, please also post the output of

Code: Select all

SELECT COUNT(*) FROM HOLDER_UV WHERE PRODUCT_ACCOUNT_NUMBER = '184306' EXPLAIN;

so that we can check what the query optimizer is doing with your query. It may be that all we need to do is to disable optimization.

[ppalka]

OK.
I will post that after I came back from my summer holiday
Thanks once again.
I hope we will solve that problem finally.

Best regards,
Piotrek

[ppalka]

SQL query:
select count(1) from HOLDER_UV where PRODUCT_ACCOUNT_NUMBER='196948'

Output:
SQL+
count ( 1 )

0

1 records listed.
End of output.

Output of LIST DICT HOLDER_UV:
TEST #1
*******

Arg1 =

Test completed.


Result = DICT HOLDER_UV 10:22:07 05-09-06 Page 1

Type &
Field......... Field. Field........ Conversion.. Column......... Output Depth &
Name.......... Number Definition... Code........ Heading........ Format Assoc..

ID D 0 MD0 10R S
@ID D 0 HOLDER_UV 10R S
PRODUCT_ACCOUN D 1 20L S
T_NUMBER
CUSTOMER_ID D 2 20L S
@REVISE PH PRODUCT_ACCOU
NT_NUMBER
CUSTOMER_ID
@ PH ID.SUP ID
PRODUCT_ACCOU
NT_NUMBER
CUSTOMER_ID
@KEY PH ID


7 records listed.

Output of query:
select count(1) from HOLDER_UV where PRODUCT_ACCOUNT_NUMBER='196948' explain;

TEST #1
*******

Arg1 =

Test completed.


Result = Optimizing query block 0
Table restriction: HOLDER_UV.PRODUCT_ACCOUNT_NUMBER = '196948'

Driver source: HOLDER_UV
Access method: file scan

DataStage/SQL: Press any key to continue or 'Q' to quit
count ( 1 )

0

1 records listed.