Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
-
create table for permissions, then build dynamic WHERE clause of query
Thank you philhege! I've created a second table as you mentioned already and my SQL is working correctly to allow user security. -
create table for permissions, then build dynamic WHERE clause of query
This is an example of my current permissions table. Username LName FName MI Password Roles ChangePassword Locked UnitsAllowed 108 Schneider William M. 108 Budget No No 6120,6121 I've got the validation and login... -
calculate a total for a column in a table
What would be the best way to achieve this? So far this is my code (but I am certainly not saying this is the best way to calculate a total); it is not calculating anything, just displaying a 0 (so it must be at least syntactically correct). <cfset ChargeTot=0> <cfif isdefined("NarAmt") or... -
SUM
Interestingly enough, I am not new to the SQL programming language. I am new to the DB2 syntax. When I tried adding a simple sum() function on a field, I get errors. Here it is....(I am having a hard time understanding the error handling that the ISeries returns.) [IBM][iSeries Access ODBC... -
YTD Criteria
I got it!! I added another % sign after my 5 in the criteria. -
YTD Criteria
I am sending this SQL to our ISeries via an ODBC connection through client Access. -
YTD Criteria
The above SQL is not working, I had to remove some parantheses. I have tried using WHERE as well as HAVING. Once I get the SQL to compile, neither keyword will return any records. Something is wrong with the criteria. -
YTD Criteria
I would tend to disagree. This forum doesn't give enough background info as to what I am trying to accomplish, as well as all the SQL changes I have made already to my code. I do need to GROUP BY my recordset, as well as set conditions on that aggregate. The date fields are alphanumeric... -
YTD Criteria
I am trying to figure out the CAST function. I get the following error: IBM][iSeries Access ODBC Driver][DB2 UDB]SQ20226 - The maximum number of stacked diagnostics areas has been exceeded. SQL = "SELECT BPKQCD as Unit, BPKRCD as Nature, BSBEVA as Budget, BPBAVA/100 as Actual, CAST(BPGDNB AS... -
YTD Criteria
Date fields are BPGDNB and BSNBCD. These are IFM files. Yuck! It was written in England and is NOT user friendly for programmers. Cast....hum.....that's a good thought. I will look into that. -
YTD Criteria
No records are being returned. Here is my SQL SELECT BPKQCD as Unit, BPKRCD as Nature, BSBEVA as Budget, BPBAVA/100 as Actual, BPGDNB, (BSBEVA-(BPBAVA/100))as Variance, AHADNA FROM YABPREP LEFT OUTER JOIN YABSREP ON BPKQCD = BSJ6CD AND BPKRCD = BSJ7CD and... -
YTD Criteria
I would like to write a SQL statement using DB2 SQL syntax. More specifically I need to date to be based on Year To Date criteria. Any ideas where I begin? My date field is alphanumeric. eek! -
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0199
Found it... DB2 UDB for iSeries SQL Programming Concepts V5R2 Simulating a Full Outer Join Like the left and right outer joins, a full outer join returns matching rows from both tables. However, a full outer join also returns non-matching rows from both tables; left and right. While DB2 UDB... -
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0199
thanks :) - seem to be great resources!!! do you know any particular place in this documentation that addresses JOINS? Can't seem to find it.... -
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0199
mercury2 - do you recommend any books or websites for this type of info? i am brand new to sending SQL to DB2. -
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0199
Mercury2 - I am still getting the same error... [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0199 - Keyword OUTER not expected. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE.] Is OUTER allowed? -
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0199
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0199 - Keyword OUTER not expected. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE. SQL = "SELECT BPKQCD, BPKRCD, BPGDNB, BPBAVA/100 as Balance, BSJ6CD, BSJ7CD, BSNBCD, BSBEVA FROM YABPREP FULL OUTER JOIN YABSREP ON... -
query of queries
I tried a UNION in a qoq... SELECT GetActual.BPKQCD, GetActual.BPKRCD, GetBudget.BSBEVA, GetActual.Balance, GetActual.BPGDNB FROM GetActual LEFT JOIN GetBudget ON GetActual.BPKQCD = GetBudget.BSJ6CD AND GetActual.BPKRCD = GetBudget.BSJ7CD UNION SELECT GetBudget.BSJ6CD, GetBudget.BSJ7CD...
