I need help writing a program that shows a specific numbers…there are 2 scenarios and I cant use an IF statement.
Any suggestions?
can you use a switch?
or you can simulate an if using the conditions of loops:
eg:
while(condition1 = true)
{
break;
}
while(condition 2 = true)
{
break;
}
Assume you have a table with two columns "Name" and "State".
Names can repeat in the table with different states. For example:
Name/State
Bob/NY
Bob/PA
Bob/VA
I want to write a SQL statement (without the use of cursors) that will return "Bob NY, PA, VA" on a single line.
Does anyone know of a way to do this in a straight SQL Statement?
Alternatively, any good tricks for accomplishing this using the functionality of Microsoft Access?
MySQL has GROUP_CONCAT, in MSSQL you can write a UDF that does some neat tricks.
In Access, you'd probably have to use VBA. The function that follows is a generic, and as such, suffers from a complete lack of optimization. If you're writing it to fill a special case, you can use static variables and collections to only have to look up data once. This function will make a request of the database for every row in the query (and sometimes more often–Access often runs code functions multiple times per row). It also assumes that the data coming in and the data coming out is textual.
'—————-
Function Group_Concat(Table As String, KeyField As String, KeyValue As Variant, ValueField As String, Optional Separator As String = ", ") As String
Dim sSQL As String
Dim rs As New ADODB.Recordset
Dim sResults() As String, iResultPosition As Integer
sSQL = "SELECT [" & ValueField & "] FROM [" & Table & "] WHERE [" & KeyField & "] "
If IsNull(KeyValue) Then
sSQL = sSQL & " Is Null"
Else
' Make sure we don't accidentally feed in SQL-breaking (or possibly SQL-injecting) quotes
' This makes it so a single quote character sent in is turned into a single-character wildcard
sSQL = sSQL & " Like '" & Replace(KeyValue, "'", "_") & "'"
End If
' Don't return Null values
sSQL = sSQL & " AND [" & ValueField & "] Is Not Null"
' Assume you want the data sorted
sSQL = sSQL & " ORDER BY [" & ValueField & "]"
' Nod toward optimization: By using a Static (or Keyset) recordset, we can find out how many rows
' are being returned, and use an array to store the contents of the row. VB bogs down heavily
' under string concatenation. However, if you're only expecting 3 or 4 results per request
' the array may be more overhead.
On Error Resume Next
rs.Open sSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
If Err.Number <> 0 Then
' Catch errors and return them as the result
Group_Concat = "#ERROR#: (" & CStr(Err.Number) & ") " & Err.Description
Exit Function
End If
On Error GoTo 0
If rs.RecordCount = 0 Then
rs.Close
Exit Function
End If
ReDim sResults(0 To rs.RecordCount - 1)
Do Until rs.EOF
sResults(iResultPosition) = rs.Fields(ValueField).Value
iResultPosition = iResultPosition + 1
rs.MoveNext
Loop
rs.Close
Group_Concat = Join(sResults, Separator)
End Function
'—————–
Sample SQL, assuming a table called tblNamesStates with two columns: Name, and State
SELECT
Name
, Group_Concat( "tblNamesStates", "Name", [Name], "State" )
FROM tblNamesStates
GROUP BY Name
Hope that helps.
I want to get the number of rows returned (Select count * from [table] where employeeNo = ?). I just want the integer which will be returned, using java programming. I know how to do resultset etc if it is a regular sql statement but how should I capture just the count result?
Thanks
Try "count(*)" instead of "count *". Then just getInt(1) from your ResultSet.
I have the following HTML:
<input type="button" onclick=askPermissionToDelete() name="deleteCust" value="Delete">
On the same form, I have some php code that displays records with a radio button beside it. When I have a radio button selected, and press the Delete button, I want to delete that record. So how can I call my javascript function, ask the user permission to delete, and than delete the record and refresh the page. Please help!!!
Never done AJAX before…is there any sample code I could go by?
Another thing you can do is confirm() without the AJAX. Get to know the basics before you start getting into things like AJAX, which might require a rewrite of some of your existing code. Follow the link in the source to see an example of what you can do.
I have been trying to figure out the correct sytnax for this for ages and was wondering if anyone could help me out ? The statement works fine, without the where clause, unfortunately I need that where clause to make the statement usefull at a few points in my application.
update tableName
SET value=CASE
WHEN value+1>100 THEN 100
ELSE value+1
WHERE value_enabled<>0
END;
update tableName
SET value=CASE
WHEN value+1>100 THEN 100
ELSE value+1 END
WHERE value_enabled<>0;
COA Table (Fields: ACode, Title, Type)
AP Table (Fields: ACode, Debit, Credit, Date)
Example:
COA: 1, Cash In Bank, Bank Statement
2, Labor Cost, Income Statement
and so on
AP: 1, 0,250, Jan 1
2,300,0,Jan 5
I need to list down the COA with corresponding SUM of Debit/Credit and Union the COA with with corresponding SUM of Debit/Credit starting frm Jan 1 to date.
need help, thanks
The short answer is yes. The longer answer is that your examples make little sense.
But, doing a UNION query between your two result sets is doable in most SQLs (definitely T-SQL), you just have to make sure the result sets you are UNIONing have the same columns.
From a balance sheet financer is always prepare this statement, why should we prepare for that? What exactly we come to know from this statement.
Cash Flow Statement : Statement showing changes in inflow & outflow of cash during the period.
Methods of cash flow:
1.Direct Method : presenting information in Statement of
A. operating Activities
B. Investment Activities
C.Financial Activities
2.Indirect Method :uses net income as base & make
adjustments to that income(cash & non-cash)transactions.
Funds Flow Statement :Statement showing the source &
application of funds during the period.
Major Difference:
The Cash Flow S tatement allows investors to understand how a company’s operations are running, where its money is coming from, and how it is being spent.
Fund Flow Statement is showing the fund for the future activites of the Company.
Have finally finished my personal statement that's part of my application for the college I'm trying to get into. My question is, how do I tie it together? Do I write "Personal Statement" across the top? Or include my name on it? Or say "to whom it may concern?" Or do I just leave it as a 5 paragraph statement with no other words on it?
I would definitely put something on it to identify it. Name, "personal statement", date, name of college, etc.
What is the corniest corporate mission statement you can come up with?
I'll start:
"Helping the people of the world to make it a
better place for the children and our children's
children."
"Our goal is to give the weak and the disenfranchised a chance to grow and prosper. We hope to make life a sunshine day for those in any and every community."
Does anyone know of any good books on how to analyse financial statements? Also, how do I know when to invest?
This is a great book on how to really read financial statement:
Financial Statement Analysis: A Practitioner's Guide
http://www.amazon.com/gp/product/0471409154?ie=UTF8&tag=stoinvmadeas-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0471409154
I have it in my financial book collection, hopes it can do good for you too!