if else logic in sql select query

Hi All

I have a sqlserver database with product, catagory and sub catagory format. Before I describe my problem, let me share whats I have in db. Their are two types of sinaros, either the products are directly assigned to a catagory or a product is placed in subcatagory that is in turn have a catagory. I use the following table struct for both of the scenarios:

Product>>subcat bridge>>subcatagory

Product>>catbridge>>catagory

Here are the queries to get them:

1. If product assinged direct in catagory then

select product.pid, product.Prd_heading,product.[Description], product.Brand, product.img from product,cat_bridge,category where product.id=cat_bridge.pid and cat_bridge.catid=category.catid;

2. If product assinged to sub cat then

select product.pid, product.Prd_heading,product.[Description], product.Brand, product.img from product,subcat_bridge,subcategory where product.pid=subcat_bridge.pid and subcat_bridge.subcatid=subcategory.subcatid and subcategory.catid=category.catid;

Now the problem is, I want to use a single query to download all the products to a CSV format and I need to combine both of the queries with a single one, probably with if else logic, but I am not getting it, I mean how to acheive. Can anyone help me sort this out?

Thanks in Advance

Regards

Mykhan

Here's one nice way to do it using a UNION ALL (note that I'm adding a column, 'COND', that will tell you which condition you're in). Note also the use of UNION ALL vs UNION which you can read about herehttp://www.fmsinc.com/free/NewTips/SQL/SQLtip5.asp

Select 'CASE1' as COND, product.pid, product.Prd_heading,product.[Description], product.Brand, product.img
from product,
cat_bridge,
category
where product.id=cat_bridge.pid and cat_bridge.catid=category.catid;

UNION ALL

select 'CASE2' as COND, product.pid, product.Prd_heading,product.[Description], product.Brand, product.img
from product,
subcat_bridge,
subcategory
where product.pid=subcat_bridge.pid and subcat_bridge.subcatid=subcategory.subcatid and subcategory.catid=category.catid;

|||

Sorry Double Post

|||

dbland07666

Thats a very useful example, I didnt knew about Union All and Union before, it might come handy in other places as well, but I have two problems with your solution, which I am not getting

First, Its not working and I am getting the following error:

Incorrect syntax near the keyword 'UNION'. And I am confused whether Union ALL works with SQL 2000 or not.

And secondly, Can you elaborate of the 'case1' as COND a little more, I didnt get it

Sorry to bother you, if you can help, I will be really thankful.

Regards

Mykhan

|||

Sorry -- cut and paste errors on my part. Take out the semicolons, ie:

Select 'CASE1' as COND, product.pid, product.Prd_heading,product.[Description], product.Brand, product.img
from product,
cat_bridge,
category
where product.id=cat_bridge.pid and cat_bridge.catid=category.catid

UNION ALL

select 'CASE2' as COND, product.pid, product.Prd_heading,product.[Description], product.Brand, product.img
from product,
subcat_bridge,
subcategory
where product.pid=subcat_bridge.pid and subcat_bridge.subcatid=subcategory.subcatid and subcategory.catid=category.catid

Re the COND column, what I mean is that if you're returning the data into some kind of collection, you can test on COND if you need to know whether you were able to link directly to category or had to go through subcategory

|||

Sorry -- cut and paste errors on my part. Take out the semicolons

And I was foolish enough not to spot that minor mistake

Anyway thanks a lot, that helped.

Regards

Mykhan

sql

if cross tab report goes beyond one page?

Hello friends,

I designed a report that shows sales of products datewise -

product 1/12 2/12 3/12 4/12 ...
------------------
abc 100 93 94 109 ...
xyz 7 100 56 98 ...
...
...

if i select 2 or 3 months then it shows 90 columns (per day one column) and i can view the report on screen in correct format. But when i send print, it prints product names and some 18 columns only on first page and on each consecutive pages it shows remaining date columns only (with data) and that is also in cut format (half column on previous page and half column on next page)

In a nut shell, is there any setting so that cross tab will work like EXCEL sheet of fixed row / fixed column facility so that on each new page it should print the fixed column.

Since title row is printing on each new page.

Pls advice.

Thanks in adv.
KSee if you find solution here
http://support.businessobjects.com/|||Hi kirankul,

If u found the solution please update this...

Thanks
Sanju|||Hi All,

I encounter the same problem and found the solution, I am not sure whetehr this solution is really suitable for your case, but I think it will.

You go to Cross-Tab expert by right click the cross tab that you have created then go to Customised style tab --> go to the grid option --> check the keep columns together and check the repeat row labels option

By choosing these options, the cross tab that you printed will have the row header name and column header names at every pages.

Cheers
CS