cancel
Showing results for 
Search instead for 
Did you mean: 

union existing values from 1st query

Former Member
0 Kudos

Hi,

We have made the below query but the input field gives existing values only from 1st query of the union and not the second query of the union.How do we do it that existing values shows from both queries.

/* SELECT FROM [dbo].[PDN1] T1 */
declare @Itmcode1 as NVARCHAR(20)
Set @Itmcode1  = /* T1.Itemcode */ 'test1'

SELECT DISTINCT @Itmcode1"BP Name",T1.ItemCode"Part No.", T1.Dscription"Part Description",T6.FirmName "Manufacturer",T0.SuppSerial"DateCode",T0.Quantity"Qty",ISNULL(T2.Custom, 0)"Customs",T1.Price,
 ISNULL(T2.Cost, 0)"Cost",((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))"CVD", (ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))) 'Total' ,T0.U_Batch_Discount "Discount", T0.U_PRICELIST,T8.U_QTY1"SLAB1",CASE WHEN T5.QryGroup1 = 'N' THEN 
(((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN1)/100)
- (((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN1)/100) * (ISNULL(T0.U_Batch_Discount, 0)))/100)
 ELSE 0 END "SLAB1 Sale Price",T8.U_QTY2"SLAB2",CASE WHEN T5.QryGroup1 = 'N' THEN 
(((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN2)/100)
- (((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN2)/100) * (ISNULL(T0.U_Batch_Discount, 0)))/100)
 ELSE 0 END "SLAB2 Sale Price",T8.U_QTY3"SLAB3",CASE WHEN T5.QryGroup1 = 'N' THEN 
(((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN3)/100)
- (((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN3)/100) * (ISNULL(T0.U_Batch_Discount, 0)))/100)
 ELSE 0 END "SLAB3 Sale Price",
T8.U_QTY4"SLAB4",CASE WHEN T5.QryGroup1 = 'N' THEN 
(((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN4)/100)
- (((((ISNULL(T2.Custom, 0)+T1.Price+ISNULL(T2.Cost, 0)+((ISNULL(T0.U_Amt_CVDBED, 0))/(T0.Quantity))))*T8.U_MARGIN4)/100) * (ISNULL(T0.U_Batch_Discount, 0)))/100)
 ELSE 0 END "SLAB4 Sale Price",
(SELECT AltItem FROM OALI WHERE Remarks = 1 AND Origitem = T1.ItemCode) [AltItem1],
(SELECT T5.onHand FROM OITM T5 WHERE T5.Itemcode = (SELECT DISTINCT AltItem FROM [DBO].[OALI] T4 WHERE T4. Remarks = 1 AND T4.Origitem = T1.ItemCode)) [Stock],
(SELECT AltItem FROM OALI WHERE Remarks = 2 AND Origitem = T1.ItemCode) [AltItem2],
(SELECT T5.onHand FROM OITM T5 WHERE T5.Itemcode = (SELECT DISTINCT AltItem FROM [DBO].[OALI] T4 WHERE T4. Remarks = 2 AND T4.Origitem = T1.ItemCode)) [Stock],
(SELECT AltItem FROM OALI WHERE Remarks = 3 AND Origitem = T1.ItemCode) [AltItem3],
(SELECT T5.onHand FROM OITM T5 WHERE T5.Itemcode = (SELECT DISTINCT AltItem FROM [DBO].[OALI] T4 WHERE T4. Remarks = 3 AND T4.Origitem = T1.ItemCode)) [Stock],
T5.QryGroup1"Hot Device" 
FROM PDN1 T1 
LEFT JOIN IPF1 T2 ON T2.BaseEntry = T1.DocEntry AND 
T1.ItemCode = T2.ItemCode AND T2.Origline = T1.LineNum 
LEFT JOIN OIBT T0 ON T1.LineNum = T0.BaseLinNum AND 
T0.BaseNum = T1.DocEntry AND T0.ItemCode = T1.ItemCode
LEFT JOIN OALI T4  on T4.OrigItem = T1.ItemCode
LEFT JOIN [dbo].[@PRICE_MASTER] T8 ON T0.U_PRICELIST = T8.NAME,
OITM T5 inner join OMRC T6 on  T5.FirmCode = T6.FirmCode,ITM1 T3
WHERE  T3.Itemcode = T1.Itemcode  and T3.PriceList = 2 AND T5.Itemcode = T1.Itemcode AND T0.Quantity > 0 AND (T1.ITEMCODE like '%'+'[%0]'+'%')
union
SELECT T1.CardName "BP Name", T0.U_PartNo"Part No.",Null "Part Description", T0.U_Make"Manufacturer", T0.U_Datecode"DateCode", T0.U_Qty"Qty",0,0,0,0, T0.U_Price"Total",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null FROM [dbo].[OSCN]  T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode WHERE (T0.U_Nulify is null ) and T0.U_PartNo LIKE  '%'+'[%0]'+'%'

