Compare value from first select statement with second select statement

myDBR Forums » General discussion

Compare value from first select statement with second select statement

(9 posts) (2 voices)

Started by Stuart Gibson
Latest reply from Stuart Gibson

Tags:

No tags yet.

Stuart Gibson, Member

Hi,

I have a report which lists current orders by date. I need to highlight the current Order which is in progress.

The following displays the current order (bgm) being worked on:

declare vCurrentBGM varchar(11);
SELECT bgm AS vCurrentBGM FROM syflux_archives.table_msg WHERE pm_date <> 0 ORDER BY pm_date DESC LIMIT 1;

I then have another select statement to produce my listing of all Orders.

SELECT DATE_FORMAT(dtm234,'%Y-%m-%d %H:%i') as 'Coll Date', dtm76 AS 'Delivery Date', bp.reference AS 'Part',
bgm AS 'Manifest', COUNT(plq_1004) AS 'Qty', tcm.dernier_rack_camion AS 'Start Seq', tcm.premier_rack_camion AS 'End Seq',
IF (msg.num_camion > 0,msg.num_camion,'') AS 'Shipment', IF(msg.num_camion <> 0,CONCAT(MID(ref_on,8,2),RIGHT(ref_on,6)),'') AS 'ASN',
DATE_FORMAT(delivery_date,'%Y-%m-%d %H:%i') as 'Shipped', msg.pdsn AS 'PDSN', null as 'nullcolumn'
FROM syflux_archives.table_msg AS msg LEFT JOIN syflux_archives.table_camions AS tcm ON msg.num_camion = tcm.num_camion
LEFT JOIN syflux_archives.base_produit AS bp ON bp.reference = msg.plq_7304
WHERE DATE_FORMAT(STR_TO_DATE(LEFT(dtm,8), '%Y%m%d'), '%Y-%m-%d') >= DATE_FORMAT(vStartDate, '%Y-%m-%d') AND msg.rack = 'RACK_FEM'
GROUP BY bgm, msg.num_camion
ORDER BY processorder, bgm, msg.num_camion;

How do I then highlight the current Order being worked on in the second select statement. Can I put the value from the first select statement into a global variable and then compare it with the values in the second select statement and change its colour to Green if the same?

Hope you can help.

Thanks,
Stu

myDBR Team, Key Master

You can use dbr.rowstyle to highlight the row.

To get the ID for the order in progress into a local variable vCurrentBGM and use that value to set rowstyle to the current row:

declare vCurrentBGM varchar(11);

SELECT bgm into vCurrentBGM

FROM syflux_archives.table_msg

WHERE pm_date != 0

ORDER BY pm_date DESC

LIMIT 1;

select 'dbr.rowstyle', 'rowstyle';

select 'dbr.colstyle', 'Shipment', '%d; ';

SELECT

  dtm234 as 'Coll Date',

  dtm76 AS 'Delivery Date',

  bp.reference AS 'Part',

  bgm AS 'Manifest',

  COUNT(plq_1004) AS 'Qty',

  tcm.dernier_rack_camion AS 'Start Seq',

  tcm.premier_rack_camion AS 'End Seq',

  msg.num_camion AS 'Shipment',

  IF(msg.num_camion != 0, CONCAT(MID(ref_on,8,2),RIGHT(ref_on,6)),'') AS 'ASN',

  delivery_date as 'Shipped',

  msg.pdsn AS 'PDSN',

  null as 'nullcolumn'

  if (msg.bgm=vCurrentBGM, 'background-color:#B3FFC0', '') as 'rowstyle'

FROM syflux_archives.table_msg msg

  LEFT JOIN syflux_archives.table_camions tcm ON msg.num_camion = tcm.num_camion

  LEFT JOIN syflux_archives.base_produit bp ON bp.reference = msg.plq_7304

WHERE cast(dtm as date) >= vStartDate AND msg.rack = 'RACK_FEM'

GROUP BY dtm234, dtm76, bp.reference, bgm, tcm.dernier_rack_camion, tcm.premier_rack_camion, Shipment, ASN, delivery_date, msg.pdsn

ORDER BY processorder, bgm, msg.num_camion;

In the queries, try to keep the datatypes intact. Datatype conversions (like caused by DATE_FORMAT), lose the original datatype and converts dates to strings. Keeping the original datatypes lets myDBR to offer better user experience to user (date sorting, user date/number formats etc).

--
myDBR Team

Stuart Gibson, Member

Hi,

I ran the code but the cell is not being highlighted. Im not sure the variable vCurrentBGM is keeping the value from the first SELECT statement and then passing to the second.

Any ideas?

Thanks also for your comment about DATE_FORMAT.
myDBR Team, Key Master

Stu,
why not just select vCurrentBGM and see if your logic for fetching the order in progress is correct.

The "WHERE pm_date != 0" sounds bit dubious as the column name would indicate that it would be a date column, not integer.

--
myDBR Team
Stuart Gibson, Member

Hi,

The first SELECT statement returns correct value. Basically the last record with a date, so returns value V0281122004, so a varchar.

Value seems to be dropped when the second SELECT statement is ran.

do you have email I can send image to and a case number?

Thanks,
Stu
myDBR Team, Key Master
Stu,
the query is quite basic and debugging it is straightforward. So, if the first query returns V0281122004, then:

1) Do you see the value V0281122004 in your report listing in the 'Manifest' column?

Note that the first query gets the value from a set where "pm_date != 0", the actual report shows only the ones where "cast(dtm as date) >= vStartDate AND msg.rack = 'RACK_FEM'". So it could be that the the newest one is not in the list at all.

