SQL Query Statement 2

[comconrk]

I am writing SQL statements to accesss data from an AS400. They work very well, but there seems to be an upper limit of 256 characters for the SELECT statement.

Is there any way around that? Or am I doing something wrong?

Here is my statement.

"SELECT PRODUCT_CODE,sum(SOLD_ITEMS),sum(REVENUE_AMOUNT) FROM PPRODUCTS JOIN DREVENUE ON (PPRODUCT = PRODUCT_CODE AND DREVENUE.ORGANIZATION_CODE = PPRODUCTS.PORGCODE) WHERE PPRODUCTS.PPARENT='" & Product_Input & "' Group by product_code

 

[GGleason]

You might try an alias to shorten the code:

SELECT PRODUCT_CODE,sum(SOLD_ITEMS),sum(REVENUE_AMOUNT)
FROM PPRODUCTS AS tP JOIN DREVENUE AS tR ON (PPRODUCT = PRODUCT_CODE AND tR.ORGANIZATION_CODE = tP.PORGCODE)
WHERE tP.PPARENT='" & Product_Input & "' Group by product_code

hth,
GGleason

 

[mikej28621]

comconrk,

What is the error message and what method are you query the 400. I perform much longer querys and have never found a limit. There are limits on the timeslice that the 400 will allow to execute the query.

M

 

[comconrk]

Mike,

What happens is that when I add more than 256 characters to my Select statement, it doesn't return any information. As soon as I drop to 256 or less, I get the data I need.

I have not printed the error message, since I know that 256 is the magic number.

I wonder if it is a problem with VBA and not the AS400.

Sounds like you are very familiar with this. I would certainly appreciate any thoughts you have.

Concerning the connection. The AS400 administrator set me up using IBM iSeries Access, so I'm not certain what I have.

 

[mikej28621]

Very interesting, there could be a max SQL parameter on the 400 that I don't know about but it would not be the default setting. I just performed a query of 417 characters (incuding spaces) with no problems. I have done it on several AS400's for years.

2 things... could you post more of your code.

Try this. Go to a table with lots of fields and do a straight pull ex.

SELECT etc1, etc1, (continue to list fields till you get past 256 characters) from Bla

On the 400 you can also do STRQSL and paste your SQL statement and see if it runs natively. Or vice versa you can create your SQL there and copy it back to vb. Just remember to translate lib/table to lib.table.

M

 

[mikej28621]

Oops That was STRSQL....

M

 

[mikej28621]

comconrk,

any luck?

M

 

[comconrk]

Mike,

Sorry, I was out of the office yesterday and today we have a vendor coming in for an all day meeting.
I will try to test this during a break either today or 1st thing tomorrow morning.

Thanks very much for your help.

Ron

 

[comconrk]

Mike,

I tried the simple SELECT as you stated
1. When I was less that 256 characters it returned 80 records.
2. As soon as I exceeded 256 characters, it returned no records.

Here is my code

databasename = "drg;USID=R_KIPFER;PWD=RR787410"


querystring = _
"SELECT PRODUCT_CODE,sum(SOLD_ITEMS),sum(REVENUE_AMOUNT),REVENUE_YEAR_MONTH FROM PPRODUCTS AS tP JOIN DREVENUE as tR ON (PPRODUCT = PRODUCT_CODE AND tR.ORGANIZATION_CODE = tP.PORGCODE) WHERE tP.PPARENT='" & Product_Input & "' Group by product_code,REVENUE_YEAR_MONTH"



returnArray = SQLRequest("DSN=" & databasename, _
querystring, , 2, True)
On Error Resume Next
Err.Clear
For i = LBound(returnArray, 1) + 1 To UBound(returnArray, 1)
' Mike...I deleted the code in here for ease of reading.

Next i


Thanks very much for your help.

Ron

 

[mikej28621]

Ron,

Sounds like no matches to your query.

Did you get an error or just no records?

Did you go to the 400 and do a STRSQL and paste your query
there. That will tell you. Any query that returns an answer there should work from ODBC.

M

 

[comconrk]

Mike,

I will check with the AS400 people which are 1200 miles from us. Unfortunately, I can not get onto the AS400 itself.

The thing that is confusing to me is that I do get matches to the query. Sometimes all I do is add the table name prior to the field name, just to get more characters for testing. Then I get no records at all.

