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

 

[huddles]

PAJR

I have succeeded in adding the view as a table, and it joins nicely on the Itm_id. When preview, I get the following error

The column prefix 'vPacklistUpc' does not match with a table name or alias name used in query.

any thoughts?

 

[pajr777]

Every time you add a view or table in the SQL database you need to quit and restart CR - I haven't found a way to force it to refresh the list.

Can you paste the sequel statement from Crystal?

 

[huddles]

sure,

here it is

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;,
vPacklistUpc.&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;}

it is the vPacklistUpc.&quot;Scan_id&quot; that is giving me the grief

the query in this view isCREATE VIEW dbo.vPacklistUpc
AS
SELECT DISTINCT dbo.Item_Xref.SCAN_ID, dbo.Item_Xref.ITM_ID
FROM dbo.Item_Xref INNER JOIN
dbo.Item_Xref Item_Xref_1 ON dbo.Item_Xref.ITM_ID = dbo.Item_Xref.ITM_ID AND dbo.Item_Xref.SCAN_ID <> dbo.Item_Xref.ITM_ID

 

[synapsevampire]

You don't need to restart Crystal, point at the datasource in the Data Explorer and hit F5 (refresh).

vPacklistUpc isn't referenced in your FROM.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[huddles]

ok, think I am almost done.

I created a view, as mentioned by James.

The report is looking quite sharp, except for this last issue, so one more push and this baby is over the cliff. I feel like Sisyphus.


The report now stands at:



The report prints this out:
QTY Col_B Col_A RtlPrc ExtPrc
2 88 12345 36.00 72.00
2 88 12345 36.00 72.00
1 88 88 33.00 33.00
1 777 777 17.00 17.00

The second line for 88 should not be there, as only twice was this item sent.

Here is the sql code as it now stands:
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;,
vPacklistUpc.&quot;SCAN_ID&quot;
FROM
(&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; left outer join vPacklistUpc on (vpacklistupc.&quot;itm_id&quot; = Packing_List.&quot;itm_id&quot; and Packing_list.&quot;itm_id&quot; <> vpacklistupc.&quot;scan_id&quot }
ORDER BY
vPacklistUpc.&quot;SCAN_ID&quot; ASC,
Packing_List.&quot;STYLE_ID&quot; ASC,
Packing_List.&quot;CHAR_VALUE_1&quot; ASC,
Packing_List.&quot;CHAR_VALUE_2&quot; ASC,
Packing_List.&quot;CHAR_VALUE_3&quot; ASC,
Packing_List.&quot;CHAR_VALUE_4&quot; ASC

 

[huddles]

I have tried the Report...Options Select Distinct, but this does not seem to work.

 

[Naith]

The 'Select Distinct' option will work (depending on your datasource), where the values in every single field in the select match those in a previous row. Although you're only displaying 5 out of 15 odd in your example, are you sure a value in one of the other 10 fields is not making the row distinct from what you consider to be it's previous counterpart? No comment in one of the 'COMNT_TXT', for instance?

If the row's definitely distinct, and SELECT DISTINCT isn't working - though I can't imagine why it wouldn't be - you can suppress your row using something like this:

(In Format Section; press the X+2 button next to Suppress, and enter

Not OnFirstRecord;
{QTY} = Previous({QTY}) and
{Col_B} = Previous({Col_B}) and
{Col_A} = Previous({Col_A})

I would prefer to not bring the row back in the first place, which is what 'Select Distinct' is supposed to achieve. But, if you've tried that, and have problems then the above will work. Besides, it's Friday, and I'm sure you want to put the lid on this and get outta here.

Naith