SQL Union Command using two tables 1

[socalvelo]

CR XI
Oracle db
SQL syntax experience = weak
Crystal syntax experience = competent

I am trying to write a SQL Command with a union to combine a two similar fields from two tables. I need both fields in one table for my report. The field is called charge (which contains various arrest charge codes applied to a person in police custody). I have listed the fields I will need to use in my report.

TABLE-1 is called INBOK6
Fields:
"INBOK6_VIEW"."PRN", PRIMARY KEY FIELD (one charge per PRN)
"INBOK6_VIEW"."Level",
"INBOK6_VIEW"."Warrant_No",
"INBOK6_VIEW"."Arrest_Date",
"INBOK6_VIEW"."Booking_No",
"INBOK6_VIEW"."Charge"

TABLE-2 is called INACG6
"INACG6_VIEW"."PRN", CHILD FIELD (0 to many charges per PRN)
"INACG6_VIEW"."Level",
"INACG6_VIEW"."Warrant_No",
"INACG6_VIEW"."Charge"

I need a left outer join from "INBOK6_VIEW"."PRN" to "INACG6_VIEW"."PRN"

Here's where I started, but I am confused on how to properly set up the SQL script for left outer join and if I have my fields set up on the proper side of the union.


SELECT 'CHARGE1' as whichcharge,
" INBOK6_VIEW"."Level",
" INBOK6_VIEW"."Warrant_No",
" INBOK6_VIEW"."Arrest_Date",
"INBOK6_VIEW"."PRN",
" INBOK6_VIEW"."Booking_No",
" INBOK6_VIEW"."Charge" as CHARGE
FROM "PDDB"."INBOK6_VIEW" " INBOK6_VIEW" LEFT OUTER JOIN "PDDB"."INACG6_VIEW" "INACG6_VIEW" ON "INBOK6_VIEW"."PRN"="INACG6_VIEW"."PRN"

UNION

SELECT
'CHARGE2' as whichcharge,
" INACG6_VIEW"."PRN",
" INACG6_VIEW"."Level",
" INACG6_VIEW"."Warrant_No",
" INACG6_VIEW"."Charge" as CHARGE
FROM "PDDB"."INBOK6_VIEW" " INBOK6_VIEW" LEFT OUTER JOIN "PDDB"."GAPD_INACG6_VIEW" "INACG6_VIEW" ON "INBOK6_VIEW"."PRN"="INACG6_VIEW"."PRN"

Here's one of the errors I get:
FROM KEYWORD NOT FOUND WHERE EXPECTED

 

[lbass]

I'm not convinced a union is called for here. Can you explain why you think you need to merge the two charge fields? How will the merged field then be used?

-LB

 

[socalvelo]

LB

Charge codes have a hierarchy rule where the most serious code will appear in the INBOK6.charge field. All other charges write to the INACG6.charge field. The charge code I want to select can appear in either table. I want to merge them all into one table so I can select only one specific charge code no matter which table it appears in. I have accomplished this another way by grouping and assigning a a numeric one each time the specific charge code appeared. However it seems that this would be more efficient with a union. Plus I am curious to know why I can't get this set up properly.
As a second part I will be using employee numbers to create a sum of a specific charge arrest to an employee number.

 

[jmd0252]

I don't belive a union will work either. It was my understand a union is to "merge", to files togather,, and the 2files have to be exactly alike. Like an order history file, and an active order file..

 

[socalvelo]

jmd0252
The files INBOK6_CHARGE and INACG6_CHARGE are the same except the second file contains multiple records (one to many relationship). I've seen other instances of a left outer join with a union in other threads on this forum. Are you saying it has to be a one to one relationship for a union to work properly?

 

[lbass]

Okay, you have to follow the principles of doing a union:

1) Fields to be merged must in the same ordinal position on each side of the Union.
2) Fields to be merged must be of the same datatype.
3) Any order by clause must be at the end, using an ordinal position rather than field name, e.g., Order by 2
4) Fields with no matches should be plugged with null
5) The field name in the first part of the union will be used to identify the fields in the command, so if it is a null, add a name to it.

