Hi All,
My sql is a little rusty, i ve been trying to do few things but still no luck. I m trying to query some data in one column based on certain . Here is my puzzle:
I have 7 tables: categories, characteristics, configs, rm_cat, rm_chars, rm_conf and rooms.
And here are the details on these tables:
- categories: {cat_id, cat_name}
- characteristics: {char_id, char}
- configs: {conf_id, conf}
- rm_cat: {room_id, cat_id}
- rm_chars: { room_id, char_id}
- rm_conf: {room_id, conf_id}
- room: {room_id. room_name}
I m trying to select a "room_name" based on a certain cat_id, char_id and conf_id and i don't know how to do this.
Sincerely,
Try this:
select room_name
from room
join rm_conf
on room.[room_id]= rm_conf.[room_id]
join configs
on rm_conf.[conf_id]= configs.[conf_id]
join rm_cat
on room.[room_id]= rm_cat.[room_id]
join rm_chars
on room.[room_id]= rm_chars.[room_id]
join categories
on categories.[cat_id]= rm_cat.[cat_id]
join characteristics
on rm_chars.[char_id]= charateristics.[char_id]
where categories.[cat_id]= @.Cat_ID
and characteristics.[char_id]= @.Char_ID
and configs.[conf_id]= @.Conf_ID
|||
Thanks mpateson ...
Could you please tell me why am i getting this error : "ORA - 01722: invalid number" when i try to apply this select statement in my gridview
<asp:GridViewID="GridView1"runat="server"AllowPaging="True"AllowSorting="True"
DataSourceID="search_result">
</asp:GridView>
<asp:SqlDataSourceID="search_result"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionStringR25 %>"
ProviderName="<%$ ConnectionStrings:ConnectionStringR25.ProviderName %>"SelectCommand="SELECT R25.ROOMS.ROOM_SHORT FROM R25.ROOMS INNER JOIN R25.RM_CONF ON R25.ROOMS.ROOM_ID = R25.RM_CONF.ROOM_ID INNER JOIN R25.CONFIGS ON R25.RM_CONF.CONF_ID = R25.CONFIGS.CONF_ID INNER JOIN R25.RM_CAT ON R25.ROOMS.ROOM_ID = R25.RM_CAT.ROOM_ID INNER JOIN R25.RM_CHARS ON R25.ROOMS.ROOM_ID = R25.RM_CHARS.ROOM_ID INNER JOIN R25.CATEGORIES ON R25.CATEGORIES.CAT_ID = R25.RM_CAT.CAT_ID INNER JOIN R25.CHARACTERISTICS ON R25.RM_CHARS.CHAR_ID = R25.CHARACTERISTICS.CHAR_ID WHERE (R25.CATEGORIES.CAT_ID = :CAT_ID) AND (R25.CHARACTERISTICS.CHAR_ID = :CHAR_ID) AND (R25.CONFIGS.CONF_ID = :CONF_ID)">
<SelectParameters>
<asp:ControlParameterControlID="RadioButtonList1"DefaultValue="NULL"Name="CAT_ID"
PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="CheckBoxList1"DefaultValue="NULL"Name="CHAR_ID"
PropertyName="SelectedValue"/>
<asp:ControlParameterControlID="DropDownList1"DefaultValue="NULL"Name="CONF_ID"
PropertyName="SelectedValue"/>
</SelectParameters>
</asp:SqlDataSource>
|||
I found this on google.
| ORA-01722: | invalid number |
| Cause: | The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates. |
| Action: | Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation. |