I will see that the AS400 people say, but it still seems like what I am doing is limited. Do you use the SQLREQUEST function, or is there a better one to use? It sounds like you have a lot of good experience in this.

Comcon

 

[mikej28621]

Cha Ching.....

Ron checkout

http://support.microsoft.com/default.aspx?scid=kb;en-us;124218

Sample 8: Using Long SQL Query Strings NOTE: There is a maximum limit of 255 characters in a SQL query string. You may also receive GPFs if the length exceeds 127 characters. Use the method below to workaround these issues. If you use a long SQL query, you can choose to pass the SQL query to SQLRetrieve or SQLRequest as a Variant data type rather than a String data type. The following example demonstrates how this can be done.

No, I use full DSN-Less ODBC Connection.

see

http://www-1.ibm.com/support/docview.wss?uid=nas1c52dfe451b37518086256931005499ff

and

http://www.w3schools.com/ado/

for examples.

It was the SQLREQUEST that got you...

M

 

[mikej28621]

Ron,

Here is one of my own examples....

Dim cn As New ADODB.Connection
Dim cnstr As String
Dim CurrLib, ipadd As String

ipadd = "192.168.1.1;"
CurrLib = "lib1"

cnstr = cnstr & "DRIVER=Client Access ODBC Driver (32-bit);"
cnstr = cnstr & "PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;"
cnstr = cnstr & "LanguageID = ENU;"
cnstr = cnstr & "DFTPKGLIB = QGPL;"
cnstr = cnstr & "DBQ=qgpl," & CurrLib & ";"
cnstr = cnstr & "SYSTEM=" & ipadd & ";"

cn.Open cnstr
cn.CursorLocation = adUseClient

Set adoprimaryrs = New Recordset
Set adosecrs = New Recordset

sqlstr = "Select B3GLAC, B2TRDT, B1VND#, B2DESC, B1VNAM, B3TITM+B3TFGT+B3TTAX-B3TDSC as Test, B1PON#, B1INV#, B3REF#, B3GLPX"
sqlstr = sqlstr & " FROM " & CurrLib & ".b3billl1"
sqlstr = sqlstr & " INNER JOIN " & CurrLib & ".b2billl3 on"
sqlstr = sqlstr & " B3REF#=B2REF# and B3SEQ#=B2SEQ#"
sqlstr = sqlstr & " INNER JOIN " & CurrLib & ".b1billl0 on"
sqlstr = sqlstr & " B2REF# = B1REF#"
sqlstr = sqlstr & " where B2TRDT >= '" & strFromDate & "'"

'Debug.Print sqlstr
Set adoprimaryrs = cn.Execute(sqlstr)

primaryreccount = adoprimaryrs.RecordCount
'Debug.Print primaryreccount

'put the records on the sheet
Sheet1.Range("A" & Trim(Str(RowIndex))).CopyFromRecordset adoprimaryrs

Good Luck

M

 

[comconrk]

Mike,

I have never used ADO, but it looks like it will give me what I need. I am having trouble executing the code.

I have looked through the websites you referred me to and also in the Microsoft Knowledge Base. They have showed me that I needed to add the following references to Excel.

Microsoft DAO 3.6 Object Library
Microsoft Data Object Components Library
Microsoft ActiveX Data Objects 2.7 Library
Microsoft ActiveX Data Objects Recordset 2.7 Libary

However, it still tells me that I can not use the New property in the Dim statement. Obviously, I have missed at least one reference to add. Could you tell me what it is?

Thanks for your help as DAO seems to be quite powerful. Once I understand it better, it will be a very good tool.

Comcon

 

[mikej28621]

All I have are

Visual Basic for applications
microsoft excel 10.0 object library
ole automation
microsoft office 10.0 object library
microsoft forms 2.0 object library
microsoft ActiveX Data Objects. 2.0 library

These are fine unless you distribute to people with older versions of Excel.
Microsoft ActiveX Data Objects 2.7 Library
Microsoft ActiveX Data Objects Recordset 2.7 Libary

M

 

[comconrk]

I do have all those at this time, but I am still getting the error on the New property of the DIM statement.
"Invalid use of NEW Keyword"

I have to work on another project at this time, but I will get back to this. I certainly appreciate all your help.

C