Also, you appear to have extra spaces between your " and your field name, which should be removed, if not really in the field name.

A Union will take longer to process than a Union All, since it is looking for distinct records.


SELECT
'CHARGE1' as whichcharge,
"INBOK6_VIEW"."Level",
"INBOK6_VIEW"."Warrant_No",
"INBOK6_VIEW"."Arrest_Date",
"INBOK6_VIEW"."PRN",
"INBOK6_VIEW"."Booking_No",
"INBOK6_VIEW"."Charge" as CHARGE

FROM "PDDB"."INBOK6_VIEW" "INBOK6_VIEW"

WHERE "INBOK6_VIEW"."Charge" = '{?Charge}'

UNION

SELECT
'CHARGE2' as whichcharge,
"INACG6_VIEW"."Level",
"INACG6_VIEW"."Warrant_No",
null,
"INACG6_VIEW"."PRN",
null,
"INACG6_VIEW"."Charge" as CHARGE

FROM "PDDB"."GAPD_INACG6_VIEW" "INACG6_VIEW"

WHERE "INACG6_VIEW"."Charge" = '{?Charge}'

Create the parameter on the right hand side within the command and double click on it to add it where your cursor is placed within the command. If the field is a string, the parameter must be enclosed in single quotes. If not a string, remove the single quotes.

You don't need to add the left joined table because you are adding it via the union.

-LB

 

[socalvelo]

LB - Thank you very much for your assistance. This worked for me and got me in the right direction. However using the "charge parameter", I tested a charge code and only came up with results form the INBOK6 table and none from the INACG6 table. To further experiment, I removed the "charge parameter" for a date range for testing purposes. What I came up with was about 48,000 records. It should have only been about 500 records. There were only a few hundred records from the Charge1 (INBOK6)table and the 48,000 were from the charge2 (INACG6) table.

There is no date field in the INACG6 table.
The ONLY common field between these two tables is the PRN field.
My problem now is that I can't get the records in the right table to only report matching PRN records from the left table. I am a bit confused about #3 and #5 in the union rules, and not sure if this is what I am doing wrong. I will go back and add parameters when I get the results correct on a date range.

Here's is what I am now trying. Note that I used abbreviated database, table, and field names to simplify my initial question.
***************

SELECT
'CHARGE1' as whichcharge,
"GAPD_INBOK6_VIEW"."Arrest_Date",
"GAPD_INBOK6_VIEW"."Booking_No",
"GAPD_INBOK6_VIEW"."Level",
"GAPD_INBOK6_VIEW"."Sequence_No",
"GAPD_INBOK6_VIEW"."Warrant_No",
"GAPD_INBOK6_VIEW"."PRN",
"GAPD_INBOK6_VIEW"."Charge" as CHARGE

FROM
"TIBURON"."GAPD_INBOK6_VIEW" "GAPD_INBOK6_VIEW"

WHERE
("GAPD_INBOK6_VIEW"."Arrest_Date">={ts '2011-03-01 00:00:00'} AND "GAPD_INBOK6_VIEW"."Arrest_Date"<{ts '2011-04-01 00:00:00'})


UNION

SELECT
'CHARGE2' as whichcharge,
null,
null,
"GAPD_INACG6_VIEW"."Level",
"GAPD_INACG6_VIEW"."Sequence_No",
"GAPD_INACG6_VIEW"."Warrant_No",
"GAPD_INACG6_VIEW"."PRN",
"GAPD_INACG6_VIEW"."Charge" as CHARGE
FROM "TIBURON"."GAPD_INACG6_VIEW" "GAPD_INACG6_VIEW

 

[lbass]

My mistake. You do need to add both tables to each side of the union in order to limit the records to the prn, like this:

