SQL Query 2

[huddles]

I need to enter a sql query for a field.

I have the following query, which will populate the field. How do I enter this or the equivalent?

SELECT distinct i1.scan_id
FROM item_xref i1 join
item_xref i2 on i1.itm_id = i2.itm_id
WHERE i1.scan_id <> i2.itm_id

It essentially queries the table, which has two columns as such
Col_A Col_B
12345 88
88 88

It returns only the unique value from Col_A.

I can use the column directly when creating a new field (Item_xref.Col_A), but then the report will show two line, 12345 and 88.

any help would be great

Huddles

 

[dtaylor92]

Have you considered displaying Col_A in a subreport?

I'm not sure I understand the scope of everything you are trying to do... what do you mean a sql query on a &quot;field&quot;, do you mean the entire report is a single column of values?

Depending on the database, sometimes you can select the values into a temporary table and then use them in another query.

Here is one query I did that does everything in a single query:

SELECT distinct aafile.aa_name, rtfile1.rt_class, rafile.ra_sid, rafile.ra_cs_ehrs, rafile.ra_cs_qhrs, rafile.ra_cs_trf_ehrs, rafile.ra_cs_gpa, ra_cs_qhrs + ra_cs_trf_ehrs as ret_hrs
from dbo_aafile_root_seg_header as aafile, dbo_rafile_cs AS rafile, dbo_rtfile_root_seg_array_acad_pgm_key AS rtfile1
INNER JOIN dbo_rtfile_root_seg_array_acad_pgm_key AS rtfile2
ON (rtfile1.rt_sid = rtfile2.rt_sid and
rtfile1.rt_term = '1990F' and
rtfile2.rt_term = '1991S' and
)
WHERE rtfile2.rt_term = '1991S'
AND rtfile2.rt_sid = rafile.ra_sid
AND rtfile2.rt_sid = aafile.aa_ssn
AND rtfile2.rt_sid = rafile.ra_sid
order by 3

Here's the SAME result using a temp table:
--Create temporary table for Step1 & Step2
declare @t1 table ( rt_sid char(10) )

--Find all Students who are both 1990F and 1990S
insert into @t1 (rt_sid)
select distinct rtfile1.RT_SID
from dbo_RTFILE_ROOT_SEG_ARRAY_ACAD_PGM_KEY as rtfile1
INNER JOIN dbo_RTFILE_ROOT_SEG_ARRAY_ACAD_PGM_KEY as rtfile2
ON (rtfile1.RT_SID = rtfile2.RT_SID AND
rtfile1.rt_term = '1990F' AND
rtfile2.rt_term = '1991S')

--Display the Student id, class and term
select distinct aafile.aa_name, rtfile.rt_sid, rtfile.rt_class, rtfile.rt_term, rafile.ra_cs_ehrs, rafile.ra_cs_qhrs, rafile.ra_cs_trf_ehrs, rafile.ra_cs_gpa, ra_cs_qhrs + ra_cs_trf_ehrs as ret_hrs
from dbo_RTFILE_ROOT_SEG_ARRAY_ACAD_PGM_KEY as rtfile, dbo_rafile_cs AS rafile, dbo_aafile_root_seg_header as aafile
where rtfile.rt_sid in (select rt_sid from @t1)
AND rtfile.rt_term = '1991S'
AND rtfile.rt_sid = rafile.ra_sid
AND rtfile.rt_sid = aafile.aa_ssn
AND rtfile.rt_sid = rafile.ra_sid
order by 2

 

[huddles]

Dtaylor,

thank you.

The report uses one database, and two tables. It gets an item from table1. Table two is joined on the common item record (Col_B). The second table has this structure:

Col_A Col_B
12345 88
88 88

The query I have derives the unique number. I have a column in the report that should display Col_A data, but I only want the unique value for the item.

I originally created the field, but the report then displays both values for item (88). Thus the query to derive the unique value.

I do not know how to create/modify the column so that it can use this query.

Confused?

Thanks

 

[lyanch]

If I am reading this correctly. You want to display column A, but only if column A is not the same as column B.

If that is true. Add the following to your selection formula

{table1.ColA} <> {table2.ColB}

Lisa

 

[synapsevampire]

Depends on the version of CR.

If you have 8.5 or less, you can't write a custom SQL Statement, so a quick cheat is to use a suppress in the details (right click the details->X-2 alongside the Suppress) which is something like:

table.Col_A = table.Col_B

This would suppress those that are the same.

Another means would be to group on the Col_B and display the results in the group footer (rather than details), this assumes that COL_A > COL_B.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[huddles]

Hello,
Even with the suppress, the Crystal Code is as follows:

