[sokg0093]

i am having a problem and want to retrieve distinct information froma database using optionbuttons.
I have a set of input boxes and lists which i use to build the SQL statements for searching.
So my problem is in building the correct search criteria. part of the code looks as follows ( and i cant paste the whole code cos its actually not neccesary and has over 10.000 lines of code) :

 


'***
' Builds the SQL statement for searching in this Mask
' First gets the Search Criteria from all the input boxes and lists
' Then executes the SQL statement
' Finally refreshes the datagrid
'***
Private Sub BuildSQLandSearch()
On Error GoTo ErrorHandle

 Dim Statement As String
 Dim SelectedItems As String
 Dim SQLAnd As String
 Dim JoinAnd As String
 Dim i As Long
 
 Statement = ""
 SQLAnd = ""
 JoinAnd = ""
 SelectedItems = ListBoxSel("FaultCodes.MSN", ACList)
 If SelectedItems <> "" Then Statement = SelectedItems: SQLAnd = " AND "
 
 SelectedItems = ListBoxSel("FaultCodes.PFR", PFRList)
 If SelectedItems <> "" Then Statement = Statement + SQLAnd + SelectedItems: SQLAnd = " AND "

 SelectedItems = ListBoxSel("FaultCodes.Emitter", EmitterList)
 If SelectedItems <> "" Then Statement = Statement + SQLAnd + SelectedItems: SQLAnd = " AND "
 
 SelectedItems = ListBoxSel("FaultCodes.Class", ClassList)
 If SelectedItems <> "" Then Statement = Statement + SQLAnd + SelectedItems: SQLAnd = " AND "

 SelectedItems = ListBoxSel("FaultCodes.FlightNumber", FlightNumberList)
 If SelectedItems <> "" Then Statement = Statement + SQLAnd + SelectedItems: SQLAnd = " AND "

 SelectedItems = ListBoxSel("FaultCodes.FlightPhase", FlightPhaseList)
 If SelectedItems <> "" Then Statement = Statement + SQLAnd + SelectedItems: SQLAnd = " AND "
 
 SelectedItems = BITEStatusCombo.List(BITEStatusCombo.ListIndex)
 If BITEStatusCombo.ListIndex = -1 Then SelectedItems = BITEStatusCombo.Text
 If BITEStatusCombo.ListIndex <> 0 Then Statement = Statement + SQLAnd + "BiteStatusTemp.BITEStatus LIKE '%" & SelectedItems & "%'": SQLAnd = " AND "

 SelectedItems = LimitCombo.List(LimitCombo.ListIndex)
 If LimitCombo.ListIndex = -1 Then SelectedItems = LimitCombo.Text
 If LimitCombo.ListIndex <> 0 Then Statement = Statement + SQLAnd + " ROWNUM <= " & SelectedItems: SQLAnd = " AND "

 If FMCText.Text <> "" Then Statement = Statement + SQLAnd + " (FaultCodes.FMC LIKE   '" & FMCText.Text & "') ": SQLAnd = " AND "
 If FaultCodeText.Text <> "" Then Statement = Statement + SQLAnd + " (FaultCodes.FaultCode LIKE   '" & FaultCodeText.Text & "') ": SQLAnd = " AND "
 If FaultMessageText.Text <> "" Then Statement = Statement + SQLAnd + " (FaultCodes.FaultMessage LIKE '" & FaultMessageText.Text & "')": SQLAnd = " AND "
 
 If (StartDateText.Text <> "") And (EndDateText.Text = "") Then Statement = Statement + SQLAnd + " ( (FaultCodes.DisappearanceDate >=   to_Date('" & StartDateText.Text & "','DD.MM.YYYY')) OR (FaultCodes.ApparitionDate >=   to_Date('" & StartDateText.Text & "','DD.MM.YYYY')) ) ": SQLAnd = " AND "
 If (EndDateText.Text <> "") And (StartDateText.Text = "") Then Statement = Statement + SQLAnd + " ( (FaultCodes.ApparitionDate <=   to_Date('" & EndDateText.Text & "','DD.MM.YYYY')) OR (FaultCodes.DisappearanceDate <=   to_Date('" & EndDateText.Text & "','DD.MM.YYYY')) )": SQLAnd = " AND "
 If (StartDateText.Text <> "") And (EndDateText.Text <> "") Then
			Statement = Statement + SQLAnd + "( (FaultCodes.ApparitionDate <=   to_Date('" & EndDateText.Text & "','DD.MM.YYYY')) AND (FaultCodes.DisappearanceDate >=   to_Date('" & StartDateText.Text & "','DD.MM.YYYY')) )"
			SQLAnd = " AND "
   
 
