SQL Anywhere database - IF Expression...

Author

IF Expression...

Troy

2005-10-27, 7:41 am

Using ASA 8.0.2.

I'm trying to include an IF expression in the FROM portion of a Select
statement but it's giving me an error when trying to save it.

Is it possible to even have an IF in the FROM?

Here's my SQL:

SELECT "inventory"."inven_id",
"inventory"."inven_description",
"inventory"."inven_incat_id",
"inventory"."inven_loc_id_1",
"inventory"."inven_loc_desc_1",
"inventory"."inven_loc_id_2",
"inventory"."inven_loc_desc_2",
"inventory"."inven_loc_id_3",
"inventory"."inven_loc_desc_3",
"inventory"." inven_asssu_assoc_id
",
"inventory"."inven_invst_id",
"inventory"."inven_invtp_id",
"inventory"." inven_distinct_sales
",
"inventory"."inven_one_time",
"inventory"."inven_on_order",
"inventory"."inven_in_stock",
"inventory"."inven_special_order",
"inventory"." inven_average_core_c
harge",
"inventory"." inven_distinct_in_st
ock",
"inventory"."inven_cost_price",
"inventory"." inven_distinct_avera
ge_cost",
"inventory"."inven_loc_desc_4",
"inventory"."inven_loc_id_4",
"inventory"." inven_distinct_cost_
price",
"inventory"." inven_actual_cost_pr
ice",
"inventory"."inven_core_charge",
"inventory"."inven_invpr_grp_num",
inven_oemrt_id,
"associate"."assoc_name_1",
sysco_data_value_str
ing CL_USE_ACTUAL_COST
FROM "inventory",
IF :ls_oem_rt_type = 'oemrt' THEN "temp_report_oemrt" LEFT OUTER JOIN
"oem_rooftop_links" ON "temp_report_oemrt"."troem_oemrt_id" =
"oem_rooftop_links"."oemrt_id" ENDIF,
system_control,
"associate"
WHERE ( "inventory"." inven_asssu_assoc_id
" = "associate"."assoc_id" ) AND
( "inventory"."inven_oemrt_id" = "oem_rooftop_links"."oemrt_id" ) AND
( sysco_syscl_id = 411 ) AND
( sysco_oemrt_id = inven_oemrt_id )

Dmitri

2005-10-27, 7:41 am

Troy wrote:

> Using ASA 8.0.2.
>
> I'm trying to include an IF expression in the FROM portion of a Select
> statement but it's giving me an error when trying to save it.
>
> Is it possible to even have an IF in the FROM?

No.

Dmitri.

Pavel

2005-10-27, 9:23 am

I do not understand Dimitri...

regarding my experience, there are two IF's, IF statement
and IF expression.

Eat this: The IF expression CAN be used in SELECT statements
(and it's even very, very useful!!)

Also, since IF is also an expression, it can be used at wide
variety of places - in inserts, in setting variables,
concatenating strings, and so on.

The main difference between IF statement and IF expression
is in the ending, while IF statement ends with END IF, the
IF expression ends with ENDIF, be aware of that.

The very simplest example that floats in my mind right now
is here:

SELECT table_id,column_name
,(IF nulls='Y' THEN 'YES' ELSE
'NO' ENDIF) FROM syscolumn

Although brackets are not necessary in this example, when
using IF expression, I strongly recommend them.

I didn't take closer look at your code but I hope this
helps.

Have a nice time.
Pavel

> Troy wrote:
>
>
> No.
>
> Dmitri.

Dmitri

2005-10-27, 9:23 am

Pavel wrote:

> I do not understand Dimitri...

Mr. Troy wants to somehow include IF in the _FROM_ part of the SELECT
statement. I'm not aware of such a possibility (and can't imagine how
server should execute such a SELECT).

> Eat this: The IF expression CAN be used in SELECT statements
> (and it's even very, very useful!!)

Surely - in the select list, as your example illustrates.

Dmitri.

Breck Carter [TeamSybase]

2005-10-27, 9:23 am

On 27 Oct 2005 06:19:40 -0700, Dmitri <NOdimSPAM@mail15.com> wrote:

>Pavel wrote:
>
>
>Mr. Troy wants to somehow include IF in the _FROM_ part of the SELECT
>statement. I'm not aware of such a possibility (and can't imagine how
>server should execute such a SELECT).

Even if it *could* execute it, the posted example doesn't include an
ELSE clause so it's hard to imagine how references to missing tables
would be handled :)

