• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

HOW to add a javascript popup window in your SQL statement

In my sql statement I need to  construct a column to build this value for each row, with the a guid being added instead of myIdGoesHere:
SQL that I have
SELECT Gtri_purchaseOrderLine.gtri_purchaseorderlineid,
            Gtri_purchaseorderline.gtri_purchaseorderidName,
            CASE WHEN GTRI_shippinginformation.GTRI_shippinginformationid IS NOT NULL
            THEN ROUND(GTRI_shippinginformation.GTRI_Quantity,0)
            ELSE FLOOR(Gtri_purchaseorderline.Gtri_quantity)
            END AS Quantity,
            Gtri_purchaseorderline.Gtri_partnumber AS ProductDescription,
            GTRI_purchaseorder.GTRI_Vendor AS Vendor,
            GTRI_purchaseorder.GTRI_purchaseorderId,
            GTRI_purchaseorder.gtri_orderid,
            SalesOrder.opportunityid,
            SalesOrder.GTRI_OpportunityID,
            CONVERT(VARCHAR(2), DATEPART(mm,Quote.GTRI_DateSubmittedtoPurchasing)) + '/' + CONVERT(VARCHAR(2), DATEPART(dd,GETDATE())) + '/' + CONVERT(VARCHAR(4), DATEPART(yyyy,GETDATE())) AS GTRI_DateSubmittedtoPurchasing,
            GTRI_shippinginformation.GTRI_shippinginformationid,
            CONVERT(VARCHAR(2), DATEPART(mm,GTRI_shippinginformation.Gtri_EstShipDate)) + '/' + CONVERT(VARCHAR(2), DATEPART(dd,GETDATE())) + '/' + CONVERT(VARCHAR(4), DATEPART(yyyy,GETDATE())) AS Gtri_EstShipDate,
            CONVERT(VARCHAR(2), DATEPART(mm,GTRI_shippinginformation.Gtri_ActualShipDate)) + '/' + CONVERT(VARCHAR(2), DATEPART(dd,GETDATE())) + '/' + CONVERT(VARCHAR(4), DATEPART(yyyy,GETDATE())) AS Gtri_ActualShipDate,
            GTRI_shippinginformation.GTRI_TrackingNumbers,
            isNull (GTRI_shippinginformation.Gtri_TrackingType,0) AS Gtri_TrackingType,  
            GTRI_shippinginformation.Gtri_SerialNumber,
            GTRI_shippinginformation.Gtri_ShippingHistory,
            isNull (GTRI_shippinginformation.Gtri_DeliveryStatus,0) AS Gtri_DeliveryStatus,
            isNull(GTRI_shippinginformation.Gtri_RequestSNT,0) AS Gtri_RequestSNT,
            isNull(GTRI_shippinginformation.Gtri_SNTOrder,0) AS Gtri_SNTOrder,
            GTRI_shippinginformation.Gtri_ShippingHistory,
            FROM Gtri_purchaseorderline
            LEFT JOIN GTRI_purchaseorder
                ON GTRI_purchaseorder.GTRI_purchaseorderId = Gtri_purchaseorderline.GTRI_purchaseorderId
            LEFT OUTER JOIN Quote
                ON Quote.QuoteId = GTRI_purchaseorder.gtri_quoteid
            LEFT JOIN SalesOrder
                ON GTRI_purchaseorder.gtri_orderid = SalesOrder.SalesOrderId
            LEFT OUTER JOIN GTRI_shippinginformation
                ON Gtri_purchaseOrderLine.gtri_purchaseorderlineid = GTRI_shippinginformation.gtri_purchaseorderlineid
                AND GTRI_shippinginformation.DeletionStateCode = 0
            WHERE SalesOrder.SalesOrderId= '09D00AFC-5343-DF11-B8ED-0050569F4744' ORDER BY Gtri_purchaseOrderLine.Gtri_lineitemnumber
            //WHERE SalesOrder.SalesOrderId= '" + _orderId + "' ORDER BY Gtri_purchaseOrderLine.Gtri_lineitemnumber

What i ned to add to the SQL Statement:
'javascript:openPopup("' + gtri_shippinginformationid  + '")' AS popup,

Please help I am new to SQL and JavaScript
0
newjeep19
Asked:
newjeep19
  • 2
  • 2
1 Solution
 
DrewKjellCommented:
So you are looking for something like this?


