adding some rows to a select

Hi folks,

I've a sql query problem I was wondering if you all had a quick and
dirty solution for. I've a query:

Select code, value from table_a where date in
(2004) and a_code in ('1000','2000') and b_code in ('01000','02000')

This returns a table that looks like:

A_CODE B_CODE VALUE
-- -- --

1000 01000 $500
1000 02000 $750

What I'd like to see is:

A_CODE B_CODE VALUE
-- -- --

1000 01000 $500
1000 02000 $750
2000 01000 $0
2000 02000 $0

Any suggestions on how to rewrite my query so the results show A_CODE
2000 with a VALUE of 0 or null?

Thank much in advance!

MarcMarc (brownjenkn@.aol.com) writes:
> I've a sql query problem I was wondering if you all had a quick and
> dirty solution for. I've a query:
> Select code, value from table_a where date in
> (2004) and a_code in ('1000','2000') and b_code in ('01000','02000')
> This returns a table that looks like:
> A_CODE B_CODE VALUE
> -- -- --
> 1000 01000 $500
> 1000 02000 $750
> What I'd like to see is:
> A_CODE B_CODE VALUE
> -- -- --
> 1000 01000 $500
> 1000 02000 $750
> 2000 01000 $0
> 2000 02000 $0
> Any suggestions on how to rewrite my query so the results show A_CODE
> 2000 with a VALUE of 0 or null?

CREATE TABLE a_code (a_code char(4) NOT NULL
CREATE TABLE b_code (b_code char(5) NOT NULL

go
INSERT a_code (a_code) VALUES ('1000')
INSERT a_code (a_code) VALUES ('2000')
INSERT b_code (b_code) VALUES ('01000')
INSERT b_code (b_code) VALUES ('02000')
go
SELECT a.a_code, b.b_code, coalesce(t.value, 0)
FROM (a_code a
CROSS JOIN b_code b)
LEFT JOIN table_a t ON a.a_code = t.a_code
AND b.b_code = t.b_code
ABD t.date = '2004'

Here I am handling a_code and b_code in the same way, so you will
get output for missing b_codes as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Adding Misc files to Solution Explorer

I assume this is the best forum for this quesiton; if not, please direct me.

I have noticed that I can add (for example) a text file to a misc folder in a SQL Server project by dragging and dropping the file from Windows Explorer onto the Misc folder inside the SQL Server project. Is this the only way to add a file?

I noticed, for example, I could not copy and past to the Misc file folder.
Is there another way? If so, what is it? what is the preferred way?

I noticed that there have bee 55 views of this thread. It was posted Monday morning. would someone take a stab at it?|||The other way to add a file is to right click on the Project, select Add Existing Item, then Browse to the file in question (you need to change the File Type at the bottom to "All" in order to see text files, etc.).

Thanks, MJ

Adding folders under the solution

In RS, can one add a folder under the Reports folder? I don't see where I can do this. We work with many different state's and we would like to organize reports by state.

Thanks for the information.

hi,

Yes, you can. You can do it on the Report Server website (eg: http://reporservername/reports) clicking on the new folder. Then you can set up explicit security on it, if you do not want to have the inherited.

Also you can set up in the VS2005 SSRS project the destination folder in the project properties. It used when you deploy reports in a project. If the folder does not exist, it will create it and inherits the permission from the parent.

Regards,

Janos

|||

What I guess I was hoping to be able to do is to right click in the solution and Add a folder, like I can in a web project. We also use Source Safe and would like a similar directory structure there too. We would like to create one solution with the reports, however each report might vary because of different state requirements. From there, we would like a directory structure of Reports/StateName/thisreport.rdl.

Each state then get a copy of their own reports when the program as a whole is delivered to them. They have no need to see other state reports.

We are trying not to create a new Solution for each state because there are reports that are generic and can be created just once.

|||

You can create your folder structure in a solution. Each folder must have its own project below the solution.

eg.> I'd like a folder structure like this:

\

--US

|

-CA

-WA

Then I create an SSRS solution, then I add 3 projects. 1st project property page set the TargetReportFolder to /US, 2nd /US/CA, 3rd /US/WA.... etc.

I hope it helps.

regrads,

Janos

|||I'm also trying to create a folder structure inside the report project itself, to no avail. I think what guyinkalamazoo3 was really going for is the ability to create folders inside the single report project to keep things organized, without having to add a separate report project for each different state, i.e.:

ReportProject
-Reports (Root directory)
--California (Subdirectory)
--Washington (Subdirectory)

It seems odd that you can add folders to organize your project with any other project type, but you can't organize your reports this way. Was this missed in development of SSRS, or is it just another "feature"?

Adding folders in the solution explorer?

Hi..
Isn′t it possible to create sub folders in the solution explorer?
I have it hard time to create a decent structure inside the SSIS Package folder, since I have like 30 packages in my project. Is it just me being silly or cannot this be done?
Have a nice day
/Erik

nope...

You can add folders to a solution but withing a project, everything is either in packages of misc.

|||ok, thanks for the input though