Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Reference a value in a table to use in another SQL power Query

Hi All   I have a value held in another PQ table. 1 row 1 column containing an invoice number. - Is it possible to use this particular invoice number and use in a SQL query without having to go int...
Need Help
Tips and Tricks
  • KNP's avatar
    KNP
    4 years ago

    Double quote missing before the closing square bracket I think. 

    I notice you're missing a table alias for your SHIP_DATE_RETURN, not sure if that will cause you any abiguity issues.

    This is just the query portion, make sure it goes inside the correct ().

    [Query="
SELECT rd.SERIAL_NUMBER
     , rd.REF_NUM
     , rd.MODEL_ASC
     , pm.MODEL
     , rd.MOTO_PART_NUMBER
     , rd.ASC_SITE_ID
     , ras.ASC_SITE_NAME
     , rd.REPAIR_TYPE
     , acm.ASC_CUSTOMER_NAME
     , rd.WARR_CODE
     , rd.RMA_CREATION_DATE
     , rd.EXPIRY_DATE
     , rd.RECV_DATE
     , rd.SHIP_DATE_RETURN
     , rd.UNIT_STATUS
     , rd.PROJECT_NAME
     , rd.FINAL_UNIT_DETERMINATION
     , rd.INVOICE_NUMBER
     , rd.INVOICE_DATE
     , ROUND((SHIP_DATE_RETURN - rd.RECV_DATE)) AS AGE
     , (rd.RECV_DATE + 30)                      AS TAT
     , rd.LABCOST
     , rd.FREIGHT_COST
FROM rdwadmin.rdw_repair_details rd
   , rdwadmin.rdw_part_master pm
   , rdwadmin.rdw_asc_customer_master acm
   , rdwadmin.rdw_asc_site ras
WHERE rd.moto_part_number = pm.moto_part_number
      AND rd.ASC_CUSTOMER_NUMBER = acm.ASC_CUSTOMER_NUMBER
      AND rd.ASC_SITE_ID = ras.ASC_SITE_ID
      AND rd.INVOICE_NUMBER = '"& #"Current-Invoice"[CurrentInvoice]{0} &"'

"]

     

KNP's avatar
KNP
Super User
4 years ago

This looks like your issue. Helps now that I know what the table is called. ğŸ˜‰

 

Change the above to...

'"& #"Current-Invoice[CurrentInvoice]{0} &"'

 

If you renamed your query you wouldn't need to reference the table with the #"".

Pay close attention to the quotes. Opens with single, double. Closes with double, single.

Make sure you don't remove the last ]) that closes off the "Query".

 

I think this will get you there.

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

So i amended the name, but it didnt like the extra ' at the end as you can see...

 

 

I removed the extra ' from the end the, PQ then started loading then came up with the below:

 

 

Though the name is spelled correctly?