SELECT '''javascript:openPopup("' + gtri_shippinginformationid  + '")''' AS popup, 
Gtri_purchaseOrderLine.gtri_purchaseorderlineid,
            Gtri_purchaseorderline.gtri_purchaseorderidName,
            CASE WHEN GTRI_shippinginformation.GTRI_shippinginformationid IS NOT NULL
            THEN ROUND(GTRI_shippinginformation.GTRI_Quantity,0)
            ELSE FLOOR(Gtri_purchaseorderline.Gtri_quantity) 
            END AS Quantity,
            Gtri_purchaseorderline.Gtri_partnumber AS ProductDescription,
            GTRI_purchaseorder.GTRI_Vendor AS Vendor,
            GTRI_purchaseorder.GTRI_purchaseorderId,
            GTRI_purchaseorder.gtri_orderid,
            SalesOrder.opportunityid,
            SalesOrder.GTRI_OpportunityID,
            CONVERT(VARCHAR(2), DATEPART(mm,Quote.GTRI_DateSubmittedtoPurchasing)) + '/' + CONVERT(VARCHAR(2), DATEPART(dd,GETDATE())) + '/' + CONVERT(VARCHAR(4), DATEPART(yyyy,GETDATE())) AS GTRI_DateSubmittedtoPurchasing,
            GTRI_shippinginformation.GTRI_shippinginformationid,
            CONVERT(VARCHAR(2), DATEPART(mm,GTRI_shippinginformation.Gtri_EstShipDate)) + '/' + CONVERT(VARCHAR(2), DATEPART(dd,GETDATE())) + '/' + CONVERT(VARCHAR(4), DATEPART(yyyy,GETDATE())) AS Gtri_EstShipDate,
            CONVERT(VARCHAR(2), DATEPART(mm,GTRI_shippinginformation.Gtri_ActualShipDate)) + '/' + CONVERT(VARCHAR(2), DATEPART(dd,GETDATE())) + '/' + CONVERT(VARCHAR(4), DATEPART(yyyy,GETDATE())) AS Gtri_ActualShipDate, 
            GTRI_shippinginformation.GTRI_TrackingNumbers,
            isNull (GTRI_shippinginformation.Gtri_TrackingType,0) AS Gtri_TrackingType,  
            GTRI_shippinginformation.Gtri_SerialNumber,
            GTRI_shippinginformation.Gtri_ShippingHistory, 
            isNull (GTRI_shippinginformation.Gtri_DeliveryStatus,0) AS Gtri_DeliveryStatus,
            isNull(GTRI_shippinginformation.Gtri_RequestSNT,0) AS Gtri_RequestSNT,
            isNull(GTRI_shippinginformation.Gtri_SNTOrder,0) AS Gtri_SNTOrder,
            GTRI_shippinginformation.Gtri_ShippingHistory, 
            FROM Gtri_purchaseorderline
            LEFT JOIN GTRI_purchaseorder
                ON GTRI_purchaseorder.GTRI_purchaseorderId = Gtri_purchaseorderline.GTRI_purchaseorderId
            LEFT OUTER JOIN Quote
                ON Quote.QuoteId = GTRI_purchaseorder.gtri_quoteid
            LEFT JOIN SalesOrder
                ON GTRI_purchaseorder.gtri_orderid = SalesOrder.SalesOrderId
            LEFT OUTER JOIN GTRI_shippinginformation
                ON Gtri_purchaseOrderLine.gtri_purchaseorderlineid = GTRI_shippinginformation.gtri_purchaseorderlineid
                AND GTRI_shippinginformation.DeletionStateCode = 0 
            WHERE SalesOrder.SalesOrderId= '09D00AFC-5343-DF11-B8ED-0050569F4744' ORDER BY Gtri_purchaseOrderLine.Gtri_lineitemnumber

Open in new window

0
 
srikanthmadishettiCommented:
You can better handle this in front end rather then in sql ,

Instead of adding this to sql as new column

loop your query

{

<button onclick = 'openPopup("' + gtri_shippinginformationid  + '");'>
}
end loop

some thing like that in your front end code if you want in query it self
just add 'javascript:openPopup("' + gtri_shippinginformationid  + '")' AS popup after GTRI_shippinginformation.GTRI_shippinginformationid, in the query

0
 