Select
'CHARGE1' as whichcharge,
"GAPD_INBOK6_VIEW"."Arrest_Date",
"GAPD_INBOK6_VIEW"."Booking_No",
"GAPD_INBOK6_VIEW"."Level",
"GAPD_INBOK6_VIEW"."Sequence_No",
"GAPD_INBOK6_VIEW"."Warrant_No",
"GAPD_INBOK6_VIEW"."PRN",
"GAPD_INBOK6_VIEW"."Charge" as CHARGE

FROM
"TIBURON"."GAPD_INBOK6_VIEW" "GAPD_INBOK6_VIEW"

LEFT OUTER JOIN "TIBURON"."GAPD_INACG6_VIEW" "GAPD_INACG6_VIEW" ON
"INBOK6_VIEW"."PRN"="GAPD_INACG6_VIEW"."PRN"

WHERE
("GAPD_INBOK6_VIEW"."Arrest_Date">={ts '2011-03-01 00:00:00'} AND
"GAPD_INBOK6_VIEW"."Arrest_Date"<{ts '2011-04-01 00:00:00'})


UNION

SELECT
'CHARGE2' as whichcharge,
null,
null,
"GAPD_INACG6_VIEW"."Level",
"GAPD_INACG6_VIEW"."Sequence_No",
"GAPD_INACG6_VIEW"."Warrant_No",
"GAPD_INACG6_VIEW"."PRN",
"GAPD_INACG6_VIEW"."Charge" as CHARGE

FROM
"TIBURON"."GAPD_INBOK6_VIEW" "GAPD_INBOK6_VIEW"

LEFT OUTER JOIN "TIBURON"."GAPD_INACG6_VIEW" "GAPD_INACG6_VIEW" ON
"INBOK6_VIEW"."PRN"="GAPD_INACG6_VIEW"."PRN"

Without the joins, you were picking up all records in the second table. Sorry--I didn't think this one out clearly.

#3 just means that if you wanted to order the data in the set by some field, you have to do add the "Order By" clause at the end of the entire query, and you have to use the ordinal number of the field instead of the field name. So if you wanted to order the data on the server by the PRN field, you would use:

Order by 7

Rule #5 refers to naming the fields. When you use a command, the fields are returned as {command.field}. If you are using a null in the first part of the query, you need to name it, so you would write it like this in the Select statement:

null as fieldname,

The syntax varies per datasource. In Oracle, it would be:

null "fieldname"

to return {command.fieldname}. Sometimes the fields on each side of the union have different names, and it can be confusing to see the name of the field for one side used as the overall field name when you know the field contains fields of two different names, so sometimes it makes sense to use a more generic name.

-LB

 

[socalvelo]

Thank you LB - this is working great. I appreciate you help.

I had to add the date range to the second half of the union to get results from the second table.

WHERE
("GAPD_INBOK6_VIEW"."Arrest_Date">={ts '2011-03-01 00:00:00'} AND
"GAPD_INBOK6_VIEW"."Arrest_Date"<{ts '2011-04-01 00:00:00'})

Also......

Is it possible to replace the hard dates with a date range using SQL language and without using a parameter selection?


I'm referring to these Crystal date range examples:

{Arrest_Date} in currentdate-30 to currentdate

or

{Arrest_Date} in YearToDate

or

year({Arrest_Date})= 2011

and so on......

 

[lbass]

Yes, in Oracle, you should be able to use:

"GAPD_INBOK6_VIEW"."Arrest_Date">={fn curdate()}-30 and
"GAPD_INBOK6_VIEW"."Arrest_Date"<{fn curdate()}+1

or:

"GAPD_INBOK6_VIEW"."Arrest_Date">=to_date('2011-01-01','YYYY-MM-dd') and
"GAPD_INBOK6_VIEW"."Arrest_Date"<{{fn curdate()}+1

or:

{fn year("GAPD_INBOK6_VIEW"."Arrest_Date")}=2011

-LB

 

[socalvelo]

Thank you for the assistance. This is working well.