[b] If ShowSingleFC.Value = True Then Statement = Statement + SQLAnd + "select" + "( distinct FaultCodes.FaultCode )": SQLAnd = "AND"[/b]	 
 End If
 


If Statement <> "" Then
	JoinAnd = " AND "
End If

	Statement = " WHERE " & Statement
	'MsgBox "select " + SelectColums + " from system.PFR, system.BiteStatusTemp, system.10KPI " & Statement & JoinAnd & " PFR.FaultCode=BiteStatusTemp.FaultCode(+) AND PFR.FaultCode=10KPI.FaultCode(+) "
	Adodc1.RecordSource = "select " + SelectColums + " from system.FaultCodes, system.BiteStatusTemp, system.10KPI " & Statement & JoinAnd & " FaultCodes.FaultCode=BiteStatusTemp.FaultCode(+) AND FaultCodes.FaultCode=10KPI.FaultCode(+) "
	
	Adodc1.Refresh


	Call FormatDataGrid
	
   Exit Sub
   
ErrorHandle:
	MsgBox "Error: " + Err.Description
Resume Next

End Sub

now the line marked bold(or if thats not the case); the line which has the ShowSingleFC is the problem part because i am trying to use the optionbuttons to pull out distinct values from the DB.
Now if someone can help me with the correct SQL command to do so i would be very grateful.

ShowSingleFC is the name of the optionButton

This post has been edited by sokg0093: 10 August 2007 - 04:32 AM

[PsychoCoder]

A SELECT DISTINCT is done like this

SELECT DISTINCT (column_name) FROM Table_Name WHERE Some_Column = Some_Value

So you need to change

If ShowSingleFC.Value = True Then Statement = Statement + SQLAnd + "select" + "( distinct FaultCodes.FaultCode )": SQLAnd = "AND"

to

If ShowSingleFC.Value = True Then Statement = Statement + SQLAnd + "select distinct (FaultCodes.FaultCode )": SQLAnd = "AND"

As far as the rest of the statement, Im really not sure how you're building a valid query with that, but hats not the problem you listed

Happy Coding!

[sokg0093]

Actually thanx psycho coder for the help but it still seems not to work.
Well to xplain a little the code i just put sort of builds the queries and combines them whereby different tables in the oracle DB are joined to produce a result. then i have a command button(actually named Search) which then sort of call this function. the search function looks like this

'***
' Builds an SQL Search Statement for this Mask and executes it
'***
Private Sub Command2_Click()

	Me.MousePointer = MousePointerHourglass
	Command2.Enabled = False
		Call BuildSQLandSearch
	Command2.Enabled = True
	Me.MousePointer = MousePointerDefault

End Sub

so when that calls the BuildSQLandSearch function these queries are then sent to the DB.
What i want is distinct faultcodes from a FaultCodes table in the DB when I let the value of the ShowSingleFC optionButton be true otherwise i get many instances of the same faultcodes.

But the line of code sent doesnot seem to give me the distinct rows.

[PsychoCoder]

Well then its something else in your query as the SELECT DISTINCT is what you want, can you post the actual SQL statement that is created (not the VB code but the SQL statement it builds)

[sokg0093]

So where do i actually see the SQL statement that is built? am not sure about where to find that from.
because that would probably help me 2 if i could actually see the SQL statement built when i run the software. So where do i see that or did i get ur question wrong?

many thanx

sokg0093