2) Did you add the rowstyle column?
```
if (msg.bgm=vCurrentBGM, 'background-color:#B3FFC0', '') as 'rowstyle'
```
3) Did you add the dbr.rowstyle-command?
```
select 'dbr.rowstyle', 'rowstyle';
```
4) What are the datatypes for syflux_archives.table_msg.bgm and syflux_archives.table_msg.pm_date fields?

--
myDBR Team
Stuart Gibson, Member

Hi,

1) Yes, value V0281122004 appears in Manifest column.

2) Yes, rowstyle column added.

3) Yes, rowstyle command added.

4) bgm datatype is varchar(15) and pm_date is varchar(17)

DROP PROCEDURE IF EXISTS sp_DBR_OrderSummary
$$
CREATE PROCEDURE `sp_DBR_OrderSummary`(vStartDate Date, ReportType varchar(1))
BEGIN

DECLARE vCurrentBGM varchar(15);

SELECT bgm AS vCurrentBGM
FROM syflux_archives.table_msg
WHERE pm_date != 0
ORDER BY pm_date DESC LIMIT 1;

SELECT 'dbr.report', 'sp_DBR_Order_Details', '[Delivery Date]', 'vDelDate=Delivery Date', 'vManifest=nullcolumn', 'vShipment=nullcolumn';
SELECT 'dbr.report', 'sp_DBR_Order_Details', '[Manifest]', 'vManifest=Manifest', 'vDelDate=nullcolumn', 'vShipment=nullcolumn';
SELECT 'dbr.report', 'sp_DBR_Order_Details','[Shipment.h]', 'vShipment=Shipment.h', 'vDelDate=nullcolumn', 'vManifest=nullcolumn','show_link=[Shipment]>0';
SELECT 'dbr.report', 'sp_DBR_AVIDIS', '[ASN.h]', 'popup[ASN]', 'vASNNumber=[PDSN]';

-- select 'dbr.report', 'sp_DBR_Order_Details','[Coll Date.h]', 'vCollDate=Coll Date.h', 'show_link=[Coll Date]>0';
-- select 'dbr.report', 'sp_DBR_Order_Details','[DI Date.h]', 'vDIDate=DI Date.h', 'show_link=[DI Date]>0';
-- select 'dbr.report', 'sp_DBR_Order_Details','[Shipment.h]', 'vShipment=Shipment.h', 'show_link=[Shipment]>0';
-- select 'dbr.report', 'sp_DBR_Order_Details','[Manifest.h]', 'vManifest=Manifest.h';

SELECT 'dbr.title', 'Summary';
SELECT 'dbr.colstyle', 'Shipment','%d';
SELECT 'dbr.colstyle', DATE_FORMAT('DI Date','%Y-%m-%d %H:%i');

SELECT 'dbr.rowstyle','rowstyle';

SELECT 'dbr.hidecolumn', 'PDSN';
SELECT 'dbr.hidecolumn', 'nullcolumn';

SELECT DATE_FORMAT(dtm234,'%Y-%m-%d %H:%i') AS 'Coll Date',
dtm76 AS 'Delivery Date',
bp.reference AS 'Part',
bgm AS 'Manifest',
COUNT(plq_1004) AS 'Qty',
tcm.dernier_rack_camion AS 'Start Seq',
tcm.premier_rack_camion AS 'End Seq',
msg.num_camion AS 'Shipment',
IF(msg.num_camion != 0,CONCAT(MID(ref_on,8,2),RIGHT(ref_on,6)),'') AS 'ASN',
DATE_FORMAT(delivery_date,'%Y-%m-%d %H:%i') as 'Shipped',
msg.pdsn AS 'PDSN',
null AS 'nullcolumn',
IF (msg.bgm=vCurrentBGM, 'background-color:#B3FFC0', '') AS 'rowstyle'
FROM syflux_archives.table_msg AS msg
LEFT JOIN syflux_archives.table_camions AS tcm ON msg.num_camion = tcm.num_camion
LEFT JOIN syflux_archives.base_produit AS bp ON bp.reference = msg.plq_7304
WHERE CAST(dtm AS date) >= vStartDate AND IF(ReportType = 0,msg.rack = 'RACK_FEM', msg.rack = 'RACK_FEM' AND delivery_date <> '0')
GROUP BY bgm, msg.num_camion
ORDER BY processorder, bgm, msg.num_camion;

END
$$
myDBR Team, Key Master
Couple of problems here:

1) Use INTO instead of AS
```
SELECT bgm AS vCurrentBGM
```
AS will not put the value of the bgm-column into vCurrentBGM variable, but will select it under that name instead. Use INTO.

2) Try not to mix datatypes
```
WHERE pm_date != 0
```
If pm_date is varchar(17), you should not compare it to integer zero. Do you mean string zero '0'?

3) This will not work
```
SELECT 'dbr.colstyle', DATE_FORMAT('DI Date','%Y-%m-%d %H:%i');
```
is not valid syntax.

4) Mixing datatypes
ReportType is varchar(1), not integer

5) Implicit datatype conversion
Try avoid using DATE_FORMAT as it converts dates/datetimes to strings.

6) Instead of:

IF(ReportType = 0,msg.rack = 'RACK_FEM', msg.rack = 'RACK_FEM' AND delivery_date <> '0')

you probably should use normal AND + OR. What is the datatype of delivery_date, date or a varchar/char?

--
myDBR Team
Stuart Gibson, Member

My bad. I see the errors of my way.

Its certainly of learning curve...

Thanks!
Stu

Reply

You must log in to post.