CREATING A IF STATEMENT INSIDE THE SQL QUERY IN IREPORT

Posted on July 28, 2016 at 11:31pm

Hello I am new in this,

I have a 2 sql select statement.

this is the first sql statement:

SELECT
     service_card.`no` AS service_card_no,
     customer.`customer_name` AS customer_customer_name,
     serial_numbers.`no` AS serial_numbers_no,
     items.`item_description` AS items_item_description,
     defect.`description` AS defect_description,
     service_card.`date_received` AS service_card_date_received,
     user.`first_name` AS user_first_name,
     user.`middle_name` AS user_middle_name,
     user.`last_name` AS user_last_name,
     service_card.`complaints` AS service_card_complaints,
     service_card.`store_name` AS service_card_store_name,
     service_card.`receipt_no` AS service_card_receipt_no,
     service_card.`date_purchased` AS service_card_date_purchased
FROM
     IF(service_card.customer)
     `customer` customer INNER JOIN `service_card` service_card ON customer.`id` = service_card.`customer_id`
     INNER JOIN `serial_numbers` serial_numbers ON service_card.`serial_no_id` = serial_numbers.`id`
     INNER JOIN `defect` defect ON service_card.`received_by` = defect.`id`
     INNER JOIN `user` user ON service_card.`received_by` = user.`id`
     INNER JOIN `items` items ON serial_numbers.`item_id` = items.`id`
WHERE
    service_card.id = $P{service_card_no}
 and service_card.`approved_by` IS NOT null
 
the second statement is this:
 
SELECT 
service_card.no as service_card_no,
service_card.customer_name as customer_customer_name,
items.item_description as items_item_description,
items.id AS serial_numbers_no,
service_card.complaints AS defect_description,
service_card.date_received as service_card_date_received,
user.first_name AS user_first_name,
user.middle_name AS user_middle_name,
user.last_name AS user_last_name,
service_card.complaints as service_card_complaints,
service_card.store_name as service_card_store_name,
service_card.receipt_no as service_card_receipt_no,
IFNULL(service_card.date_purchased,'') as service_card_date_purchased
FROM
service_card 
join `user` user on user.id = service_card.received_by
JOIN `items` items on service_card.item_id = items.id
where service_card.id=31
 
my question is how can I do the IF else statement inside the sql qeury of the ireport
 
any answers will be appreciated. thank you.
 
Eleazar Embuscado
 

 

ely78144's picture
ely78144
32
Joined: May 18 2016 - 10:08pm
Last seen: 4 years 7 months ago

3 Answers:

Posted on July 29, 2016 at 12:03am

Hi,
Try using Case when. The purpose would be the same.

Hope this helps,
KKriplani

kkriplani's picture
kkriplani
5056
Joined: Sep 4 2015 - 2:18am
Last seen: 11 months 2 weeks ago
Posted on August 1, 2016 at 4:01pm

Simplest way is using a case statement, but it can also be achieved by using a terinary statement. 

Try creating a parameter1 with 2 options. (SQL1 and SQL2).

Create another parameter2 which processes the first parameter with a java terinary statement as below.

$P{parameter1} == "SQL1" ? "paste your sql1 here without spaces/line breaks" : "paste your sql2 here without spaces/line breaks"

In the dataset and query editor, remove any existing SQL and paste $P!{parameter2}

Passing SQL1 into the parameter1 will pass the sql1, anything else will be evaluated to sql2.

 

reportdev's picture
reportdev
10564
Joined: Oct 12 2015 - 12:05pm
Last seen: 9 months 2 weeks ago
Posted on August 1, 2016 at 11:37pm

Yes, Case when being the simplest. 
The other option that the above answer gives is of "Conditional Parameter" stated as $!P{} which is quite efficient but I do not recommend the same in this situation for below mentioned reasons;
1) Case when is very simple
2) Conditional parameter increase the run time of the query compared to case when
3) May get complex and involves an additional parameter creation. however case when is operated upon a column value.

Moreover, Conditional parameter is genrally used in case of "where or From" clause in a query because if the conditional parameter is used in "Select" clause, the read fields from db would not work.

Hope this helps guys,
Regards,
KKriplani

kkriplani's picture
kkriplani
5056
Joined: Sep 4 2015 - 2:18am
Last seen: 11 months 2 weeks ago
Feedback