Regards,

Rajeev

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Rajeev

The difference between union and union all is if you have same records in two table the query will ignore the duplicate record if you use union. if you use union all the query will not ignore the records even they are duplicate.

As far as retriving the item code and partnumber you can make a simple query linking oitm table on pdn1 table.

Regards

[Abdul Muneem|http://www.brio.co.in/products/sap-business-one/]

Edited by: Muneem Abdul on Nov 22, 2010 12:08 PM

Answers (2)

Answers (2)

Former Member
0 Kudos

Because your query has so many UDF & UDT, there is no way that we can test it or rewrite it.

Just take a look of your two parts, I am afraid your logic may not be possible from any kind of queries due to the different table selections.

Thanks,

Gordon

Former Member
0 Kudos

Hi Rajeev,

First of all, you need to use Union All instead of union only. To answer your question, it is simple that the second part of query is just like a new query, you have to copy all the conditions to the second part.

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

What difference will union or union all make.

Please can you provide a example of copying all the conditions to the second query.Basically what i want is that i am using the same input variable in both queries .so the input variable should be populated with possible values of both queries in existing values.

Thanks in advance.

Regards,

Rajeev

former_member583013
Active Contributor
0 Kudos

You can check the following 2 links to understand the difference between UNION AND UNION ALL. SQL Server help files should also explain the same in detail.

http://www.tizag.com/sqlTutorial/sqlunion.php

http://blog.sqlauthority.com/2007/03/10/sql-server-union-vs-union-all-which-is-better-for-performanc...

When writing queries to be executed within Business One, it is important that you use the syntax

[dbo].[tablename] alias in your SELECT statements.

example:

FROM [dbo].[PDN1] T1 LEFT JOIN [dbo].[IPF1] T2 .......

You could also define the LIKE in the WHERE clause as

LIKE '%[%0]%'

Former Member
0 Kudos

Hi Gordon/Sudha,

When I use union i get 116 records and when union all 297 records.Which one would be correct to use union or union all .

Secondly , pls answer the below question.

Basically what i want is that i am using the same input variable in both queries of the union.so the input variable should be populated with possible values of both queries in existing values.Can u provide a example of this if it can be done.

Regards,

Rajeev

former_member583013
Active Contributor
0 Kudos

It is important to understand how the Input Variables work and whats displayed when you Press Existing Values.

In this area of your query, you are using T1.ItemCode which refers to the ItemCode in the Goods Receipt table,

therefore only ItemCode which are in the PDN1 table will be displayed and not values in the U_PartNo (UDF) from OSCN table

/* SELECT FROM [dbo].[PDN1] T1 */
declare @Itmcode1 as NVARCHAR(20)
Set @Itmcode1  = /* T1.Itemcode */ 'test1'

This is as per the system design and cannot be changed.

Regards

Suda

Former Member
0 Kudos

hi,

How can we modify the code such that we get ItemCode which are in the PDN1 table will be displayed as well as values in the U_PartNo (UDF) from OSCN able.If you cannt explain with ref. to this query you can provide me a example with some other query .

Rgds,

Rajeev.

Former Member
0 Kudos

query-followup