SELECT
Packing_List.&quot;TRNS_NBR&quot;, Packing_List.&quot;RGST_ID&quot;, Packing_List.&quot;ITM_ID&quot;, Packing_List.&quot;STYLE_ID&quot;, Packing_List.&quot;CHAR_VALUE_1&quot;, Packing_List.&quot;CHAR_VALUE_2&quot;, Packing_List.&quot;CHAR_VALUE_3&quot;, Packing_List.&quot;CHAR_VALUE_4&quot;, Packing_List.&quot;DESCR&quot;, Packing_List.&quot;RTL_PRC&quot;, Packing_List.&quot;EXT_PRC&quot;, Packing_List.&quot;ORD_QTY&quot;, Packing_List.&quot;SHIP_QTY&quot;,
Transfer_Comment.&quot;COMNT_TXT&quot;,item_xref.&quot;scan_id&quot;

FROM
{ oj (&quot;Packing_List&quot; Packing_List INNER JOIN &quot;Tradewind&quot;.&quot;dbo&quot;.&quot;Item_Xref&quot; Item_Xref ON
Packing_List.&quot;ITM_ID&quot; = Item_Xref.&quot;ITM_ID&quot; )
LEFT OUTER JOIN &quot;Transfer_Comment&quot; Transfer_Comment ON
Packing_List.&quot;TRNS_NBR&quot; = Transfer_Comment.&quot;TRNS_NBR&quot;}

The result looks like:


Header1 Col_A Col_B Header4 Header5
xxxxxx 88 12345 xxxxxxx xxxxxxx
xxxxxx 88 xxxxxxx xxxxxxx


I do not want that second row printing at all. The suppress only removed the Col_B from printing.

Thank in advance. Great tips!

 

[lyanch]

you don't want to suppress it, you want it to go away. Add the item I gave you to your selection formula.

Lisa

 

[huddles]

Lisa, and others.

I must apologize.

There will be time whwre the Col_A and Col_B values will be a 1:1 situation, such as
Col_A Col_B
12345 88
88 88
999 999
777 777
444 35
35 35

What I need to see is
12345
999
777
444

Therefore if count(Col_A) > 1 for the same Col_B, only show me the unique value, else show me the Col_A value that = Col_b

Thanks

 

[Reebo99]

Huddles,

I've experienced similar before, I used the following, however, it is not very pretty but it shows the correct data (so far anyway!) :

Group by Col_B

create a formula with the following and place in detail :

if {Col_A}-{Col_B} = 0 then 0 else {Col_A}

create another formula with the following and place in the Group Footer :

if count({Col_B},{Col_B}) = 1 then {Col_A} else sum({formula1},{Col_B})

supress the group header and the detail sections.

Hope this helps, and I know I'm leaving myself open to lots of comments from the crystal experts, but it meets my needs. If anyone knows of a better way, I'd be happy to use it. Reebo
Scotland (Raining)

 

[huddles]

Hello Reebo,

unfortunately the Col_A and Col_B are varchar type, and I get a warning that number is required right after the &quot;IF&quot;

 

[Reebo99]

use cdbl() e.g. cdbl({Col_A}), assuming that the contents can be converted to numbers. Reebo
Scotland (Raining)

 

[huddles]

sorry Reebo, no dice. I now get a &quot;summary/ running total could not be created&quot;

Frustrating for something that seems so simple.

 

[Reebo99]

change :
if count({Col_B},{Col_B}) = 1 then {Col_A} else sum({formula1},{Col_B})

to:
if count({Col_B},{Col_B}) = 1 then {Col_A} else maximum({formula1},{Col_B})

I'm assuming you used CDbl() on all instances of {Col_A} and {Col_B}?
Reebo
Scotland (Raining)

 

[huddles]

sorry mate,

same message with either sum or maximum. I have the CDbl on all instances of Col_A or Col_B.

 

[Reebo99]

bugger! Reebo
Scotland (Raining)

 

[harwooddale]

why not create a view on the database and select only where the columns are different or the same depending on what you want?

 

[huddles]

James,

I have created the view, and it works just lovely. From the above thread, I the following:

Col_A Col_B
12345 88
88 88
999 999
777 777
444 35
35 35

The report prints this out:
QTY Col_B Col_A Header4 Header5
1 88 12345 xxxxxxx xxxxxxx
1 88 88 xxxxxxx xxxxxxx
1 777 777
What I need to print is:
QTY Col_B Col_A Header4 Header5
2 88 12345 xxxxxxx xxxxxxx
1 777 777 xxxxxxx xxxxxxx

I must join two tables to get the Col_A value, the problem is is that it keeps showing both lines no matter what I have tried.

any help is greatly appreciated.

 

[huddles]

sorry, I have the view created, but do not know how to use this with Crystal

 

[pajr777]

Add database, click options and make sure the 'Views' checkbox is ticked. Then use it just like a regular table.

 

[huddles]

pajr,

you may be on to something. I have created the view, and it display what I need, but I just can't seem to get Crystal to let me see it as a table. I see another view (created a while ago) as a valid table, but not this new one. I have refreshed SQL Server, as well as through Crystal. Any thoughts on how to get Crystal to let me see my little lovel;y view?

Cheers, (I can feel I am almost home).