I am joining a number of tables, mostly to retrieve descriptive information like names from related tables, and the simpler joins are working ok but where I am a little stuck is that I need to get project names from one database table if the value in a column called ''type'' is 1, and an entirely different table (in a different database) if the value in column ''type'' is 2. Not all records have an entry in the column, it is an optional column so there are nulls as well. I am a bit unsure of how to do it, I tried %26lt;CFIF%26gt; statements and also SQL case but couldn't get it to work. I was trying to use cfif to read the value in the column but it didn't seem to do it, I am not sure if I just got the syntax wrong or whether it can't be done this way. I really want to do this within the SQL query so that all the data is in one final table ready to display. I will also later need to count the number of 1s and 2s.
Can I do this with an if statement or is there another way? My code was along the lines of:
%26lt;cfif isNumeric(''m.type'') IS 2 %26gt;
Are the two databases on the same server and are they the same type (MSSQL, Oracle, whatever)??If so, you might be able to set it up as a single database query, as long as the proper permissions are in place.?Otherwise, Queries of Queries might be the answer.
sql help querying multiple database...Yes, same server, same database type (mssql). I have up to now been using a different dsn for the second database and just grabbing the data from the second database in the CF code when displaying the data another part of the app (Query of Queries used there), but it would be nice to have one neat table to use in multiple pages without post-processing. I want to do it the simplest possible way so I can reuse it. I was hoping it could be done in the sql with an if statement or something, but I haven't worked it out for the table in the same database let alone the one in the second database.
No comments:
Post a Comment