Breck

>
>
>Surely - in the select list, as your example illustrates.
>
>Dmitri.

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Pavel

2005-10-27, 9:23 am

Yeah, my bad. I red the post in a brief manner (plus solved
one or two local cases between reading and posting an
answer) and so "brief" was my response.

Of course, there isn't even a logical way (related to
relational databases) how to compute IFs in FROM clause.

I still have much to learn :) The best to you all.
Pavel

> Pavel wrote:
>
>
> Mr. Troy wants to somehow include IF in the _FROM_ part of
> the SELECT statement. I'm not aware of such a possibility
> (and can't imagine how server should execute such a
> SELECT).
>
>
> Surely - in the select list, as your example illustrates.
>
> Dmitri.

Breck Carter [TeamSybase]

2005-10-27, 9:23 am

AFAIK you would have to code two separate queries and do the picking
on the client side; e.g., if this is PowerBuilder...

IF ls_oem_rt_type = 'oemrt' THEN
SELECT #1
ELSE
SELECT #2
END IF

You could also bury this in a stored procedure and do

SELECT ... FROM p_whatever ( :ls_oem_rt_type )

and have the procedure do the IF ELSE, but again, you still need to
code two selects inside the procedure. You can have a procedure call
in a FROM clause in ASA 9, although I have not tried that in
PowerScript... PowerScript can be as dumb as a sack of soup at times.

Note that SELECT #2 would have to omit the reference to
oem_rooftop_links in the WHERE clause, since that table would be
omitted from the FROM.

Breck

On 27 Oct 2005 03:46:43 -0700, "Troy" < coombst@NOSPAMquorum
is.com>
wrote:

>Using ASA 8.0.2.
>
>I'm trying to include an IF expression in the FROM portion of a Select
>statement but it's giving me an error when trying to save it.
>
>Is it possible to even have an IF in the FROM?
>
>Here's my SQL:
>
> SELECT "inventory"."inven_id",
> "inventory"."inven_description",
> "inventory"."inven_incat_id",
> "inventory"."inven_loc_id_1",
> "inventory"."inven_loc_desc_1",
> "inventory"."inven_loc_id_2",
> "inventory"."inven_loc_desc_2",
> "inventory"."inven_loc_id_3",
> "inventory"."inven_loc_desc_3",
> "inventory"." inven_asssu_assoc_id
",
> "inventory"."inven_invst_id",
> "inventory"."inven_invtp_id",
> "inventory"." inven_distinct_sales
",
> "inventory"."inven_one_time",
> "inventory"."inven_on_order",
> "inventory"."inven_in_stock",
> "inventory"."inven_special_order",
> "inventory"." inven_average_core_c
harge",
> "inventory"." inven_distinct_in_st
ock",
> "inventory"."inven_cost_price",
> "inventory"." inven_distinct_avera
ge_cost",
> "inventory"."inven_loc_desc_4",
> "inventory"."inven_loc_id_4",
> "inventory"." inven_distinct_cost_
price",
> "inventory"." inven_actual_cost_pr
ice",
> "inventory"."inven_core_charge",
> "inventory"."inven_invpr_grp_num",
> inven_oemrt_id,
> "associate"."assoc_name_1",
> sysco_data_value_str
ing CL_USE_ACTUAL_COST
> FROM "inventory",
> IF :ls_oem_rt_type = 'oemrt' THEN "temp_report_oemrt" LEFT OUTER JOIN
>"oem_rooftop_links" ON "temp_report_oemrt"."troem_oemrt_id" =
>"oem_rooftop_links"."oemrt_id" ENDIF,
> system_control,
> "associate"
> WHERE ( "inventory"." inven_asssu_assoc_id
" = "associate"."assoc_id" ) AND
> ( "inventory"."inven_oemrt_id" = "oem_rooftop_links"."oemrt_id" ) AND
> ( sysco_syscl_id = 411 ) AND
> ( sysco_oemrt_id = inven_oemrt_id )
>
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links