newjeep19Author Commented:
I get this error:
The data types varchar and uniqueidentifier are incompatible in the add operator.
When I  'javascript:openPopup("' + gtri_shippinginformationid  + '")' AS popup after GTRI_shippinginformation.GTRI_shippinginformationid, in the query

I get this error:
The data types varchar and uniqueidentifier are incompatible in the add operator.
SELECT '''javascript:openPopup("' + gtri_shippinginformationid  + '")''' AS popup,
Gtri_purchaseOrderLine.gtri_purchaseorderlineid,
            Gtri_purchaseorderline.gtri_purchaseorderidName,
            CASE WHEN GTRI_shippinginformation.GTRI_shippinginformationid IS NOT NULL
            THEN ROUND(GTRI_shippinginformation.GTRI_Quantity,0)
            ELSE FLOOR(Gtri_purchaseorderline.Gtri_quantity)
            END AS Quantity,
            Gtri_purchaseorderline.Gtri_partnumber AS ProductDescription,
            GTRI_purchaseorder.GTRI_Vendor AS Vendor,
            GTRI_purchaseorder.GTRI_purchaseorderId,
            GTRI_purchaseorder.gtri_orderid,
            SalesOrder.opportunityid,
            SalesOrder.GTRI_OpportunityID,
            CONVERT(VARCHAR(2), DATEPART(mm,Quote.GTRI_DateSubmittedtoPurchasing)) + '/' + CONVERT(VARCHAR(2), DATEPART(dd,GETDATE())) + '/' + CONVERT(VARCHAR(4), DATEPART(yyyy,GETDATE())) AS GTRI_DateSubmittedtoPurchasing,
            GTRI_shippinginformation.GTRI_shippinginformationid,
            CONVERT(VARCHAR(2), DATEPART(mm,GTRI_shippinginformation.Gtri_EstShipDate)) + '/' + CONVERT(VARCHAR(2), DATEPART(dd,GETDATE())) + '/' + CONVERT(VARCHAR(4), DATEPART(yyyy,GETDATE())) AS Gtri_EstShipDate,
            CONVERT(VARCHAR(2), DATEPART(mm,GTRI_shippinginformation.Gtri_ActualShipDate)) + '/' + CONVERT(VARCHAR(2), DATEPART(dd,GETDATE())) + '/' + CONVERT(VARCHAR(4), DATEPART(yyyy,GETDATE())) AS Gtri_ActualShipDate,
            GTRI_shippinginformation.GTRI_TrackingNumbers,
            isNull (GTRI_shippinginformation.Gtri_TrackingType,0) AS Gtri_TrackingType,  
            GTRI_shippinginformation.Gtri_SerialNumber,
            GTRI_shippinginformation.Gtri_ShippingHistory,
            isNull (GTRI_shippinginformation.Gtri_DeliveryStatus,0) AS Gtri_DeliveryStatus,
            isNull(GTRI_shippinginformation.Gtri_RequestSNT,0) AS Gtri_RequestSNT,
            isNull(GTRI_shippinginformation.Gtri_SNTOrder,0) AS Gtri_SNTOrder,
            GTRI_shippinginformation.Gtri_ShippingHistory,
            FROM Gtri_purchaseorderline
            LEFT JOIN GTRI_purchaseorder
                ON GTRI_purchaseorder.GTRI_purchaseorderId = Gtri_purchaseorderline.GTRI_purchaseorderId
            LEFT OUTER JOIN Quote
                ON Quote.QuoteId = GTRI_purchaseorder.gtri_quoteid
            LEFT JOIN SalesOrder
                ON GTRI_purchaseorder.gtri_orderid = SalesOrder.SalesOrderId
            LEFT OUTER JOIN GTRI_shippinginformation
                ON Gtri_purchaseOrderLine.gtri_purchaseorderlineid = GTRI_shippinginformation.gtri_purchaseorderlineid
                AND GTRI_shippinginformation.DeletionStateCode = 0
            WHERE SalesOrder.SalesOrderId= '09D00AFC-5343-DF11-B8ED-0050569F4744' ORDER BY Gtri_purchaseOrderLine.Gtri_lineitemnumber
0
 
srikanthmadishettiCommented:
try this

 'javascript:openPopup("' + CAST(gtri_shippinginformationid  AS varchar(12)) + '")' AS popup
0
 
newjeep19Author Commented:
Thank you that worked
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now