easy newbie SQL question 1

[sfabry]

What do i use to join the 2 statments below? If i do not put anything after "null" then the field does not show up in my command.

SELECT "v_basic_booking_data"."book_urn", "v_basic_booking_data"."fac_mnc", "v_basic_booking_data"."book_date", "v_basic_booking_data"."book_status", "v_basic_booking_data"."patient_name", "v_basic_booking_data"."proc_descr", "v_basic_booking_data"."surg_mnc",
"v_basic_booking_data"."pt_unit_number"
FROM "ormanager"."reportuser"."v_basic_booking_data" LEFT JOIN "ormanager"."reportuser"."v_basic_case_rec_data" ON "v_basic_booking_data"."book_urn" = "v_basic_case_rec_data"."book_urn"
WHERE "v_basic_booking_data"."book_status"='A' AND ("v_basic_case_rec_data"."book_urn" is null)

***need join here***

SELECT "v.BKA_02_Booking_1"."bk_pat_type"
FROM "ormanager"."reportuser"."v.BKA_02_Booking_1" INNER JOIN "ormanager"."reportuser"."v_basic_booking_data"
ON "v.BKA_02_Booking_1"."book_urn" = "v_basic_booking_data"."book_urn

 

[jmd0252]

Not to be silly,, but are you sure you have the correct forum??
Are you doing this i a command, to get into your crystal report??
Or ae you asking if you need an inner, or out join, for your crystal report?

 

[sfabry]

not silly, i could be wrong. i am kind of testing/teaching myself how to do this is crystal...
in crystal's database explorer i chose "add command" to new report and then typed in the above. i am trying to get fields from 2 different views to be available for my report.
when i type in the 1st part, it works fine but the 2nd part does not show up. I tried "AND" and "," but it is a syntax error - normally i just link the tables or views (in the "links" tab with arrows) so i am not sure how to do it this way.
does this need to be somewhere else?

 

[jmd0252]

you do not want to add a command,, click on the left side, and add the view(s) to the right side,, when you click ok,, it will take you to a screen, where you define how the data is joined..for most reports, this is the accepted way to define files, or views..

 

[lbass]

I think you should explain why you are not simply joining the two views in the database expert.

-LB

 

[sfabry]

JMD & LB - normally i would just join the 2 views but i have a report built on a command which is the top part of the command in the original question and i would like to add the bottom part so i have access to the group type.
I did not originally build this command, I am just trying to add to it. I also do not have access to build views myself - I wouldnt know how to if i did anyway...
why do you not want to use commands? what are the differences?

 

[jmd0252]

Commands execute much quicker, as they are processed by the SQL server before they "get into" Crystal. But when you join files, tables, view, etc, etc. The command becomes very cumbersome. Why not rewrite it and just use the data, like you normally would. You have the original, which shows you everything going on. Rewritting should be easy. The best way I have found to generate a command, is to go into the SQL Manager, and try building a view with 2 tables, it will lead you thru what to do, and will show you the sql that is needed.

 

[sfabry]

I do not believe that I have access to the SQL manager/the ability to write a new view. But in other words, command should only be used for one table? Why not just select the whole table and sort or select in crystal - is it only for execution time?

 

[lbass]

When you use a command, you should use it as your sole datasource, since linking it to another table will cause the linking to occur locally and dramatically slow the report. However, I see no reason not to just build the additional table into the existing command--although without knowing the purpose of the tables, I can't say for sure.

Go into the database expert->command->right click->edit command-> and enter:

SELECT "v_basic_booking_data"."book_urn",
"v_basic_booking_data"."fac_mnc",
"v_basic_booking_data"."book_date",
"v_basic_booking_data"."book_status",
"v_basic_booking_data"."patient_name",
"v_basic_booking_data"."proc_descr",
"v_basic_booking_data"."surg_mnc",
"v_basic_booking_data"."pt_unit_number",
"v.BKA_02_Booking_1"."bk_pat_type"

FROM (("ormanager"."reportuser"."v_basic_booking_data"

LEFT JOIN "ormanager"."reportuser"."v_basic_case_rec_data" ON "v_basic_booking_data"."book_urn" = "v_basic_case_rec_data"."book_urn"
)

LEFT JOIN "ormanager"."reportuser"."v.BKA_02_Booking_1" ON
"v_basic_booking_data"."book_urn"="v.BKA_02_Booking_1"."book_urn"
)

WHERE "v_basic_booking_data"."book_status"='A' AND
("v_basic_case_rec_data"."book_urn" is null)

I left joined the new table to the existing table, so that there is no chance it will limit records provided by the initial query, but an inner join would be fine if there is always a matching record in the BKA_02 Booking_1 table.

-LB

 

[sfabry]

thanks again LB -
the left join is probably a better idea b/c we've been having some issues with info being kicked out on the program end... but if you don't mind explaining, what do you mean by the "purpose" of the tables? Do you mean the meaning of the fields/report?

also, in the 1st part of the statement you can add all of the fields that you want regardless of the table or the FROM statement? Wouldn't you also need a FROM statement with

"ormanager"."reportuser"."v.BKA_02_Booking_1"

since its another table/view? My original thinking was that you would need to SELECT FROM WHERE separately for each table you would be joining. I am not able to access it right now to try it but will tomorrow...

I am also trying to find some good books on this too, if you have any suggestions. I am obviously very new at this but enjoy it and thinking of pursuing it more seriously as a career option.

 

[lbass]

I don't know how the table behaves in relation to the other table--does it only have one record per record in the main table? Or can it be null (have no corresponding record). Sometimes knowing what the table is doing for you helps to know how it should be linked.

Did you try my query? No, you should NOT have a separate Select/From/Where for each table. The new table IS in the From clause, since it is linked to the first table.

-LB

 

[sfabry]

LB - basic booking would be the table i would link from (the "primary") so left join is the better choice.
about the statements - i thought the purpose was to tell it to SELECT "these fields" FROM "this table" and that you would need to specify each table to find the specific fields. is it included in your FROM statement b/c of the parentheses?
And yes, i just tried your solution and it worked. Thank you again!

 

[lbass]

FROM (("ormanager"."reportuser"."v_basic_booking_data"

LEFT JOIN "ormanager"."reportuser"."v_basic_case_rec_data" ON "v_basic_booking_data"."book_urn" = "v_basic_case_rec_data"."book_urn"
)

LEFT JOIN "ormanager"."reportuser"."v.BKA_02_Booking_1" ON
"v_basic_booking_data"."book_urn"="v.BKA_02_Booking_1"."book_urn"
)

The above is the entire FROM statement. You can see the links between the tables by looking at the "ON" clauses, and the parens are necessary to organize the joins.

-LB

 

[sfabry]

OK thank you, I appreciate your help and patience.