one field from multirow lookup to multivalue field
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
SystemReturnCode=1ray.wurlod wrote:And SystemReturnCode is less than zero, indicating that an error has occurred?
All the result:Please post ALL the text captured in the third argument, not just one field.
TEST #1
*******
Arg1 = CUSTOMER_HASH
Test completed.
Result = SQLcount ( * )
0
1 records listed.
By loading data from Oracle table to UV stage.How, and where, did you create the hashed file?
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
I would not process this with ODBC in a routine.
MyLookup(ID, PRODUCT_ACCOUNT_NUMBER)
This is much faster. The else stop needs to be changed but this is close enough.
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
Mamu Kim
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?
Code: Select all
open "whatever" to FilePointer else print "whatever"
Mamu Kim
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?kduke wrote: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?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Get to a TCL prompt on the server or use the Administrator client's Command window and execute the query 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 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.
Code: Select all
select count(*) from HOLDER_UV where PRODUCT_ACCOUNT_NUMBER='184306';
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")
Once you've posted the result of executing the query manually, we will show you at least three ways to achieve that.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Code: Select all
select count(*) from HOLDER_UV where PRODUCT_ACCOUNT_NUMBER='184306';
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?
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.kduke wrote: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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 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.
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;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.