VB.Net - Select from mutiple table

[charlotte]

hi, help needed!

below are my coding, i have a datagrid.
and i want the records display in the datagrid are select from two table.

 Dim SQL_Cat, SQL_Dept, SQL_Yr, SQL_Cat1, SQL_Dept1, SQL_Yr1 As String 
 
' For ESC_APPLICANT
If ddlCat.SelectedItem.Text <> "" Then
SQL_Cat = "And APP_CAT LIKE '%" & ddlCat.SelectedItem.Text & "%'"
 
Else
 
SQL_Cat = ""
 
End If
 
If ddlReqDept.SelectedItem.Text <> "" Then
 
SQL_Dept = "And STFSEC LIKE '" & ddlReqDept.SelectedItem.Text & "%'"
 
Else
 
SQL_Dept = ""
 
End If
 
If ddlYear.SelectedItem.Text <> "" Then
 
SQL_Yr = "And TO_CHAR(ISSUE_DATE, 'yyyy')= '" & ddlYear.SelectedItem.Text & "'"
 
Else
 
SQL_Yr = ""
 
End If
 
 
' For ESC_LOSS
 
If ddlCat.SelectedItem.Text <> "" Then
 
SQL_Cat1 = "And b.APP_CAT LIKE '%" & ddlCat.SelectedItem.Text & "%'"
 
Else
 
SQL_Cat1 = ""
 
End If
 
If ddlReqDept.SelectedItem.Text <> "" Then
 
SQL_Dept1 = "And b.STFSEC LIKE '" & ddlReqDept.SelectedItem.Text & "%'"
 
Else
 
SQL_Dept1 = ""
 
End If
 
If ddlYear.SelectedItem.Text <> "" Then
 
SQL_Yr1 = "And TO_CHAR(b.ISSUE_DATE, 'yyyy')= '" & ddlYear.SelectedItem.Text & "'"
 
Else
 
SQL_Yr1 = ""
 
End If
 
Dim SQL As String = " SELECT a.APP_CAT, a.STFSEC, a.ISSUE_DATE, b.APP_CAT, b.STFSEC, b.ISSUE_DATE FROM ESC_APPLICANT a, ESC_LOSS b WHERE (a.STATUS = 'APPROVE' OR a.STATUS = 'RENEW' OR a.STATUS = 'RETURN')" + SQL_Cat + SQL_Dept + SQL_Yr + "OR b.STATUS = 'LOSS' " + SQL_Cat1 + SQL_Dept1 + SQL_Yr1

i try to run the code above but it give an error:-

One or more errors occurred during processing of command. ORA-00960: ambiguous column naming in select list

[font=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif]Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: One or more errors occurred during processing of command. ORA-00960: ambiguous column naming in select list[/font]


is it something wrong with my select statement?
thanks for reply!


regards;
charlotte

 

[jmcilhinney]

You are selecting columns from more than one table with the same name without using aliases. As an example, you are returning a.APP_CAT and b.APP_CAT. The table name does not form part of the column name in the result set so you are trying to create two columns called APP_CAT. You need to use aliases to overcome the ambiguity:

SELECT a.APP_CAT AS APP_CAT_A, b.APP_CAT AS APP_CAT_B, ...

 

[charlotte]

thanks, jmcilhinney.

if i write a.APP_CAT As APP_CAT_A, b.APP_CAT As APP_CAT_B

then my datagrid will become:-
APP_CAT_A ssss APP_CAT_B
TRAINEE sssssssssssTRAINEE
STAFF sssssssssssssSTAFF


but i want my datagrid to be:-
APP_CAT
TRAINEE
STAFF


So how?
actually what i want to do is select from two table the same items and display in one datagrid only.
(p/s: even i try the method above, it still give me the same error!)
anyway thanks for the help!


regards
charlotte

 

[jmcilhinney]

If you only want one APP_CAT column, particularly if they are related and are actually the same value, then only return one of the columns with your select statement. If you want both columns then you'll have to alias at least one of them. Note also that APP_CAT is not the only column name that is duplicated.

 

[kulrom]

SQL SELECT DISTINCT function will do the job ... it preserves duplicated values ... Cheers

 

[jmcilhinney]

SQL SELECT DISTINCT function will do the job ... it preserves duplicated values ... Cheers

Doesn't DISTINCT cause a select statement to return a single row when there are duplicates? If this is correct, I'm not sure how that is applicable in this case.

 

[levyuk]

Yes distinct returns a single row.